På denne side har jeg udvalgt program eksempler fra SQL, livet er jo ikke kun excel :O): 

 https://thenewboston.com/videos.php?cat=49&video=19004

 

Opret en ny database og én tabel

§Database navn: Privat
§Tabel navn: Venner

 

 

CREATE DATABASE Privat;

 

USE Privat;

 

 

 

CREATE TABLE Venner

 

  ( ID INT PRIMARY KEY auto_increment,

 

  FirstName VARCHAR(20),

 

  LastName VARCHAR(30),

 

  Address VARCHAR(50),

 

  ZipCode SMALLINT(4),

 

  City VARCHAR(30),

 

  PhoneNumber INT(8),

 

  DayofBirth VARCHAR(10) );

 

 

 

INSERT INTO Venner (FirstName, LastName, Address, ZipCode, City, PhoneNumber, DayofBirth)

 

VALUES

 

('Jesper', 'Jespersen', 'Nybrovej 260', 2800, 'Lyngby', '61303005', '15-06-1987'),

 

('David', 'Nielsen', 'Rosenhavne 26', 3660, 'Stenloese', '40501337', '25-04-1960'),

 

('Pernille', 'Rasmussen', 'Neptunvej 9', 4040, 'Jyllinge', '61330850', '01-02-1989');

 

 

 

 

Lektion 1

Opgave 1

*/

 

/*   

List følgende felter fra tabellen: Customers

       CustomerId, CompanyName, ContactName, Country    

*/

 

USE Northwind;

 

SELECT

       CustomerId,

    CompanyName,

    ContactName,

    Country

FROM Customers;

 

 

/*

Find alle lande der er sendt en ordre til,

vis dem kun én gang

*/

SELECT DISTINCT ShipCity

FROM Orders;

 

/*

List alle Produkter fra tabellen Products efter UnitPrice falden order

*/

SELECT * FROM Products

ORDER BY UnitPrice DESC;

 

Lektion 2

Find alle Customeres fra UK og
 Spain 
*/
USE Northwind;
 
SELECT * FROM Customers
WHERE Country = 'UK' OR 
Country = 'Spain';
 
/*
Find alle Products hvor vi har mere end 100 på
 lager (UnitsInStock)
*/
SELECT * FROM Products
WHERE UnitsInStock > 100;
 
/*
Find alle Products hvor vi har mere end 100 på lager (UnitsInStock)
og prisen (UnitPrice)
 er større end eller lig med 25
*/
SELECT * FROM Products
WHERE UnitsInStock > 100 AND
UnitPrice >= 25;
 
/*
Find Ordre værdien for hver ProductID
Altså
 det via har i ordre (UnitsOnOrder)
Skal sorteres faldende og ProductID uden ordre (0) skal ikke vises 
*/
SELECT
       ProductID, 
       UnitsOnOrder
 * UnitPrice AS SaleValue
FROM Products
WHERE UnitsOnOrder > 0
ORDER BY SaleValue DESC;
 
/*
Opret en ny tabel: KunderUSA. 
Kopier alle Customers fra USA der ikke har en Fax,
 ind i KunderUSA
*/
CREATE TABLE KunderUSA LIKE Customers;
 
INSERT INTO KunderUSA
SELECT * FROM Customers
WHERE Country= 'USA'
AND Fax IS NULL;
 
SELECT
 * FROM KunderUSA;
 
 
/*
Find alle Ordres der er har en OrdreDate i 10 månede af 1996
*/
SELECT 
       OrderID,
      
 OrderDate
FROM Orders
WHERE YEAR(OrderDate) = 1996 AND
MONTH(OrderDate) = 10;
 
/*
Find alle Orders hvor ShipRegion er blank,
ShipCountry = Germany, 
Freight er større
 end eller lig med 100, 
EmployeeID = 1,
OrdreDate er fra 1996
*/
SELECT * FROM Orders
WHERE ShipRegion IS NULL AND
ShipCountry = 'Germany' AND
Freight >= 100 AND
EmployeeID
 = 1 AND
YEAR(OrderDate) = 1996;
 
 

Lektion 3

/*
Opret en SELECT sætning der viser følgende felter:
ProductID
ProductName
CategoryName
*/
USE Northwind;
 
SELECT 
       Products.ProductID,
       Products.ProductName,
      
 Categories.CategoryName
