Tagi: join

Zadanie 15.

Utwórz zapytanie, które pokaże nr zamówienia, nazwę firmy zamawiającej oraz wartość zamówienia. Zadanie rozwiąż na dwa różne sposoby.

Baza: Northwind, Tabele: dbo.Orders, dbo.Customers, dbo.Order Subtotals (widok), Kolumny: OrderID, CustomerID, CompanyName, Subtotal, Wynik: 830 rows

s15

 

 

 

Rozwiązanie

1. Z użyciem złączenia tabel:

SELECT o.OrderID, c.CompanyName, os.Subtotal
FROM dbo.Orders as o JOIN dbo.Customers as c on o.CustomerID = c.CustomerID
JOIN dbo.[Order Subtotals] as os on o.OrderID = os.OrderID;

2. Z wykorzystaniem podzapytań skorelowanych:

SELECT o.OrderID,
( SELECT c.CompanyName FROM dbo.Customers as c WHERE o.CustomerID = c.CustomerID ) as 'CompanyName',
( SELECT os.Subtotal FROM dbo.[Order Subtotals] as os WHERE o.OrderID = os.OrderID ) as 'Subtotal'
FROM dbo.Orders as o;

[collapse]

Zadanie 14.

1. Utwórz wspólne wyrażenie tablicowe (CTE) nazwane ShortOrd, które zwróci ID zamówienia, ID klienta, ID pracownika, datę zamówienia oraz ID przewoźnika (z tabeli Orders).

2. Utwórz kwerendę zewnętrzną, opartą na powyższym wyrażeniu, która zwróci ID zamówienia, ID klienta, wartość zamówienia oraz nazwę przewoźnika, dotyczące zamówień złożonych w 1996 roku. (Uwaga: potrzebne będzie złączenie wyrażenia tablicowego z tabelą Shippers oraz widokiem Order Subtotals).

Baza: Northwind, Tabele: dbo.Orders, dbo.Shippers, dbo.Order Subtotals (widok), Kolumny: OrderID, CustomerID, EmployeeID, OrderDate, ShipVia, Subtotal, CompanyName, Wynik: 152 rows

s14

 

 

 

Rozwiązanie

WITH ShortOrd AS
(
SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia
FROM dbo.Orders
)

SELECT so.OrderID, so.CustomerID, os.Subtotal as Sum, s.CompanyName as Shipper
FROM ShortOrd as so JOIN dbo.Shippers as s on so.ShipVia = s.ShipperID
JOIN dbo.[Order Subtotals] as os on so.OrderID = os.OrderID
WHERE YEAR(so.OrderDate) = '1996'
ORDER BY so.CustomerID;

[collapse]

Zadanie 13.

1. Utwórz zapytanie, które z tabeli Order Details zwróci numer zamówienia oraz jego łączną wartość (suma).

2. Powyższe zapytanie przekształć do postaci tabeli pochodnej; w kwerendzie zewnętrznej wybierz numer zamówienia, sumę oraz ID klienta dla zamówień, których wartość wynosi conajmniej 10 000. (Uwaga: potrzebne będzie złączenie z tabelą dbo.Orders)

Baza: Northwind, Tabele: dbo.Order Details, dbo.Order, Kolumny: OrderID, UnitPrice, Quantity, Wynik: 14 rows

s13

 

 

 

 

Rozwiązanie

SELECT Tab.OrderID, Tab.suma, Ord.CustomerID
FROM (
SELECT OrderID, SUM(UnitPrice*Quantity) as suma
FROM dbo.[Order Details]
GROUP BY OrderID) as Tab JOIN dbo.Orders as Ord on Tab.OrderID = Ord.OrderID
WHERE suma >= 10000
ORDER BY suma DESC;

[collapse]

Zadanie 28.

Podsumuj pracę przewoźników: podlicz, ile zamówień obsłużyli w każdym roku. W wyniku zaznacz, na którym miejscu jest dany przewoźnik w ilości obsłużonych zamówień.

Baza: Northwind, Tabele: dbo.Orders, dbo.Shippers, Kolumny: CompanyName, OrderDate, ShipVia, ShipperID, Wynik: 9 rows

37

Rozwiązanie

SELECT ROW_NUMBER() OVER(PARTITION BY YEAR(O.OrderDate) ORDER BY COUNT(*) DESC) as Nr, S.CompanyName, YEAR(O.OrderDate) as Year, COUNT(*) as Orders
FROM dbo.Orders as O
JOIN dbo.Shippers as S on O.ShipVia = S.ShipperID
GROUP BY S.CompanyName, YEAR(O.OrderDate)
ORDER BY Year, Orders DESC;

