SHARE
TWEET

Untitled

a guest Dec 6th, 2019 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. use Northwind
  2.  
  3. --Zad 1.1
  4.  
  5. Select B.CompanyName, B.Phone
  6. From Orders as A
  7.     Inner Join Customers as B
  8.         on A.CustomerID = B.CustomerID
  9.     Inner Join Shippers as C
  10.         on A.ShipVia = C.ShipperID
  11. Where Year(A.ShippedDate)  = '1997'
  12. AND C.CompanyName = 'United Package'
  13. Group By B.CompanyName, B.Phone
  14.  
  15. Select B.CompanyName, B.Phone
  16. From Customers as B
  17. Where
  18. Exists ( Select *
  19.         from Orders as A
  20.         Where Exists (Select *
  21.                       From Shippers as C
  22.                       Where C.CompanyName = 'United Package'
  23.                       AND C.ShipperID = A.ShipVia)
  24.         AND Year(A.ShippedDate)  = '1997'
  25.         AND B.CustomerID = A.CustomerID)
  26.  
  27. --Zad 1.3
  28.  
  29. Select B.CompanyName, B.Phone
  30. From Customers as B
  31. Where  Not Exists( Select *
  32.                   From Orders as A
  33.                   Where A.CustomerID = B.CustomerID
  34.                   AND EXISTS ( SELECT *
  35.                                FROM [Order Details] as C
  36.                                Where C.OrderID = A.OrderID
  37.                                AND EXISTS (SELECT *
  38.                                            FROM Products as D
  39.                                            WHERE
  40.                                            D.ProductID = C.ProductID
  41.                                            AND EXISTS (Select *
  42.                                                        FROM Categories as E
  43.                                                        Where E.CategoryID = D.CategoryID
  44.                                                        AND E.CategoryName = 'Confections'))))
  45.  
  46. Select B.CompanyName , B.Phone
  47. From Orders as A
  48.     INNER JOIN [Order Details] as C
  49.         On C.OrderID = A.OrderID
  50.     INNER JOIN Products as D
  51.         ON D.ProductID = C.ProductID
  52.     INNER JOIN Categories as E
  53.         ON E.CategoryID = D.CategoryID AND E.CategoryName = 'Confections'
  54.     RIGHT JOIN Customers as B
  55.         On B.CustomerID = A.CustomerID
  56. WHERE A.Freight IS NULL
  57. GROUP BY B.CompanyName, B.Phone
  58.  --2.1
  59. SELECT A.ProductName, (SELECT  MAX(B.Quantity)
  60.          FROM [Order Details] as B
  61.          WHERE A.ProductID = B.ProductID)
  62. FROM Products A
  63.  
  64. SELECT A.ProductName, MAX(B.Quantity)
  65. FROM Products A
  66.     INNER JOIN [Order Details] as B
  67.         ON A.ProductID = B.ProductID
  68. GROUP BY A.ProductName
  69.  
  70. --2.2
  71.  
  72. SELECT A.ProductName
  73. FROM Products as A
  74. WHERE
  75. A.UnitPrice > (Select Avg(B.UnitPrice)
  76.                FROM Products as B)
  77. -- 3.1
  78. SELECT A.ProductName
  79. FROM Products as A
  80. WHERE
  81. A.UnitPrice > (Select Avg(B.UnitPrice)
  82.                FROM Products as B
  83.                WHERE A.CategoryID = B.CategoryID)
  84.  
  85. SELECT A.ProductName, A.UnitPrice,
  86.                (Select Avg(B.UnitPrice)
  87.                FROM Products as B) as AVERAGE,
  88.                A.UnitPrice - (Select Avg(B.UnitPrice)
  89.                FROM Products as B)
  90. FROM Products as A
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top