Advertisement
VSZM

2021-06-03 SQL

Mar 26th, 2021
1,031
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT P.CategoryID, CategoryName = (SELECT CategoryName FROM dbo.Categories C WHERE C.CategoryID = P.CategoryID), MaxUnitPrice = MAX(UnitPrice), MinUnitPrice = MIN(UnitPrice), AvgUnitPrice = AVG(UnitPrice)
  2. FROM dbo.Products P
  3. GROUP BY P.CategoryID;
  4.  
  5. SELECT O.OrderID, BuyerName = (SELECT C.ContactName FROM dbo.Customers C WHERE C.CustomerID = O.CustomerID), SellerName = (SELECT E.FirstName + ' ' + E.LastName FROM dbo.Employees E WHERE E.EmployeeID = O.EmployeeID)
  6. FROM dbo.Orders O;
  7.  
  8.  
  9. /*
  10. SELECT O.ShipCountry, CustomerName = (SELECT C.ContactName FROM dbo.Customers C WHERE C.CustomerID = O.CustomerID), max_weight = MAX(O.Freight)
  11. FROM dbo.Orders O
  12. GROUP BY O.ShipCountry, O.CustomerID
  13. */
  14.  
  15.  
  16. SELECT CategoryName = (SELECT CategoryName FROM dbo.Categories WHERE CategoryID = P.CategoryID), SUM(UnitsInStock)
  17. FROM dbo.Products P
  18. GROUP BY CategoryID
  19.  
  20.  
  21. SELECT
  22.     (SELECT E.FirstName + ' ' + E.LastName FROM dbo.Employees E WHERE E.EmployeeID = O.EmployeeID) AS Employee,
  23.     COUNT(*) AS order_count,
  24.     (SELECT AVG(COUNT)
  25.         FROM
  26.         (SELECT COUNT(*) AS COUNT
  27.         FROM dbo.Orders O
  28.         GROUP BY O.EmployeeID) AS counts) AS avg_count,
  29.     COUNT(*) - (SELECT AVG(COUNT)
  30.         FROM
  31.         (SELECT COUNT(*) AS COUNT
  32.         FROM dbo.Orders O
  33.         GROUP BY O.EmployeeID) AS counts) AS diff_from_avg
  34. FROM dbo.Orders O
  35. GROUP BY O.EmployeeID
  36. ORDER BY 2 DESC
  37.  
  38.  
  39.  
  40. SELECT OrderID, SUM(Price)
  41. FROM
  42.     (SELECT O.OrderID, O.ShippedDate, P.ProductName, P.UnitPrice * OD.Quantity AS Price
  43.     FROM dbo.Orders O INNER JOIN dbo.[ORDER Details] OD ON OD.OrderID = O.OrderID INNER JOIN dbo.Products P ON P.ProductID = OD.ProductID
  44.     ) AS orders
  45. GROUP BY OrderID
  46. ORDER BY 2 DESC
  47.  
  48.  
  49. SELECT
  50.    e1.EmployeeID
  51.    ,e1.LastName
  52.    ,e1.FirstName
  53.    ,e1.ReportsTo
  54.    ,Manager_LastName = e2.LastName
  55.    ,Manager_FirstName = E2.FirstName
  56. FROM [dbo].[Employees] e1
  57. LEFT JOIN [dbo].[Employees] e2
  58.    ON e1.ReportsTo = e2.EmployeeID
  59. ORDER BY
  60.    ReportsTo
  61.  
Advertisement
RAW Paste Data Copied
Advertisement