NemExcelNu ~

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

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;

Del siden