Monn_9999

Laboratorium 2

Oct 28th, 2020 (edited)
947
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.     Lab 2
  3. -- Dla wszystkich zamówień i klientów wypisać informacje w postaci:
  4. -- 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
  5. -- 2. Dla każdego z menedżerów ustalić ilu każdy ma bezpośrednich i wszystkich podwładnych
  6. -- 3. Zrobić totalizer zamówień utworzyć tabelę CustomerTotalizer z danymi customerid, totalvalue, ordercount i wypełnić ją danymi
  7. -- 4. Znaleźć ostatni numer zamówienia (i datę jego realizacji), dla którego suma wartości zamówień < 500000
  8. -- 5. Utworzyc widok zawierający dla każdego z dostawców liczbę produktów zamówionych w poszczególnych latach
  9. */
  10.  
  11. /* Zadanie № 1 */
  12. SELECT sc.companyname AS 'Company Name', SUM(sod.qty * sod.unitprice) AS 'Greater than 2000'
  13. FROM Sales.Orders so, Sales.OrderDetails sod, Sales.Customers sc
  14. WHERE so.orderid = sod.orderid AND so.custid = sc.custid
  15. GROUP BY sc.companyname
  16. HAVING SUM(sod.qty * sod.unitprice) > 2000
  17. ORDER BY SUM(sod.qty * sod.unitprice) DESC
  18.  
  19. SELECT sc.companyname AS 'Company Name', SUM(sod.qty * sod.unitprice) AS 'Between [1000, 2000]'
  20. FROM Sales.Orders so, Sales.OrderDetails sod, Sales.Customers sc
  21. WHERE so.orderid = sod.orderid AND so.custid = sc.custid
  22. GROUP BY sc.companyname
  23. HAVING SUM(sod.qty * sod.unitprice) BETWEEN 1000 AND 2000
  24. ORDER BY SUM(sod.qty * sod.unitprice) DESC;
  25.  
  26. SELECT sc.companyname AS 'Company Name', SUM(sod.qty * sod.unitprice) AS 'Less than 1000'
  27. FROM Sales.Orders so, Sales.OrderDetails sod, Sales.Customers sc
  28. WHERE so.orderid = sod.orderid AND so.custid = sc.custid
  29. GROUP BY sc.companyname
  30. HAVING SUM(sod.qty * sod.unitprice) < 1000
  31. ORDER BY SUM(sod.qty * sod.unitprice) DESC;
  32.  
  33. /* Zadanie № 2 */
  34. SELECT man.firstname AS 'First Name', COUNT(emp.mgrid) AS 'Bez', COUNT(man.mgrid) AS 'Wszystkie'
  35. FROM HR.Employees emp JOIN HR.Employees man ON (emp.mgrid = man.mgrid)
  36. WHERE emp.mgrid is null OR emp.mgrid = man.mgrid
  37. GROUP BY man.firstname, emp.mgrid
  38.  
  39. /* Zadanie № 3 */
  40.  
  41. CREATE TABLE CustomerTotalizer(
  42.     customerid INT NOT NULL Primary key,
  43.     totalvalue INT DEFAULT NULL,
  44.     ordercount INT DEFAULT NULL
  45. );
  46.  
  47. INSERT INTO CustomerTotalizer(customerid, totalvalue, ordercount)
  48. SELECT sc.custid AS 'Customer ID', SUM(sod.qty * sod.unitprice) AS 'Total Value', COUNT(so.orderid) AS 'Order Count'
  49. FROM Sales.Customers sc, Sales.OrderDetails sod, Sales.Orders so
  50. WHERE sc.custid = so.custid AND sod.orderid = so.orderid
  51. GROUP BY sc.custid
  52. ORDER BY sc.custid ASC;
  53.  
  54. /* Zadanie № 4 */
  55. SELECT TOP(1) so.orderid, so.orderdate
  56. FROM Sales.Orders so, Sales.OrderDetails od
  57. WHERE od.orderid = so.orderid
  58. GROUP BY so.orderid, so.orderdate
  59. HAVING SUM(od.qty * od.unitprice) < 50000
  60. ORDER BY so.orderdate DESC;
  61.  
  62. /* Zadanie № 5 */
  63. SELECT sc.companyname AS 'Company Name', SUM(sod.qty) as 'Quantity', YEAR(so.orderdate) AS 'For the year'
  64. FROM Sales.OrderDetails sod, Sales.Orders so, Sales.Customers sc
  65. WHERE sod.orderid = so.orderid AND sc.custid = so.custid
  66. GROUP BY sc.companyname, YEAR(so.orderdate)
  67. ORDER BY sc.companyname, YEAR(so.orderdate)
RAW Paste Data