-- wypisz przewoźników wraz z liczbą wykonanych zamówień w latach 1996 - 1998 z podziałem na lata i wewnątrz lat na miesiące. wynik ograniczyć do przewoźników których suma zamówień w danym okresie jest wyższa niż średnia suma wykonanych zamówień dla wszystkich przewoźników w danym okresie

use Northwind;
SELECT s.CompanyName, YEAR(o.OrderDate), MONTH(o.OrderDate), COUNT(*)
FROM Shippers s
JOIN Orders o ON o.ShipVia = s.ShipperID
WHERE YEAR(o.OrderDate) BETWEEN 1996 AND 1998 AND (
        SELECT SUM(UnitPrice * Quantity * (1 - Discount)) AS s1
        FROM Orders o2
        JOIN [Order Details] d ON d.OrderID = o2.OrderID
        WHERE YEAR(o2.OrderDate) = YEAR(o.OrderDate) AND MONTH(o2.OrderDate) = MONTH(o.OrderDate) AND o2.ShipVia = s.ShipperID
) > (
    SELECT AVG(s1) FROM (
        SELECT SUM(UnitPrice * Quantity * (1 - Discount)) AS s1
        FROM Orders o2
        JOIN [Order Details] d ON d.OrderID = o2.OrderID
        WHERE YEAR(o2.OrderDate) = YEAR(o.OrderDate) AND MONTH(o2.OrderDate) = MONTH(o.OrderDate)
        GROUP BY o2.ShipVia
    ) xdd
)
GROUP BY s.ShipperID, s.CompanyName, YEAR(o.OrderDate), MONTH(o.OrderDate);

-- wypisz imiona i nazwiska, adresy użytkowników biblioteki, jeśli są dzieckiem to wypisz adres rodzica, wypisz ilość aktualnie wypożyczonych książek, liczbę dni ponad due_date dla wypożyczonych książek, rozróżnić dorosłych i dzieci, dla dzieci które przetrzymują książki ponad due_date wypisać imie i nazwisko rodzica

use library;
SELECT m.firstname, m.lastname, CONCAT(a.street, ' ', a.city, ' ', a.state, ' ', a.zip),
    IIF(j.member_no IS NULL, 'dorosly', 'dziecko'), (
        SELECT COUNT(*) FROM loan l1 WHERE l1.member_no = m.member_no
    ), (
        SELECT SUM(IIF(DATEDIFF(day, GETDATE(), due_date) < 0, DATEDIFF(day, GETDATE(), due_date), 0)) FROM loan l2 WHERE l2.member_no = m.member_no
    )
FROM member m
LEFT JOIN juvenile j on m.member_no = j.member_no
LEFT JOIN adult a ON m.member_no = a.member_no OR j.adult_member_no = a.member_no;

-- wypisać nazwę klienta który złożył zamówienie 23/05/1997 i imię i nazwisko pracownika jeśli obsługiwał to zamówienie i ma podwładnych

use Northwind;
SELECT c.CompanyName, e.FirstName, e.LastName
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
LEFT JOIN Employees e ON e.EmployeeID = o.EmployeeID AND EXISTS (SELECT * FROM Employees e2 WHERE e2.ReportsTo = e.EmployeeID)
WHERE OrderDate = '1997/05/23';

-- wypisać imię i nazwisko i adres użytkowników biblioteki ze stanu kalifornia którzy nigdy nie wypożyczyli książek oraz użytkowników którzy nie mają dzieci zapisanych do biblioteki i wypożyczyli ponad 3 książki

use library;
SELECT m.firstname, m.lastname FROM member m
JOIN adult a ON a.member_no = m.member_no
LEFT JOIN juvenile j ON j.adult_member_no = m.member_no
WHERE j.member_no IS NULL AND a.state = 'CA'
AND (
    SELECT COUNT(*) FROM loan l WHERE l.member_no = m.member_no
) + (
    SELECT COUNT(*) FROM loanhist h WHERE h.member_no = m.member_no
) > 3;

-- wypisz użytkowników biblioteki którzy nie wypożyczyli w 1996 roku książki autorstwa samuela smile’a w tłumaczeniu arabskim na joinach i na selektach

use library
SELECT DISTINCT m.firstname, m.lastname
FROM member m
JOIN loanhist h ON h.member_no = m.member_no
JOIN copy c ON c.copy_no = h.member_no
JOIN item i ON i.isbn = c.isbn
JOIN title t ON c.title_no = t.title_no
WHERE i.translation = 'Arabic' AND t.author = 'Samuel Smiles' AND YEAR(h.out_date) = 1996;