--Katarzyna Reszka-Hanaj
USE NORTHWND
--Łączenie tabel

/*
W klauzuli FROM określamy przede wszystkim źródła (zbiory) z których chcemy pobrać dane
FROM jest pierwszym krokiem przetwarzania danych

SELECT * FROM Tab_1, Tab_2 -> nie stusujemy zazwyczaj, dostajemy iloczyn kartezjanski
*/

Select * FROM Employees --9 wierszy
Select * from orders    --830 wierszy
select * from employees, orders --9 * 830 wierszy

--iloczyn kartezjański: podstawa wszystkich rodzaje złączeń
--odrzuca się z niego następnie wiersze, które 'nie pasują' do złączenia danego typu

/*
patrzymy na klucze glówne i obce

Złączenia wewnętrzne:
JOIN ... ON ...
INNER JOIN ... ON ...

Złączenia zewnętrzne
LEFT JOIN ... ON ...
LEFT OUTER JOIN ... ON ...
RIGHT JOIN ... ON ...
RIGHT OUTER JOIN ... ON ...
FULL JOIN ... ON ...
CROSS JOIN ... on...

select*
from tabela1 JOIN tabela2 ON tabela1.nazwaKolumny = tabela2.nazwaKolumny

Podstawowym rodzajem złączeń jest INNER JOIN:
Z iloczynu kartezjańskiego wybiera ono te wiersze dla  których warunek równania zostaje spełniony.
W żadnej z łączonych tabel kolumna użyta do łączenia nie może mieć wartości NULL.
Zwraca wyłacznie te wiersze, dla których kolumny użyte do złaczenia mają tę samą wartość.

LEFT JOIN zwraca:
1) wiersze dla których warunek złączenia jest spełniony,
2) wiersze z 'lewej' tabeli, dla których nie ma odpowiedników w 'prawej' tabeli.

RIGHT JOIN zwraca:
1) wiersze dla których warunek złączenia jest spełniony,
2) wiersze z 'prawej' tabeli, dla których nie ma odpowiedników w 'lewej' tabeli.

LEFT OUTER JOIN zwraca:
1) wiersze dla których warunek złączenia jest spełniony,
2) wiersze z 'lewej' tabeli, dla których nie ma odpowiedników w 'prawej' tabeli.

RIGHT OUTER JOIN zwraca:
1) wiersze dla których warunek złączenia jest spełniony,
2) wiersze z 'prawej' tabeli, dla których nie ma odpowiedników w 'lewej' tabeli.


FULL OUTER JOIN zwraca:
1) wiersze dla których warunek złączenia jest spełniony,
2) wiersze z 'prawej' tabeli, dla których nie ma odpowiedników w 'lewej' tabeli,
3) wiersze z 'lewej' tabeli, dla których nie ma odpowiedników w 'prawej' tabeli.

JOIN = INNER JOIN
LEFT JOIN = LEFT OUTER JOIN
RIGHT JOIN = RIGHT OUTER JOIN
FULL JOIN = FULL OUTER JOIN
CROSS JOIN = iloczyn kartezjański

Kroki przetwarzania:
1) iloczyn kartezjański (każdy z każdym)
2) określenie warunków połączeń (zdefiniowanych w ON) -> True, False, Unknown
3) Usuwanie wszystkich elementów z pośredniego zbioru wynikowego, dla których wynik połaczenia (pkt 2)
jest rózny od true
4) W zależności od typu (left, right, full), wykonywane jest dopełnienie zbioru, o wszystkie
elementy tabeli występującej po lewej, prawej, lub obydwu stronach operatora join, dla których wynik
warunków nie był spełniony (false lub unknown)

Krok 4 nie jest wykonywany dla inner join

ALIASY:
Skoro from jest wykonywane jako pierwsze, jeśli nadamy aliasy we from, możemy ich potem wszędzie indziej używać
 SELECT *
 FROM tabela1 T1 JOIN tabela2 T2 on T1.nazwaKolumny = T2.nazwaKolumny
 T1, T2 - to aliasy - z pierwszych liter
*/

--ZADANIA

--wyświetl numery zamówień i pracowników (imię i nazwisko)
SELECT FirstName, LastName, OrderID
FROM Employees E JOIN Orders O ON E.EmployeeID=O.EmployeeID

--zamówienia/klienci: numery i klienci
SELECT CompanyName, OrderID
FROM Customers C JOIN Orders O ON O.CustomerID=C.CustomerID

