Laboratorium 2

/*
    Lab 2
-- Dla wszystkich zamówień i klientów wypisać informacje w postaci:
-- 1. nazwa klienta, liczba zamówień o wartości > 2000, liczba zamówień o wartości <1000, 2000>, liczba zamówień o wartości < 1000
-- 2. Dla każdego z menedżerów ustalić ilu każdy ma bezpośrednich i wszystkich podwładnych
-- 3. Zrobić totalizer zamówień utworzyć tabelę CustomerTotalizer z danymi customerid, totalvalue, ordercount i wypełnić ją danymi
-- 4. Znaleźć ostatni numer zamówienia (i datę jego realizacji), dla którego suma wartości zamówień < 500000
-- 5. Utworzyc widok zawierający dla każdego z dostawców liczbę produktów zamówionych w poszczególnych latach
*/

/* Zadanie № 1 */
SELECT sc.companyname AS 'Company Name', SUM(sod.qty * sod.unitprice) AS 'Greater than 2000'
FROM Sales.Orders so, Sales.OrderDetails sod, Sales.Customers sc
WHERE so.orderid = sod.orderid AND so.custid = sc.custid
GROUP BY sc.companyname
HAVING SUM(sod.qty * sod.unitprice) > 2000
ORDER BY SUM(sod.qty * sod.unitprice) DESC

SELECT sc.companyname AS 'Company Name', SUM(sod.qty * sod.unitprice) AS 'Between [1000, 2000]'
FROM Sales.Orders so, Sales.OrderDetails sod, Sales.Customers sc
WHERE so.orderid = sod.orderid AND so.custid = sc.custid
GROUP BY sc.companyname
HAVING SUM(sod.qty * sod.unitprice) BETWEEN 1000 AND 2000
ORDER BY SUM(sod.qty * sod.unitprice) DESC;

SELECT sc.companyname AS 'Company Name', SUM(sod.qty * sod.unitprice) AS 'Less than 1000'
FROM Sales.Orders so, Sales.OrderDetails sod, Sales.Customers sc
WHERE so.orderid = sod.orderid AND so.custid = sc.custid
GROUP BY sc.companyname
HAVING SUM(sod.qty * sod.unitprice) < 1000
ORDER BY SUM(sod.qty * sod.unitprice) DESC;

/* Zadanie № 2 */
SELECT man.firstname AS 'First Name', COUNT(emp.mgrid) AS 'Bez', COUNT(man.mgrid) AS 'Wszystkie'
FROM HR.Employees emp JOIN HR.Employees man ON (emp.mgrid = man.mgrid)
WHERE emp.mgrid is null OR emp.mgrid = man.mgrid
GROUP BY man.firstname, emp.mgrid

/* Zadanie № 3 */

CREATE TABLE CustomerTotalizer(
    customerid INT NOT NULL Primary key,
    totalvalue INT DEFAULT NULL,
    ordercount INT DEFAULT NULL
);

INSERT INTO CustomerTotalizer(customerid, totalvalue, ordercount)
SELECT sc.custid AS 'Customer ID', SUM(sod.qty * sod.unitprice) AS 'Total Value', COUNT(so.orderid) AS 'Order Count'
FROM Sales.Customers sc, Sales.OrderDetails sod, Sales.Orders so
WHERE sc.custid = so.custid AND sod.orderid = so.orderid
GROUP BY sc.custid
ORDER BY sc.custid ASC;

/* Zadanie № 4 */
SELECT TOP(1) so.orderid, so.orderdate
FROM Sales.Orders so, Sales.OrderDetails od
WHERE od.orderid = so.orderid
GROUP BY so.orderid, so.orderdate
HAVING SUM(od.qty * od.unitprice) < 50000
ORDER BY so.orderdate DESC;

/* Zadanie № 5 */
SELECT sc.companyname AS 'Company Name', SUM(sod.qty) as 'Quantity', YEAR(so.orderdate) AS 'For the year'
FROM Sales.OrderDetails sod, Sales.Orders so, Sales.Customers sc
WHERE sod.orderid = so.orderid AND sc.custid = so.custid
GROUP BY sc.companyname, YEAR(so.orderdate)
ORDER BY sc.companyname, YEAR(so.orderdate)