[collapse]

Zadanie 21.

Dla klientów o ID ALFKI, ISLAT, SAVEA stwórz zestawienie, pokazujące ich uporządkowane i ponumerowane według wartości zamówienia.

Baza: Northwind, Tabele: dbo.Orders, dbo.Order Details, Kolumny: CustomerID, OrderID, UnitPrice, Quantity, Wynik: 151 rows

26

Rozwiązanie

SELECT O.CustomerID, O.OrderID, (OD.UnitPrice * OD.Quantity) as OrdVal, ROW_NUMBER() OVER(PARTITION BY O.CustomerID ORDER BY (OD.UnitPrice * OD.Quantity) DESC ) as num

FROM dbo.Orders as O JOIN dbo.[Order Details] as OD on O.OrderID = OD.OrderID WHERE O.CustomerID IN('ALFKI', 'ISLAT', 'SAVEA')

ORDER BY O.CustomerID, OrdVal DESC;

[collapse]

Zadanie 20.

Które produkty najlepiej się sprzedają? Wyznacz te, na które złożono ponad 40 zamówień.

Baza: Northwind, Tabele: dbo.Order Details, dbo.Products, Kolumny: OrderID, ProductID, ProductName, Wynik: 13 rows

25

Rozwiązanie


SELECT P.ProductID, P.ProductName, COUNT(OD.OrderID) as Zamowienia

FROM dbo.[Order Details] as OD

JOIN dbo.Products as P on OD.ProductID = P.ProductID

GROUP BY P.ProductID, P.ProductName

HAVING COUNT(OD.OrderID) >= 40

ORDER BY Zamowienia DESC;

[collapse]

Zadanie 19.

Wyznacz 5 klientów, którzy mają najwyższą wartość zamówień.

Baza: Northwind, Tabele: dbo.Customers, dbo.Orders, dbo.Order Details, Kolumny: CustomerID, CompanyName, Quantity, UnitPrice, Wynik: 5 rows

24

Rozwiązanie

SELECT C.CustomerID, C.CompanyName, SUM(OD.Quantity * OD.UnitPrice) as Suma FROM dbo.Customers as C

JOIN dbo.Orders as O on C.CustomerID = O.CustomerID

JOIN dbo.[Order Details] as OD on O.OrderID = OD.OrderID

GROUP BY C.CustomerID, C.CompanyName

ORDER BY Suma DESC

OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

[collapse]

Zadanie 18.

Który dostawca jakie produkty dostarcza?

Baza: Northwind, Tabele: dbo.Suppliers, dbo.Products, Kolumny: Country, CompanyName, ProductName, Wynik: 77 rows

23

Rozwiązanie


SELECT S.Country, S.CompanyName, P.ProductName

FROM dbo.Suppliers as S

JOIN dbo.Products as P on S.SupplierID = P.SupplierID

GROUP BY S.Country, S.CompanyName, P.ProductName

ORDER BY S.Country, S.CompanyName, P.ProductName;

[collapse]

Zadanie 17.

Policz wartości zamówień, zrealizowane przez każdego pracownika w 1997 roku.

Baza: Northwind, Tabele: dbo.Employees, dbo.Orders, dbo.Order Details, Kolumny: EmployeeID, LastName, Title, UnitPrice, Quantity, Wynik: 9 rows

22

Rozwiązanie

SELECT E.EmployeeID as ID, E.LastName, E.Title, SUM(OD.UnitPrice * OD.Quantity) as Sprzedaz

FROM dbo.Employees as E

JOIN dbo.Orders as O on E.EmployeeID = O.EmployeeID

JOIN dbo.[Order Details] as OD on O.OrderID = OD.OrderID

WHERE O.OrderDate >= '19970101' AND O.OrderDate < '19980101'

GROUP BY E.EmployeeID, E.LastName, E.Title

ORDER BY Sprzedaz DESC;

[collapse]

Zadanie 16.

Pokaż, ile zamówień obsłużył każdy z pracowników.

Baza: Northwind, Tabele: dbo.Employees, dbo.Orders, Kolumny: EmployeeID, FirstName, LastName, Title, OrderID, Wynik: 9 rows

21

Rozwiązanie

SELECT E.EmployeeID, E.FirstName, E.LastName, E.Title, COUNT(O.OrderID) as Orders FROM dbo.Employees as E JOIN dbo.Orders as O on E.EmployeeID = O.EmployeeID GROUP BY E.EmployeeID, E.FirstName, E.LastName, E.Title

ORDER BY Orders DESC;

[collapse]
« Starsze wpsiy Recent Entries »