--1. Список неотправленных заказов (пустая дата отправки - ShippedDate)
SELECT *
FROM ORders
WHERE ShippedDate IS NULL
--Agregat Functions: SUM, COUNT, MIN, MAX, AVG
--Стоимость каждого заказа (первые 10 записей)
SELECT TOP 10 OrderID, SUM(UnitPrice*Quantity*(1-Discount)) as Summa, ROUND(SUM(UnitPrice*Quantity*(1-Discount)),2) AS TotalSumma, COUNT(*) as RowsCount
FROM [Order Details]
GROUP BY ORDERID
--3.Самая низкая, высокая и средняя цена каждой категории (CategoryID)
-- Functions MIN, MAX, AVG
SELECT CategoryID, MIN(UnitPrice) as MinPrice, MAX(UnitPrice) AS MaxPrice,
AVG(UnitPrice) AS AveragePrice
FROM Products
GROUP BY CategoryID
ORDER BY AVG(UnitPrice)
--3.Самая низкая, высокая и средняя цена каждой категории
SELECT P.CategoryID, C.CategoryName, MIN(UnitPrice) as MinPrice, MAX(UnitPrice) AS MaxPrice,
AVG(UnitPrice) AS AveragePrice
FROM Products As P, Categories as C
WHERE P.CategoryID=C.CategoryID
GROUP BY P.CategoryID, C.CategoryName
ORDER BY AVG(UnitPrice)
--3.Самая низкая, высокая и средняя цена каждой категории
SELECT P.CategoryID, C.CategoryName, MIN(UnitPrice) as MinPrice, MAX(UnitPrice) AS MaxPrice,
AVG(UnitPrice) AS AveragePrice
FROM Products As P INNER JOIN Categories as C --Соединяем таблицы
ON P.CategoryID=C.CategoryID
GROUP BY P.CategoryID, C.CategoryName
ORDER BY AVG(UnitPrice)
--Число и стоимость заказов по месяцам и годам
SELECT Year(O.OrderDate) as Year, Month(O.OrderDate) as Month,
COUNT(O.OrderID) as OrderCount,--Число заказов
ROUND(SUM(D.UnitPrice*D.Quantity*(1-Discount)),2) as TotalSumma --стоимость заказов
FROM [Order Details] AS D INNER JOIN Orders AS O
ON D.OrderID=O.OrderID
GROUP BY Year(O.OrderDate) , Month(O.OrderDate)
ORDER BY Year(O.OrderDate) , Month(O.OrderDate)
--1.Число и стоимость заказов по странам (ShipCountry)
--2. Число и стоимость заказов по работникам (EmployeeID)
--3. Число и стоимость заказов по работникам (Employee Firstname, lastname)
SELECT O.ShipCountry,
COUNT(O.OrderID) as OrderCount,--Число заказов
ROUND(SUM(D.UnitPrice*D.Quantity*(1-Discount)),2) as TotalSumma --стоимость заказов
FROM [Order Details] AS D INNER JOIN Orders AS O
ON D.OrderID=O.OrderID
GROUP BY o.ShipCountry --по странам (ShipCountry)
ORDER BY o.ShipCountry
--Число и стоимость заказов по работникам
CREATE VIEW vwOrdersByEmployee As
SELECT O.EmployeeID, E.FirstName, E.LastName,
COUNT(O.OrderID) as OrderCount,--Число заказов
ROUND(SUM(D.UnitPrice*D.Quantity*(1-Discount)),2) as TotalSumma --стоимость заказов
FROM [Order Details] AS D INNER JOIN Orders AS O --Соединяем таблицы Заказ и Детали заказа
ON D.OrderID=O.OrderID
INNER JOIN Employees as E -- Соединяем с таблицей Работников
On E.EmployeeID=O.EmployeeID
GROUP BY O.EmployeeID, E.FirstName, E.LastName
--ORDER BY COUNT(O.OrderID) desc
SELECT *
FROM vwOrdersByEmployee
ORDER BY TotalSumma
-- Наиболее продаваемые товары (по числу проданных штук) - название, число продаж
SELECT TOP 10 WITH TIES ProductName, P.ProductID, SUM(Quantity) AS ProductsTotal
FROM [Order Details] D INNER JOIN Products P
ON P.ProductID=D.ProductID
GROUP BY ProductName, P.ProductID
ORDER BY SUM(Quantity) DESC
--Число подчиненных
USE Northwind
go
SELECT E1.EmployeeID, E1.FirstName, E1.LastName, E1.Title, COUNT(E2.ReportsTo) as Reported
FROM Employees E1 Inner JOIN Employees E2 -- SELF JOIN, Самосоединение
ON E1.EmployeeID=E2.ReportsTo
GROUP BY E1.EmployeeID, E1.FirstName, E1.LastName, E1.Title
SELECT EmployeeID, ReportsTo FROM Employees