SHARE
TWEET

Ths

EditorRUS Mar 17th, 2017 (edited) 78 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 1.
  2.  
  3. SELECT Customers.CompanyName, Products.ProductName
  4. FROM Products
  5. CROSS JOIN Customers, Orders, [Order Details]
  6. WHERE
  7.     Products.ProductID = [Order Details].ProductID AND
  8.     [Order Details].OrderID = Orders.OrderID AND
  9.     Customers.CustomerID = Orders.CustomerID
  10. ORDER BY CompanyName ASC
  11.  
  12. 2.4
  13.  
  14. SELECT Products.ProductName
  15. FROM Products
  16. LEFT JOIN
  17. (
  18.     SELECT Products.ProductName
  19.     FROM Products
  20.     JOIN Customers
  21.     ON Customers.CompanyName='Around the Horn'
  22.     JOIN Orders
  23.     ON Customers.CustomerID=Orders.CustomerID
  24.     JOIN [Order Details]
  25.     ON [Order Details].OrderID=Orders.OrderID
  26.     WHERE Products.ProductID=[Order Details].ProductID
  27. ) AS BoughtProducts
  28. ON BoughtProducts.ProductName = Products.ProductName
  29. WHERE BoughtProducts.ProductName IS NULL
  30.  
  31. 3.
  32. SELECT Orders.OrderID, [Order Details].UnitPrice*[Order Details].Discount*[Order Details].Quantity
  33. FROM Orders, [Order Details]
  34. WHERE [Order Details].OrderID = Orders.OrderID
  35.  
  36. 4.
  37. SELECT Orders.OrderID, SUM([Order Details].UnitPrice*[Order Details].Discount*[Order Details].Quantity)
  38. FROM Orders, [Order Details]
  39. WHERE [Order Details].OrderID = Orders.OrderID
  40. GROUP BY Orders.OrderID
  41.  
  42. 5.
  43. SELECT
  44.     Customers.CompanyName,
  45.     SUM([Order Details].UnitPrice*[Order Details].Discount*[Order Details].Quantity),
  46.     MAX([Order Details].UnitPrice*[Order Details].Discount*[Order Details].Quantity),
  47.     AVG([Order Details].UnitPrice*[Order Details].Discount*[Order Details].Quantity),
  48.     MIN([Order Details].UnitPrice*[Order Details].Discount*[Order Details].Quantity)
  49.  
  50. FROM Customers, Orders, [Order Details]
  51. WHERE
  52.     [Order Details].OrderID = Orders.OrderID AND
  53.     Orders.CustomerID = Customers.CustomerID
  54. GROUP BY Customers.CompanyName
  55. ORDER BY Customers.CompanyName ASC
  56.  
  57. 6.
  58. SELECT Employees.FirstName+' '+Employees.LastName, COUNT(Orders.EmployeeID)
  59. FROM Employees, Orders
  60. WHERE Orders.EmployeeID = Employees.EmployeeID
  61. GROUP BY Employees.FirstName+' '+Employees.LastName
  62. ORDER BY Employees.FirstName+' '+Employees.LastName ASC
  63.  
  64. 7.
  65.  
  66. (
  67.     SELECT Orders.OrderID, Orders.EmployeeID, SUM([Order Details].UnitPrice*[Order Details].Discount*[Order Details].Quantity) AS OrderPrice
  68.     FROM Orders, [Order Details]
  69.     WHERE [Order Details].OrderID = Orders.OrderID
  70.     GROUP BY Orders.OrderID
  71. )
RAW Paste Data
Top