2021-06-03 SQL

SELECT P.CategoryID, CategoryName = (SELECT CategoryName FROM dbo.Categories C WHERE C.CategoryID = P.CategoryID), MaxUnitPrice = MAX(UnitPrice), MinUnitPrice = MIN(UnitPrice), AvgUnitPrice = AVG(UnitPrice)
FROM dbo.Products P
GROUP BY P.CategoryID;

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)
FROM dbo.Orders O;


/*
SELECT O.ShipCountry, CustomerName = (SELECT C.ContactName FROM dbo.Customers C WHERE C.CustomerID = O.CustomerID), max_weight = MAX(O.Freight)
FROM dbo.Orders O
GROUP BY O.ShipCountry, O.CustomerID
*/


SELECT CategoryName = (SELECT CategoryName FROM dbo.Categories WHERE CategoryID = P.CategoryID), SUM(UnitsInStock)
FROM dbo.Products P
GROUP BY CategoryID


SELECT
    (SELECT E.FirstName + ' ' + E.LastName FROM dbo.Employees E WHERE E.EmployeeID = O.EmployeeID) AS Employee,
    COUNT(*) AS order_count,
    (SELECT AVG(COUNT)
        FROM
        (SELECT COUNT(*) AS COUNT
        FROM dbo.Orders O
        GROUP BY O.EmployeeID) AS counts) AS avg_count,
    COUNT(*) - (SELECT AVG(COUNT)
        FROM
        (SELECT COUNT(*) AS COUNT
        FROM dbo.Orders O
        GROUP BY O.EmployeeID) AS counts) AS diff_from_avg
FROM dbo.Orders O
GROUP BY O.EmployeeID
ORDER BY 2 DESC



SELECT OrderID, SUM(Price)
FROM
    (SELECT O.OrderID, O.ShippedDate, P.ProductName, P.UnitPrice * OD.Quantity AS Price
    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
    ) AS orders
GROUP BY OrderID
ORDER BY 2 DESC


SELECT
   e1.EmployeeID
   ,e1.LastName
   ,e1.FirstName
   ,e1.ReportsTo
   ,Manager_LastName = e2.LastName
   ,Manager_FirstName = E2.FirstName
FROM [dbo].[Employees] e1
LEFT JOIN [dbo].[Employees] e2
   ON e1.ReportsTo = e2.EmployeeID
ORDER BY
   ReportsTo