FROM Products INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID;
 
 
/*
Opret en SELECT sætning der indeholder følgende felter:
ProductID
ProductName
CompanyName
*/
SELECT 
       Products.ProductID,
       Products.ProductName,
      
 Suppliers.CompanyName
FROM Products INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID;
 
 
/*
Opret en SELECT sætning der indeholder følgende felter
OrderID
CompanyName
Name som består af FirstName og LastName
*/
SELECT
       Orders.OrderID,
       Customers.CompanyName,
       Employees.LastName + ' ' + Employees.FirstName AS Name
FROM Employees INNER JOIN Orders 
ON Employees.EmployeeID = Orders.EmployeeID 
INNER JOIN Customers 
ON Orders.CustomerID
 = Customers.CustomerID
 
 
/*
Opret en SELECT sætning der indeholder følgende felter:
OrderId
CompanyName
ProductName
*/
SELECT
       Orders.OrderID,
       Customers.CompanyName,
       Products.ProductName
FROM Customers INNER JOIN Orders
ON
 Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID
ORDER BY OrderID
 
 
/*
Find alle TerritoryID hvor der
ikke er en Employee tilknytet
*/
SELECT 
       Employees.EmployeeID,
      
 Territories.TerritoryID,
       Territories.TerritoryDescription
FROM Employees INNER JOIN EmployeeTerritories 
ON Employees.EmployeeID = EmployeeTerritories.EmployeeID 
RIGHT JOIN Territories
 
ON EmployeeTerritories.TerritoryID = Territories.TerritoryID
WHERE Employees.EmployeeID IS NULL
 
 
/*
Find alle Products (ProductName og ProductID)
Der er solgt i maj
 månede 1997
*/
SELECT 
       Products.ProductID,
       Products.ProductName
FROM Products INNER JOIN `Order Details`
ON Products.ProductID
 = `Order Details`.ProductID
INNER JOIN Orders
ON `Order Details`.OrderID = Orders.OrderID
WHERE YEAR(OrderDate) = 1997
AND MONTH(OrderDate) = 5
 
/*
Opret en SQL sætning
 der viser 
*/
SELECT
       Orders.OrderID,
    Customers.CompanyName,
    Products.ProductName
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
INNER JOIN `Order Details`
ON Orders.OrderID = `Order Details`.OrderID
INNER JOIN Products
ON `Order Details`.ProductID = Products.ProductID;
Lektion
 4
USE Northwind;
 
/*
Find de ordre der ikke er leveret til tiden, ShippedeDate er større end
RequiredDate. 
Brug tabellen Orders
Resultat: 37 Rows
*/
SELECT 
OrderID, 
DATEDIFF(ShippedDate, RequiredDate) AS 'AntalDage' -- DATEDIFF - DAY 
YEAR giver dage forskelen mellem de to tabeller husk AS eller år
FROM Orders
WHERE ShippedDate > RequiredDate
ORDER BY AntalDage; -- Sorter på AntalDage
 
/*
Du skal oprette en kolonne der indeholder følgende værdier:
* Hvis der er ikke er givet Discount: Ingen Discount
* Hvis der
 er givet mellem 0 og 10 pct.: Lille Discount
* Hvis der er givet over 10 pct.: Stor Discount
*/
SELECT 
       OrderId,
    Discount,
      
 CASE 
              WHEN Discount = 0 THEN 'Ingen Discount'
              WHEN Discount BETWEEN 0 AND 0.1
 THEN 'Lille Discount'
              WHEN Discount > 0.1 THEN 'Stor Discount'
       END AS DiscountRates
FROM `Order Details`
ORDER BY Discount DESC;
 
/*
Find alle Ordre (OrderDate) fra 1997 i månederne januar, februar, marts og
april fra Canada (ShipCountry). 
Brug tabelen ’Order Details’
Resulatat:
 8 Rows
*/
SELECT OrderID, ShipCountry, OrderDate
FROM Orders
WHERE MONTH(OrderDate) IN (1, 2, 3, 4) AND 
YEAR(OrderDate)=1997 AND ShipCountry='Canada'; -- Finder Månder Jan, Feb, Mar, Apr i 1997
 og i Canada
 