--zamówienia/klienci/pracownicy: numery i klienci i pracownicy (imię i nazwisko)
SELECT FirstName, LastName, CompanyName, O.OrderID
FROM Employees E JOIN Orders O ON E.EmployeeID=O.EmployeeID
JOIN Customers C ON O.CustomerID=C.CustomerID

--ile zmowien zrobili poszczegolni klienci
SELECT CompanyName, count(O.OrderID) AS 'liczba zamowien'
FROM Customers C JOIN Orders O ON O.CustomerID=C.CustomerID
GROUP BY CompanyName
ORDER BY 1

--ile zmowien z poszczegolnych krajow
SELECT C.Country AS kraj, count(O.OrderID) AS 'liczba zamowien'
FROM Customers C JOIN Orders O ON O.CustomerID=C.CustomerID
GROUP BY C.Country
ORDER BY 2 desc

--ile dostarczono produktów z poszczegolnych krajow
SELECT S.Country AS kraj, SUM(P.UnitsInStock) AS 'liczba dostarczonych produktów'
FROM Suppliers S JOIN Products P ON S.SupplierID = P.SupplierID
GROUP BY S.Country
ORDER BY 2 desc

--ile rodzajow produktów w danej kategorii
SELECT Cat.CategoryName AS kategoria, count(P.ProductID) AS 'liczba produktów'
FROM Categories Cat JOIN Products P ON Cat.CategoryID = P.CategoryID
GROUP BY Cat.CategoryName

--jakie produkty kupowali poszczeglni klienci
--odp: nazwa klienta (firma), produkty

SELECT DISTINCT C.CompanyName, P.ProductName
FROM Products P JOIN [Order Details] OD ON P.ProductID= OD.ProductID
JOIN Orders O ON OD.OrderID=O.OrderID
JOIN Customers C ON C.CustomerID = O.CustomerID
Order by 1

--podaj pracownikow oraz wartosc sprzedazy i udzielony rabat dla zmaowien, ktore realizowali
SELECT E.FirstName, E.LastName, OD.Discount, COUNT(O.OrderID) AS 'liczba zamowien', round(SUM(OD.Quantity*OD.UnitPrice), 2) AS Sprzedaz, round(AVG(OD.Quantity*OD.UnitPrice),2) AS sredniaWartosc
FROM Employees E JOIN
Orders O ON E.EmployeeID = O.EmployeeID JOIN
[Order details] OD ON O.OrderID = OD.OrderID
GROUP BY E.FirstName, E.LastName, OD.Discount

--podaj pracownikow oraz wartosc sprzedazy i udzielony rabat dla zmaowien, ktore realizowali
SELECT E.FirstName, E.LastName, round(SUM(OD.Quantity*OD.UnitPrice), 2) AS Sprzedaz, round(SUM(OD.Quantity*OD.Discount), 2)
FROM Employees E JOIN
Orders O ON E.EmployeeID = O.EmployeeID JOIN
[Order details] OD ON O.OrderID = OD.OrderID
GROUP BY E.FirstName, E.LastName
Order by 3 desc

--wybierz nazwy i nry tel klientow ktorym w 97r przesyłki dostarczała firma United Package
SELECT DISTINCT C.CompanyName,C.Phone,  S.CompanyName AS Dostawca
FROM Customers C JOIN
Orders O ON O.CustomerID=C.CustomerID JOIN
Shippers S ON S.ShipperID=O.ShipVia
WHERE DATEPART(yyyy, O.OrderDate)= 1997
AND S.CompanyName LIKE 'United Package'
ORDER BY 1

--ile zamowien obsluzyli spedytorzy
SELECT S.CompanyName, count(O.OrderID) AS liczba
FROM Orders O JOIN
Shippers S ON S.ShipperID=O.ShipVia
GROUP BY S.CompanyName
ORDER BY 2 desc

--dla każdego zamowienia łaczna liczba zamowionych jednostek
--wyświetl tylko zamówienia, gdzie łączna ilośc jedbostek jest większa od 250

SELECT O.OrderID, SUM(OD.Quantity) AS 'liczba jednostek'
FROM Orders O JOIN [Order Details] OD ON O.OrderID=OD.OrderID
GROUP BY O.OrderID
HAVING SUM(OD.Quantity)>250
ORDER BY 2 desc