Monn_9999

laboratorium 1

Oct 28th, 2020
1,278
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.     Lab 1
  3. --1. Zrobić zestawienie kategorie produktów, liczba sztuk
  4. --2. Znaleźć nazwy 3 produktów zamawianych najczęściej w roku 2007
  5. --3. Dla tych produktów z zapytania 2 podnieść ceny o 10%
  6. --4. Usunąć wszystkie produkty, które nie były zamówione, jeżeli nie ma takiego produktu to dodać jakiś insertem
  7. */
  8.  
  9. /* Zadanie № 1 */
  10. SELECT pc.categoryname, COUNT(pp.categoryid) AS 'Quantity'
  11. FROM Production.Categories pc
  12. LEFT JOIN Production.Products pp ON pp.categoryid = pc.categoryid
  13. GROUP BY pc.categoryname
  14.  
  15. /* Zadanie № 2 */
  16. SELECT TOP 3 pp.productname, COUNT(sod.orderid) AS 'Quantity', pp.unitprice AS 'Unit Price'
  17. FROM Sales.Orders so, Sales.OrderDetails sod, Production.Products pp
  18. WHERE pp.productid = sod.productid AND so.orderid = sod.orderid AND YEAR(so.orderdate) = 2007
  19. GROUP BY pp.productname, pp.unitprice
  20. ORDER BY count(sod.orderid) DESC;
  21.  
  22. /* Zadanie № 3 */
  23. UPDATE pp
  24. SET pp.unitprice = pp.unitprice + (pp.unitprice * 0.1)
  25. FROM Sales.Orders so, Sales.OrderDetails sod, Production.Products pp
  26. WHERE pp.productname in (SELECT TOP 3 pp.productname FROM Sales.Orders so, Sales.OrderDetails sod, Production.Products pp
  27. WHERE pp.productid = sod.productid AND so.orderid = sod.orderid AND YEAR(so.orderdate) = 2007
  28. GROUP BY pp.productname
  29. ORDER BY count(sod.orderid) DESC);
  30.  
  31. /* Zadanie № 4 */
  32. INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
  33. VALUES ('test', 15, 1, 69.69, 0),
  34.         ('test2', 16, 2, 420.69, 1);
  35.  
  36.            
  37. SELECT pp.productid
  38. FROM Production.Products pp
  39. LEFT JOIN Sales.OrderDetails sod ON sod.productid = pp.productid
  40. WHERE sod.productid IS NULL
  41.  
  42. DELETE pp
  43. FROM Production.Products pp
  44. LEFT JOIN Sales.OrderDetails sod ON sod.productid = pp.productid
  45. WHERE sod.productid IS NULL
RAW Paste Data