/*
Find de 3 produkter vi har flest på lager af – Products.UnitsInStock
Resultat: ProductID; 75, 40, 6
*/
SELECT
      
 ProductID, 
       SUM(UnitsInStock) AS Units
FROM Products
GROUP BY ProductID
ORDER BY Units DESC
LIMIT 3;
 
/*
Find de 3 produkter
 hvor vi har mest på lager i forhold til værdien af lageret
Resultat: ProductID; 38, 59, 12
*/
SELECT
       ProductID, 
    UnitsInStock*UnitPrice
 AS StockValue
FROM Products
ORDER BY StockValue DESC
LIMIT 3;
 
/*
Find de Employees hvor der ikke er en værdi (NULL) enten i Region eller
Reports To. Desuden skal de være
 født i 1960 eller tidligere
Resultat: 3 Rows
*/
SELECT 
       LastName,
       BirthDate, 
      
 Region, 
       ReportsTo
FROM Employees
WHERE (Region IS NULL OR ReportsTo IS NULL) 
AND YEAR(BirthDate)<= 1960;
 
/*
Find de Ordre hvor EmployeeID
 er lig med 2, 5 eller 8. ShipRegion ikke er
NULL og ShipVia enten er 1 eller 3. 
Skal sorteres først efter EmployeeID derefter ShipVia
Resultat: 57 Rows
*/
SELECT 
      
 EmployeeID,
    ShipRegion, 
    ShipVia
FROM Orders
WHERE EmployeeID IN (2, 5, 8) AND ShipRegion IS NOT NULL AND ShipVia IN (1, 3)
ORDER BY EmployeeID, ShipVia;
 
/*
Find alle Customeres hvis Contact Titel 
enten er Sales Agent eller Sales Associate
Resultat: 12 Rows
*/
SELECT *
FROM Customers
WHERE ContactTitle = 'Sales
 Agent' OR ContactTitle = 'Sales Associate'
 
Lektion 5
USE Northwind;
 
/*
*****************************
********** SELF JOIN ********
*****************************
*/
SELECT
       Employees.EmployeeID,
       Employees.LastName,
       Employees.ReportsTo, 
      
 Boss.EmployeeID AS BossID, 
       Boss.LastName AS BossLastName
FROM Employees INNER JOIN Employees AS Boss 
ON Employees.ReportsTo = Boss.EmployeeID
ORDER BY Employees.EmployeeID;
 
 
/*
*****************************
************* VIEW **********
*****************************
*/
CREATE VIEW Products_Above_Average_Price2 
AS
 
       SELECT 
              ProductName,
              UnitPrice
       FROM Products
       WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);
 
-- Test af VIEW - Products_Above_Average_Price
SELECT
 * 
FROM Products_Above_Average_Price;
 
-- Total oms pr. ordre
CREATE VIEW TotalOmsOrdre
AS
       SELECT
             
 Orders.OrderID,
              SUM((UnitPrice * Quantity)-(UnitPrice * Quantity * Discount)) AS Total 
       FROM Orders INNER JOIN `Order
 Details`
       ON Orders.OrderID = `Order Details`.OrderID
       GROUP BY Orders.OrderID;
 
SELECT * FROM TotalOmsOrdre;
 
 
-- Ændre VIEW
ALTER VIEW Products_Above_Average_Price 
AS
 
       SELECT 
             
 ProductName,
              UnitPrice,
              UnitsInStock
      
 FROM Products
       WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);
 
-- Slet VIEW
DROP VIEW Products_Above_Average_Price;
 
 
/*
*****************************
********** SUBQUERIES *******
*****************************
*/
SELECT DISTINCT 
       Country FROM Customers 
WHERE
 Country NOT IN 
       (SELECT DISTINCT Country FROM Suppliers)
ORDER BY Country;
 
-- EXIST
SELECT 
       CustomerID, 
       CompanyName 
FROM Customers
WHERE EXISTS
       (SELECT * FROM Orders
    WHERE Customers.CustomerID = Orders.CustomerID
    AND ShipCountry = 'UK')
ORDER BY CustomerID;
 
-- Samme med JOIN
SELECT
       DISTINCT Orders.CustomerID,
      
 Customers.CompanyName
FROM Customers INNER JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipCountry = 'UK'
ORDER BY CustomerID;
 
-- Exist
SELECT DISTINCT
 
       Country FROM Customers 
