Kategoria: Poziom średniozaawansowany

Zadanie 16.

Wykorzystamy tabelę wynikową uzyskaną w zadaniu 15.

1. Powyższą tabelę przekształć do postaci wspólnego wyrażenia tablicowego (CTE), nazwanego Tab.

2. W kwerendzie zewnętrznej wybierz „20% najcenniejszych klientów”: nazwę firmy, łączną wartość zamówień złożonych przez daną firmę oraz łączną wartość wszystkich zamówień złożonych przez wszystkich klientów. Interesuje nas tylko 20% firm z najwyższymi łącznymi wartościami zamówień.

Baza: Northwind, Tabele: CTE Tab, dbo.order Subtotals, Kolumny: CompanyName, Subtotal, Wynik: 18 rows

s16

 

 

 

 

Rozwiązanie

WITH Tab AS (
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
)

SELECT TOP (20) PERCENT CompanyName, SUM(Subtotal) as 'CustSum', ( SELECT SUM(Subtotal) FROM dbo.[Order Subtotals] ) as TotalSum
FROM Tab
GROUP BY CompanyName
ORDER BY CustSum DESC;

[collapse]

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 12.

1. Stwórz zapytanie, które pokaże nam najczęściej zamawiane produkty.

2. Powyższe zapytanie przekształć do postaci tabeli pochodnej; w kwerendzie zewnętrznej wybierz 15 najczęściej zamawianych produktów. (Uwaga: więcej niż jeden produkt może być zamówiony daną ilość razy)

Baza: Northwind, Tabela: dbo.Order Details, Kolumny: ProductID, OrderID, Wynik: 17 rows

s12

 

 

 

 

 

Rozwiązanie

SELECT TOP(15) WITH TIES *
FROM (
SELECT ProductID, COUNT(OrderID) as Ordrs
FROM dbo.[Order Details]
GROUP BY ProductID
) as Tab
ORDER BY Ordrs DESC;

[collapse]

Zadanie 11.

Używając widoku Order Subtotals, napisz kwerendę, która zwróci zamówienia o wartości podobnej (plus minus 10%) do wskazanej (zadeklaruj w tym celu zmienną @sum). Wynik posortuj rosnąco.

Screen przedstawia część wyniku dla @sum = 400.

Baza: Northwind, Tabela (widok): dbo.Order Subtotals, Kolumny: OrderID, Subtotal, Wynik: 38 rows (dla @sum = 400)

s11

 

 

 

 

 

 

Rozwiązanie

DECLARE @sum as INT = 400;

SELECT OrderID, Subtotal
FROM dbo.[Order Subtotals]
WHERE Subtotal BETWEEN 0.9*(@sum) AND 1.1*(@sum)
ORDER BY Subtotal;

[collapse]

Zadanie 10.

Zadanie składa się z kilku kroków:

1. Utwórz wspólne wyrażenie tablicowe (CTE), które będzie zwracać id przewoźnika, nazwę firmy, miasto i kraj. Dodaj filtr, który zwróci tylko przewoźników z Londynu. Nazwijmy to wyrażenie SuppliersByCity.

Wybierz wszystkie rekordy z SuppliersByCity.

2. Zadeklaruj zmienną tekstową ‚miasto’, która będzie przechowywać nazwę miasta. Zmodyfikuj CTE, wstawiając zmienną. (DECLARE @miasto as nvarchar(20) = ‚London’;).

3. Sprawdź poprawność działania kwerendy, wykonując ją z różnymi parametrami zmiennej @miasto (np Tokyo, Osaka, Berlin, Ravenna, Paris, Boston, Sydney).

4. Zmodyfikuj kwerendę tak, by działała, gdy zmienna @miasto zawiera jedynie część nazwy.

Baza: Northwind, Tabela: dbo.Suppliers, Kolumny: SupplierID, CompanyName, City, Country

Rozwiązanie

1. WITH SuppliersByCity AS
(
SELECT SupplierID, CompanyName, City, Country
FROM dbo.Suppliers
WHERE city = 'London'
)
SELECT * FROM SuppliersByCity;

 

2. DECLARE @miasto as nvarchar(20) = 'London';
WITH SuppliersByCity AS
(
SELECT SupplierID, CompanyName, City, Country
FROM dbo.Suppliers
WHERE city = @miasto
)
SELECT * FROM SuppliersByCity;

 

4. DECLARE @miasto as nvarchar(20) = 'sa';
WITH SuppliersByCity AS
(
SELECT SupplierID, CompanyName, City, Country
FROM dbo.Suppliers
WHERE city LIKE '%'+@miasto+'%'
)
SELECT * FROM SuppliersByCity;

[collapse]

Zadanie 9.

Znajdź numery tych zamówień, których wartość jest co najmniej trzy razy większa niż średnia wartość zamówienia.

Użyj widoku Order Subtotals (odwołujesz się do niego tak samo, jak do tabeli).

Baza: Northwind, Tabela (widok): dbo.Order Subtotals, Kolumny: OrderID, Subtotal, Wynik: 47 rows

s9

Rozwiązanie

SELECT os1.OrderID, os1.Subtotal
FROM dbo.[Order Subtotals] as os1
WHERE os1.Subtotal >= 3* ( SELECT AVG(os2.Subtotal) FROM dbo.[Order Subtotals] as os2 )
ORDER BY os1.OrderID;

[collapse]

Zadanie 8.

Używając podkwerend, pokaż klientów, którzy zamówili produkt nr 28.

Baza: Northwind, Tabele: dbo.Orders, dbo.Order Details, Kolumny: OrderID, CustomerID, ProductID, Wynik: 26 rows

s8

Rozwiązanie

SELECT DISTINCT o.CustomerID
FROM dbo.Orders as o
WHERE o.OrderID in (
SELECT od.OrderID
FROM dbo.[Order Details] as od
WHERE od.ProductID = 28
)
ORDER BY o.CustomerID;

[collapse]

Zadanie 7.

Używając podkwerend, znajdź firmy, które dostarczają produkty z zamówienia nr 10337.

Baza: Northwind, Tabele: dbo. Suppliers, dbo.Products, dbo.Order Details, Kolumny: SupplierID, CompanyName, ProductID, OrderID, Wynik: 4 rows

s7

Rozwiązanie

SELECT s.SupplierID, s.Companyname
FROM dbo.Suppliers as s
WHERE s.SupplierID in (
SELECT p.SupplierID
FROM dbo.Products as P
WHERE p.ProductID in (
SELECT od.ProductID
FROM dbo.[Order Details] as od
WHERE od.OrderID = 10337
)
)

[collapse]
« Starsze wpsiy Recent Entries »