SHARE
TWEET

Ths

EditorRUS Mar 17th, 2017 (edited) 80 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. DECLARE @EmployeesNamesAndIDs TABLE (FullName TEXT, ID INT)
  67. INSERT INTO @EmployeesNamesAndIDs (FullName, ID)
  68. (
  69.     SELECT Employees.LastName + ' ' + Employees.FirstName, Employees.EmployeeID
  70.     FROM Employees
  71. )
  72.  
  73. DECLARE @EmployeesOrders TABLE (ID INT, Amt INT)
  74. INSERT INTO @EmployeesOrders (ID, Amt)
  75. (
  76.     SELECT Employees.EmployeeID, COUNT(Orders.OrderID)
  77.     FROM Employees, Orders
  78.     WHERE Orders.EmployeeID = Employees.EmployeeID
  79.     GROUP BY Employees.EmployeeID
  80. )
  81.  
  82. DECLARE @OrdersPrices TABLE (ID INT, EID INT, Price REAL)
  83. INSERT INTO @OrdersPrices (ID, EID, Price)
  84. (
  85.     SELECT Orders.OrderID, Orders.EmployeeID, SUM([Order Details].UnitPrice*(1-[Order Details].Discount)*[Order Details].Quantity)
  86.     FROM Orders, [Order Details]
  87.     WHERE [Order Details].OrderID = Orders.OrderID
  88.     GROUP BY Orders.OrderID, Orders.EmployeeID
  89. )
  90.  
  91. DECLARE @EmployeesProfit TABLE (EID INT, FullProfit REAL)
  92. INSERT INTO @EmployeesProfit (EID, FullProfit)
  93. (
  94.     SELECT OP.EID, SUM(OP.Price)
  95.     FROM Employees, @OrdersPrices as OP
  96.     WHERE OP.EID = Employees.EmployeeID
  97.     GROUP BY OP.EID
  98. )
  99.  
  100. DECLARE @EmployeeRankings TABLE (FullName TEXT, OrdersAmt INT, OrdersPrice REAL)
  101. INSERT INTO @EmployeeRankings (FullName, OrdersPrice, OrdersAmt)
  102. (
  103.     SELECT ENamesAndIDs.FullName, EP.FullProfit, EO.Amt
  104.     FROM @EmployeesNamesAndIDs as ENamesAndIDs, @EmployeesProfit as EP, @EmployeesOrders as EO
  105.     WHERE ENamesAndIDs.ID = EP.EID AND ENamesAndIDs.ID = EO.ID
  106. )
  107.  
  108. DECLARE @MostOrders INT
  109. DECLARE @HighestProfit REAL
  110. DECLARE @LeastOrders INT
  111. DECLARE @LowestProfit REAL
  112.  
  113. SET @MostOrders = (SELECT MAX(ER.OrdersAmt) FROM @EmployeeRankings AS ER)
  114. SET @HighestProfit = (SELECT MAX(ER.OrdersPrice) FROM @EmployeeRankings AS ER)
  115. SET @LeastOrderS = (SELECT MIN(ER.OrdersAmt) FROM @EmployeeRankings AS ER)
  116. SET @LowestProfit = (SELECT MIN(ER.OrdersPrice) FROM @EmployeeRankings AS ER)
  117.  
  118. DECLARE @Info TABLE (Stat TEXT, FullName TEXT, Score INT)
  119. INSERT INTO @Info (Stat, FullName, Score)
  120. (
  121.     SELECT 'Best by orders', ER.FullName, ER.OrdersAmt FROM @EmployeeRankings AS ER WHERE ER.OrdersAmt = @MostOrders
  122.     UNION ALL
  123.     SELECT 'Best by profit', ER.FullName, ER.OrdersPrice FROM @EmployeeRankings AS ER WHERE ER.OrdersPrice = @HighestProfit
  124.     UNION ALL
  125.     SELECT 'Worst by orders', ER.FullName, ER.OrdersAmt FROM @EmployeeRankings AS ER WHERE ER.OrdersAmt = @LeastOrders
  126.     UNION ALL
  127.     SELECT 'Worst by profit', ER.FullName, ER.OrdersPrice FROM @EmployeeRankings AS ER WHERE ER.OrdersPrice = @LowestProfit
  128. )
  129. SELECT * FROM @Info
RAW Paste Data
Top