WHERE EXISTS
       (SELECT DISTINCT Country FROM Suppliers);
 
-- NOT EXIST
SELECT DISTINCT
 
       Country FROM Customers 
WHERE NOT EXISTS
       (SELECT DISTINCT Country FROM Suppliers);
 
/*
*****************************
************ UNION **********
*****************************
*/
SELECT City FROM Customers;
 
SELECT City FROM Suppliers
ORDER BY City;
 
SELECT City FROM
 Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
 
-- UNION ALL
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City
 
 
 
 
 
 
 

 

 Skoleopgave Lektion 5

 

Create Database Skole;

CREATE TABLE Elev_Fag_Kar
( ElevNr INT(2), Fag Varchar(10), Kar INT(10));


INSERT INTO Elev_Fag_Kar (ElevNr, Fag, Kar)
VALUES
('16', 'Dansk', '10'),
('16', 'Foto', '12'),
('16', 'Matematik', '7'),
('24', 'Matematik', '2'),
('24', 'Dansk', '7'),
('31', 'Fysik', '4'),
('31', 'Engelsk', '4'),
('43', 'Engelsk', '7'),
('43', 'Dansk', '10'),
('44', 'Matematik', '2'),
('43', 'Engelsk', '7'),
('50', 'Dansk', '10');

CREATE TABLE Elev_Navn
( ElevNr INT(2), Navn Varchar(10));

INSERT INTO Elev_Navn (ElevNr, Navn)
VALUES
('16', 'Poul'),
('24', 'Ulla'),
('31', 'Bent'),
('43', 'Otto'),
('44', 'Fie'),
('50', 'Lars');

CREATE TABLE Elev_klasse
(ElevNr INT(2), Klasse Varchar(2));
INSERT INTO Elev_Klasse (ElevNr, Klasse)
VALUES
('16', 'B3'),
('24', 'A3'),
('31', 'B2'),
('43', 'A5'),
('44', 'B2'),
('50', 'A5');

CREATE TABLE Klasse_vejleder
(Klasse Varchar(2); Vejleder Varchar(10));

INSERT INTO Klasse_vejleder (klasse, Vejleder)
VALUES
('B3', 'Jensen'),
('A3', 'Ibsen'),
('B2', 'Jensen'),
('A5', 'Flovman');

 

 

 

SELECT 
*
FROM elev_fag_kar 
INNER JOIN elev_klasse
ON elev_fag_kar.ElevNr=elev_klasse.ElevNr
INNER JOIN elev_navn
ON elev_fag_kar.ElevNr= elev_navn.ElevNr
INNER JOIN klasse_vejleder
ON elev_klasse.Klasse=klasse_vejleder.Klasse;

 

Use Skole;
SELECT
*
FROM elev_fag_kar
INNER JOIN elev_klasse
ON elev_fag_kar.ElevNr=elev_klasse.ElevNr
INNER JOIN elev_navn
ON elev_fag_kar.ElevNr= elev_navn.ElevNr
INNER JOIN klasse_vejleder
ON elev_klasse.Klasse=klasse_vejleder.Klasse
Where klasse_vejleder.Vejleder = 'Jensen';

Create View...

Use Skole;

Create VIEW V_Jensen

AS

SELECT
elev_fag_kar.ElevNr,
elev_fag_kar.Fag,
elev_fag_kar.Kar,
elev_klasse.Klasse,
elev_navn.Navn,
klasse_vejleder.Vejleder
FROM elev_fag_kar
INNER JOIN elev_klasse
ON elev_fag_kar.ElevNr=elev_klasse.ElevNr
INNER JOIN elev_navn
ON elev_fag_kar.ElevNr= elev_navn.ElevNr
INNER JOIN klasse_vejleder
ON elev_klasse.Klasse=klasse_vejleder.Klasse
Where klasse_vejleder.Vejleder = 'Jensen';

Use Skole;
Select *
FROM V_Jensen;

 

Use Classicmodels;

Select
Concat(contactFirstName," ", contactLastName) As Contactname,
orderDate,
quantityOrdered * priceEach As salesvalue
From orders
INNER JOIN Customers
ON orders.Customernumber = Customers.Customernumber
INNER JOIN Orderdetails
ON orders.orderNumber = Orderdetails.orderNumber
Where quantityOrdered * priceEach>10000;