laboratorium 1

/*
    Lab 1
--1. Zrobić zestawienie kategorie produktów, liczba sztuk
--2. Znaleźć nazwy 3 produktów zamawianych najczęściej w roku 2007
--3. Dla tych produktów z zapytania 2 podnieść ceny o 10%
--4. Usunąć wszystkie produkty, które nie były zamówione, jeżeli nie ma takiego produktu to dodać jakiś insertem
*/

/* Zadanie № 1 */
SELECT pc.categoryname, COUNT(pp.categoryid) AS 'Quantity'
FROM Production.Categories pc
LEFT JOIN Production.Products pp ON pp.categoryid = pc.categoryid
GROUP BY pc.categoryname

/* Zadanie № 2 */
SELECT TOP 3 pp.productname, COUNT(sod.orderid) AS 'Quantity', pp.unitprice AS 'Unit Price'
FROM Sales.Orders so, Sales.OrderDetails sod, Production.Products pp
WHERE pp.productid = sod.productid AND so.orderid = sod.orderid AND YEAR(so.orderdate) = 2007
GROUP BY pp.productname, pp.unitprice
ORDER BY count(sod.orderid) DESC;

/* Zadanie № 3 */
UPDATE pp
SET pp.unitprice = pp.unitprice + (pp.unitprice * 0.1)
FROM Sales.Orders so, Sales.OrderDetails sod, Production.Products pp
WHERE pp.productname in (SELECT TOP 3 pp.productname FROM Sales.Orders so, Sales.OrderDetails sod, Production.Products pp
WHERE pp.productid = sod.productid AND so.orderid = sod.orderid AND YEAR(so.orderdate) = 2007
GROUP BY pp.productname
ORDER BY count(sod.orderid) DESC);

/* Zadanie № 4 */
INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
VALUES ('test', 15, 1, 69.69, 0),
        ('test2', 16, 2, 420.69, 1);


SELECT pp.productid
FROM Production.Products pp
LEFT JOIN Sales.OrderDetails sod ON sod.productid = pp.productid
WHERE sod.productid IS NULL

DELETE pp
FROM Production.Products pp
LEFT JOIN Sales.OrderDetails sod ON sod.productid = pp.productid
WHERE sod.productid IS NULL