SQL - notatki z wykładu część trzecia PDF

Preview

Summary

Język SQL, część 3.Łączenie tabel. Złączenie wewnętrzne (INNER JOIN), złączenia zewnętrzne(OUTER JOINS).CREATE TABLE Pracownicy (Nr INT PRIMARY KEY, Nazwisko NVARCHAR( 50 ) NOT NULL, Imie NVARCHAR( 50 ) NOT NULL, Stawka MONEY, DataZatrudnienia DATE, LiczbaDzieci TINYINT )CREATE TABLE Urlopy (NrPrac ...

Description

Język SQL, część 3. Łączenie tabel. Złączenie wewnętrzne (INNER JOIN), złączenia zewnętrzne (OUTER JOINS). CREATE TABLE Pracownicy (Nr INT PRIMARY KEY, Nazwisko NVARCHAR(50) NOT NULL, Imie NVARCHAR(50) NOT NULL, Stawka MONEY, DataZatrudnienia DATE, LiczbaDzieci TINYINT ) CREATE TABLE Urlopy (NrPrac INT, OdKiedy DATE, DoKiedy DATE, CHECK (DoKiedy>=OdKiedy), PRIMARY KEY (NrPrac, OdKiedy), FOREIGN KEY (NrPrac) REFERENCES Pracownicy(Nr) ) INSERT INTO Pracownicy VALUES (1,'Kowal','Jan',2500,'2010-01-01',2), (2,'Zielinska','Agnieszka',3500,'2013-04-01',1), (3,'Nowak','Piotr',4500,'2000-01-01',0) SELECT * FROM Pracownicy INSERT INTO Urlopy VALUES (1,'2015-01-01','2015-01-07'), (3,'2015-01-01','2015-01-07'), (1,'2015-03-10','2015-03-15') SELECT * FROM Pracownicy SELECT * FROM Urlopy

„Iloczyn kartezjański” tabel (każdy wiersz z jednej tabeli połączony z każdym wierszem z drugiej tabeli): SELECT * FROM Pracownicy, Urlopy SELECT * FROM Pracownicy, Urlopy WHERE Nr=NrPrac SELECT * FROM Pracownicy, Urlopy WHERE Pracownicy.Nr=Urlopy.NrPrac --Aliasy nazw tabel SELECT * FROM Pracownicy AS P, Urlopy AS U WHERE P.Nr=U.NrPrac --Słowo AS może byc pominięte, podobnie jak w przypadku aliasów dla kolumn SELECT * FROM Pracownicy P, Urlopy U WHERE P.Nr=U.NrPrac

Operator złączenia tabel (JOIN = INNER JOIN): SELECT * FROM Pracownicy JOIN Urlopy ON Pracownicy.Nr = Urlopy.NrPrac SELECT * FROM Pracownicy SELECT * FROM Urlopy SELECT * FROM Pracownicy INNER JOIN Urlopy ON Pracownicy.Nr = Urlopy.NrPrac

Złączenie zewnętrzne lewe (LEFT OUTER JOIN = LEFT JOIN): SELECT * FROM Pracownicy LEFT OUTER JOIN Urlopy ON Pracownicy.Nr = Urlopy.NrPrac SELECT * FROM Pracownicy LEFT JOIN Urlopy ON Pracownicy.Nr = Urlopy.NrPrac

Złączenie zewnętrzne prawe (RIGHT OUTER JOIN = RIGHT JOIN): SELECT * FROM Pracownicy RIGHT JOIN Urlopy ON Pracownicy.Nr = Urlopy.NrPrac SELECT * FROM Urlopy U RIGHT JOIN Pracownicy P ON P.Nr = U.NrPrac SELECT P.*, U.* FROM Urlopy U RIGHT JOIN Pracownicy P ON P.Nr = U.NrPrac SELECT *, UnitPrice* Quantity * (1-Discount) AS Amount FROM [Order Details] SELECT *, UnitPrice* Quantity * CAST((1-Discount) AS MONEY) AS Amount FROM [Order Details] SELECT *, ROUND(UnitPrice* Quantity * CAST((1-Discount) AS MONEY),2) AS Amount FROM [Order Details] SELECT * FROM Orders --Dodajmy do tego zestawu --id klienta i datę zamówienia: SELECT OD.*, ROUND(UnitPrice*Quantity * CAST((1-Discount) AS MONEY),2) AS Amount, O.OrderDate, O.CustomerID FROM [Order Details] AS OD JOIN Orders AS O ON OD.OrderID = O.OrderID

SELECT OD.*, ROUND(UnitPrice*Quantity * CAST((1-Discount) AS MONEY),2) AS Amount, O.OrderDate, O.CustomerID FROM [Order Details] AS OD JOIN Orders AS O ON OD.OrderID = O.OrderID WHERE ShipCountry = N'France' --To jest źle: SELECT OD.*, ROUND(UnitPrice*Quantity * CAST((1-Discount) AS MONEY),2) AS Amount, O.OrderDate, O.CustomerID FROM [Order Details] AS OD JOIN Orders AS O ON OD.OrderID = O.OrderID WHERE ShipCountry = N'France' AND Amount > 200 --Poprawne rozwiązanie SELECT OD.*, ROUND(UnitPrice*Quantity * CAST((1-Discount) AS MONEY),2) AS Amount, O.OrderDate, O.CustomerID FROM [Order Details] AS OD JOIN Orders AS O ON OD.OrderID = O.OrderID WHERE ShipCountry = N'France' AND ROUND(UnitPrice*Quantity * CAST((1-Discount) AS MONEY),2) > 200

Funkcje agregujące. Zdania SELECT z agregacjami. --Funkcje agregujące: --SUM(Ilosc) – wylicza sumę wszystkich wartości w podanej kolumnie (tutaj: Ilosc) -- Rekordy, które w podanej kolumnie mają NULL, nie są w ogóle brane pod uwagę -- przy obliczaniu sumy --AVG(Cena) – wyliczenie średniej arytmetycznej w kolumnie Cena -- Rekordy, które w podanej kolumnie mają NULL, nie są w ogóle brane pod uwagę -- przy obliczaniu średniej --MIN (Cena) – wyznaczenie najmniejszej wartości w podanej kolumnie (tutaj: Cena) --w odniesieniu do dat, będzie to data najwcześniejsza --MAX(Cena) – wyznaczenie największej wartości w podanej kolumnie (tutaj: Cena) --w odniesieniu do dat, będzie to data najpóźniejsza --COUNT(*) - zlicza ile jest rekordów w grupie --COUNT(Cena) - zlicza w ilu rekordach w grupie -w kolumnie Cena jest wpisana wartość, czyli w ilu rekordach -jest coś innego niż NULL --COUNT(DISTINCT Cena) - zlicza ile jest różnych cen w grupie (NULL nie jest liczony) SELECT COUNT(*) FROM Customers SELECT * FROM Customers SELECT COUNT(Region) FROM Customers SELECT COUNT(*) FROM Customers WHERE Region IS NULL SELECT COUNT(Region) FROM Customers WHERE Region IS NULL SELECT COUNT(DISTINCT Region) FROM Customers SELECT COUNT(DISTINCT Region) FROM Customers WHERE Region IS NOT NULL SELECT * FROM Products SELECT AVG(UnitPrice) FROM Products SELECT AVG(CAST(UnitPrice AS DECIMAL(15,10))) FROM Products SELECT * FROM Products SELECT CategoryID, AVG(UnitPrice) AS AveragePrice FROM Products GROUP BY CategoryID --To jest totalna kompromitacja:  SELECT CategoryID, ProductName, AVG(UnitPrice) AS AveragePrice FROM Products GROUP BY CategoryID --To nie ma specjalnie sensu: SELECT CategoryID, ProductName, AVG(UnitPrice) AS AveragePrice FROM Products GROUP BY CategoryID, ProductName

ORDER BY CategoryID SELECT * FROM Products SELECT * FROM Categories SELECT P.CategoryID, AVG(UnitPrice) AS AveragePrice FROM Products P JOIN Categories C ON P.CategoryID=C.CategoryID GROUP BY P.CategoryID SELECT P.CategoryID, CategoryName, AVG(UnitPrice) AS AveragePrice FROM Products P JOIN Categories C ON P.CategoryID=C.CategoryID GROUP BY P.CategoryID SELECT P.CategoryID, CategoryName, AVG(UnitPrice) AS AveragePrice FROM Products P JOIN Categories C ON P.CategoryID=C.CategoryID GROUP BY P.CategoryID,CategoryName --Ten sam zestaw danych można uzyskać bez „sztucznego” grupowania według CategoryName. --Będzie to omówione później.

Klauzule WHERE i HAVING w zdaniach SELECT z agregacjami. SELECT * FROM Products SELECT AVG(UnitPrice) FROM Products SELECT AVG(UnitPrice) Srednia FROM Products GROUP BY CategoryID ORDER BY Srednia DESC SELECT CategoryID, AVG(UnitPrice) Srednia FROM Products GROUP BY CategoryID ORDER BY Srednia DESC SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products GROUP BY CategoryID ORDER BY Srednia DESC SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products WHERE UnitPrice > 20 GROUP BY CategoryID ORDER BY Srednia DESC

--Chcemy wyświetlić dane ja powyżej, ale tylko dla --kategorii, w których średnia cena jest wiesza niż 25 --To jest źle: (!!!!!!). Na etapie realizacji klauzuli WHERE średnia nie jest jeszcze --znana. Ponadto nie wiadomo jeszcze jak grupować wiersze, więc w sumie nie wiadomo --jak liczyć średnią (w jakich grupach wierszy). Ilustrują to dwa poniższe przykłady: SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products WHERE Srednia > 25 GROUP BY CategoryID ORDER BY Srednia DESC --To jest źle: (!!!!!!) SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products WHERE AVG(UnitPrice) > 25 GROUP BY CategoryID ORDER BY Srednia DESC --Rozwiązanie poprawne --klauzula HAVING SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products GROUP BY CategoryID HAVING AVG(UnitPrice) > 25 ORDER BY Srednia DESC --Ale to jest źle. Na etapie realizacji klauzuli HAVING średnia --nie jest jeszcze wyliczona SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products GROUP BY CategoryID HAVING Srednia > 25 ORDER BY Srednia DESC --Wyliczmy podobne dane dla produktów, których --nazwa rozpoczyna się od litery A SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products WHERE ProductName LIKE 'a%' GROUP BY CategoryID HAVING AVG(UnitPrice) > 25 ORDER BY Srednia DESC --Wyliczmy podobne dane dla produktów, których --nazwa rozpoczyna się od litery C oraz tych produktów, których nazwa --rozpoczyna się od litery M. (WAŻNE – należy użyć operatora OR. Użycie operatora AND --jest tu niepoprawne (da pusty zestaw wierszy). SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products WHERE ProductName LIKE 'C%' OR ProductName LIKE 'M%' GROUP BY CategoryID HAVING AVG(UnitPrice) > 25 ORDER BY Srednia DESC --Uwaga: kryterium ProductName = ‘C%’ oznacza wybór produktów, których nazwa --jest równa 'C' (sama litera C):

SELECT * FROM Products WHERE ProductName = 'C%' --Wyświetlić średnią cenę i liczbę produktów w kategoriach. --Tym razem ograniczamy się do kategorii 2, 4 i 7 SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products GROUP BY CategoryID ORDER BY Srednia DESC SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products WHERE CategoryID = 2 OR CategoryID = 4 OR CategoryID = 7 GROUP BY CategoryID ORDER BY Srednia DESC --Zapis można uprościć stosując operator IN SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products WHERE CategoryID IN (2,4,7) GROUP BY CategoryID ORDER BY Srednia DESC --Kryterium wyboru w tym przypadku można umieścić --w klauzuli HAVING SELECT CategoryID, AVG(UnitPrice) Srednia, COUNT(*) LiczbaProduktow FROM Products GROUP BY CategoryID HAVING CategoryID IN (2,4,7) ORDER BY Srednia DESC

Złączenia tabel w zdaniach SELECT z agregacjami. --Dla każdego klienta (wystarczy CustomerID) należy podać --sumę kwot na wszystkich jego zamówieniach --Dwie kolumny: CustomerID, Sum SELECT * FROM Orders SELECT * FROM [Order Details] --Zróbmy najpierw prostsze zadanie: dla każdego zamówienia wyliczmy kwotę --Najpierw wypiszmy kwoty na wszystkich szczegółowych pozycjach zamówień: SELECT *, ROUND(UnitPrice*Quantity*CAST((1-Discount) AS MONEY),2) AS Amount FROM [Order Details] --Teraz pogrupujmy według OrderID i zsumujmy kwoty SELECT OrderID, SUM(ROUND(UnitPrice*Quantity*CAST((1-Discount) AS MONEY),2)) AS Amount FROM [Order Details] GROUP BY OrderID

--Ile jest pozycji na każdym zamówieniu? SELECT OrderID, SUM(ROUND(UnitPrice * Quantity * CAST((1-Discount) AS MONEY),2)) AS Amount, COUNT(*) AS NumberOfItems FROM [Order Details] GROUP BY OrderID --Wróćmy do zadania z wyliczeniem kwot dla każdego klienta. --Najpierw połączmy tabelę [Order Details] z Orders tak, aby dla każdej pozycji --zamówienia wypisać identyfikator klienta: SELECT OD.*, O.OrderID, O.OrderDate, O.CustomerID, ROUND(UnitPrice * Quantity * CAST((1-Discount) AS MONEY),2) AS Amount FROM [Order Details] OD JOIN Orders O ON OD.OrderID = O.OrderID --Rozwiązanie: SELECT O.CustomerID, SUM(ROUND(UnitPrice * Quantity * CAST((1-Discount) AS MONEY),2)) AS TotalAmount FROM [Order Details] OD JOIN Orders O ON OD.OrderID = O.OrderID GROUP BY CustomerID ORDER BY TotalAmount DESC --Dodajmy do powyższego zestawu rekordów liczbę zamówień, --złożonych przez każdego klienta. --Poniższe rozwiązanie jest złe (dlaczego?) SELECT O.CustomerID, SUM(ROUND(UnitPrice * Quantity * CAST((1-Discount) AS MONEY),2)) AS TotalAmount, COUNT(*) AS NumberOfOrders FROM [Order Details] OD JOIN Orders O ON OD.OrderID = O.OrderID GROUP BY CustomerID ORDER BY TotalAmount DESC

Jak poprawić powyższe rozwiązanie, by dla każdego klienta wyświetlana była liczba zamówień?...