In [1]:
--1. Список неотправленных заказов (пустая дата отправки - ShippedDate)
SELECT * 
FROM ORders
WHERE ShippedDate IS NULL
(21 rows affected)
Total execution time: 00:00:00.116
Out[1]:
OrderIDCustomerIDEmployeeIDOrderDateRequiredDateShippedDateShipViaFreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountry
11008ERNSH71998-04-08 00:00:00.0001998-05-06 00:00:00.000NULL379,4600Ernst HandelKirchgasse 6GrazNULL8010Austria
11019RANCH61998-04-13 00:00:00.0001998-05-11 00:00:00.000NULL33,1700Rancho grandeAv. del Libertador 900Buenos AiresNULL1010Argentina
11039LINOD11998-04-21 00:00:00.0001998-05-19 00:00:00.000NULL265,0000LINO-DelicatesesAve. 5 de Mayo PorlamarI. de MargaritaNueva Esparta4980Venezuela
11040GREAL41998-04-22 00:00:00.0001998-05-20 00:00:00.000NULL318,8400Great Lakes Food Market2732 Baker Blvd.EugeneOR97403USA
11045BOTTM61998-04-23 00:00:00.0001998-05-21 00:00:00.000NULL270,5800Bottom-Dollar Markets23 Tsawassen Blvd.TsawassenBCT2F 8M4Canada
11051LAMAI71998-04-27 00:00:00.0001998-05-25 00:00:00.000NULL32,7900La maison d'Asie1 rue Alsace-LorraineToulouseNULL31000France
11054CACTU81998-04-28 00:00:00.0001998-05-26 00:00:00.000NULL10,3300Cactus Comidas para llevarCerrito 333Buenos AiresNULL1010Argentina
11058BLAUS91998-04-29 00:00:00.0001998-05-27 00:00:00.000NULL331,1400Blauer See DelikatessenForsterstr. 57MannheimNULL68306Germany
11059RICAR21998-04-29 00:00:00.0001998-06-10 00:00:00.000NULL285,8000Ricardo AdocicadosAv. Copacabana, 267Rio de JaneiroRJ02389-890Brazil
11061GREAL41998-04-30 00:00:00.0001998-06-11 00:00:00.000NULL314,0100Great Lakes Food Market2732 Baker Blvd.EugeneOR97403USA
11062REGGC41998-04-30 00:00:00.0001998-05-28 00:00:00.000NULL229,9300Reggiani CaseificiStrada Provinciale 124Reggio EmiliaNULL42100Italy
11065LILAS81998-05-01 00:00:00.0001998-05-29 00:00:00.000NULL112,9100LILA-SupermercadoCarrera 52 con Ave. Bolívar #65-98 Llano LargoBarquisimetoLara3508Venezuela
11068QUEEN81998-05-04 00:00:00.0001998-06-01 00:00:00.000NULL281,7500Queen CozinhaAlameda dos Canàrios, 891Sao PauloSP05487-020Brazil
11070LEHMS21998-05-05 00:00:00.0001998-06-02 00:00:00.000NULL1136,0000Lehmanns MarktstandMagazinweg 7Frankfurt a.M.NULL60528Germany
11071LILAS11998-05-05 00:00:00.0001998-06-02 00:00:00.000NULL10,9300LILA-SupermercadoCarrera 52 con Ave. Bolívar #65-98 Llano LargoBarquisimetoLara3508Venezuela
11072ERNSH41998-05-05 00:00:00.0001998-06-02 00:00:00.000NULL2258,6400Ernst HandelKirchgasse 6GrazNULL8010Austria
11073PERIC21998-05-05 00:00:00.0001998-06-02 00:00:00.000NULL224,9500Pericles Comidas clásicasCalle Dr. Jorge Cash 321México D.F.NULL05033Mexico
11074SIMOB71998-05-06 00:00:00.0001998-06-03 00:00:00.000NULL218,4400Simons bistroVinbæltet 34KobenhavnNULL1734Denmark
11075RICSU81998-05-06 00:00:00.0001998-06-03 00:00:00.000NULL26,1900Richter SupermarktStarenweg 5GenèveNULL1204Switzerland
11076BONAP41998-05-06 00:00:00.0001998-06-03 00:00:00.000NULL238,2800Bon app'12, rue des BouchersMarseilleNULL13008France
11077RATTC11998-05-06 00:00:00.0001998-06-03 00:00:00.000NULL28,5300Rattlesnake Canyon Grocery2817 Milton Dr.AlbuquerqueNM87110USA
In [3]:
--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
(10 rows affected)
Total execution time: 00:00:00.093
Out[3]:
OrderIDSummaTotalSummaRowsCount
102484404403
102491863.39999389648441863.42
102501552.60003662109381552.63
10251654.0600051879883654.063
102523597.899902343753597.93
102531444.79998779296881444.83
10254556.6200332641602556.623
102552490.52490.54
10256517.8000030517578517.82
102571119.9000015258791119.93
In [9]:
--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)
(9 rows affected)
Total execution time: 00:00:00.088
Out[9]:
CategoryIDMinPriceMaxPriceAveragePrice
NULL0,00000,00000,0000
57,000038,000020,2500
86,000062,500020,6825
210,000043,900023,0625
39,200081,000025,1600
42,500055,000028,7300
710,000053,000032,3700
14,5000263,500038,4653
67,4500123,790054,0066
In [11]:
--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)
(8 rows affected)
Total execution time: 00:00:00.049
Out[11]:
CategoryIDCategoryNameMinPriceMaxPriceAveragePrice
5Grains/Cereals7,000038,000020,2500
8Seafood6,000062,500020,6825
2Condiments10,000043,900023,0625
3Confections9,200081,000025,1600
4Dairy Products2,500055,000028,7300
7Produce10,000053,000032,3700
1Beverages4,5000263,500038,4653
6Meat/Poultry7,4500123,790054,0066
In [13]:
--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)
(8 rows affected)
Total execution time: 00:00:00.093
Out[13]:
CategoryIDCategoryNameMinPriceMaxPriceAveragePrice
5Grains/Cereals7,000038,000020,2500
8Seafood6,000062,500020,6825
2Condiments10,000043,900023,0625
3Confections9,200081,000025,1600
4Dairy Products2,500055,000028,7300
7Produce10,000053,000032,3700
1Beverages4,5000263,500038,4653
6Meat/Poultry7,4500123,790054,0066
In [18]:
--Число и стоимость заказов по месяцам и годам
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)
(23 rows affected)
Total execution time: 00:00:00.058
Out[18]:
YearMonthOrderCountTotalSumma
199675927861,89
199686925485,27
199695726381,4
1996107337515,73
1996116645600,04
1996128145239,63
199718561258,07
199727938483,63
199737738547,22
199748153032,95
199759653781,29
199767636362,8
199777751020,86
199788447287,67
199799555629,24
19971010666749,23
1997118943533,81
19971211471398,43
1998115294222,11
1998212299415,29
19983178104854,16
19984180123798,68
199855918333,63
In [19]:
--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
(21 rows affected)
Total execution time: 00:00:00.060
Out[19]:
ShipCountryOrderCountTotalSumma
Argentina348119,1
Austria125128003,84
Belgium5633824,85
Brazil203106925,78
Canada7550196,29
Denmark4632661,02
Finland5418810,05
France18481358,32
Germany328230284,63
Ireland5549979,91
Italy5315770,15
Mexico7223582,08
Norway165735,15
Poland163531,95
Portugal3011472,36
Spain5417983,2
Sweden9754495,14
Switzerland5231692,66
UK13558971,31
USA352245584,61
Venezuela11856810,63
In [47]:
--Число и стоимость заказов по работникам
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
Commands completed successfully.
Total execution time: 00:00:00.250
In [49]:
SELECT * 
FROM vwOrdersByEmployee
ORDER BY TotalSumma
(9 rows affected)
Total execution time: 00:00:00.052
Out[49]:
EmployeeIDFirstNameLastNameOrderCountTotalSumma
5StevenBuchanan11768792,28
6MichaelSuyama16873913,13
9AnneDodsworth10777308,07
7RobertKing176124568,24
8LauraCallahan260126862,28
2AndrewFuller241166537,75
1NancyDavolio345192107,6
3JanetLeverling321202812,84
4MargaretPeacock420232890,85
In [25]:
-- Наиболее продаваемые товары (по числу проданных штук) - название,  число продаж
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
(11 rows affected)
Total execution time: 00:00:00.064
Out[25]:
ProductNameProductIDProductsTotal
Camembert Pierrot601577
Raclette Courdavault591496
Gorgonzola Telino311397
Gnocchi di nonna Alice561263
Pavlova161158
Rhonbrau Klosterbier751155
Guarana Fantastica241125
Boston Crab Meat401103
Tarte au sucre621083
Chang21057
Flotemysost711057
In [2]:
--Число подчиненных
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
Commands completed successfully.
(2 rows affected)
Total execution time: 00:00:00.016
Out[2]:
EmployeeIDFirstNameLastNameTitleReported
2AndrewFullerVice President, Sales5
5StevenBuchananSales Manager3
In [46]:
SELECT EmployeeID, ReportsTo FROM Employees
(9 rows affected)
Total execution time: 00:00:00.103
Out[46]:
EmployeeIDReportsTo
12
2NULL
32
42
52
65
75
82
95