Autor Kategorii: Agnieszka Włodarczyk

Zadanie 17.

1. Utwórz wspólne wyrażenie tablicowe (CTE), nazwane SalesSummary, które wyświetli nam szereg informacji o zamówieniach (OrderID, SaleAmount, CompanyName, ProductID, ProductName, UnitPrice, Quantity, Discount, ExtendedPrice, ShippedDate). Użyj w tym celu widoków Sales Totals by Amount oraz Order Details Extended.

2. W kwerendzie zewnętrznej wybierz wszystkie kolumny tabeli SalesSummary, oraz dołóż kolumnę zawierającą ID klienta (CustomerID, z tabeli Customers).

Baza: Northwind, Tabele: dbo.Sales Totals by Amount (widok), dbo.Order Details Extended (widok), dbo.Customers, Kolumny: OrderID, SaleAmount, CompanyName, ProductID, ProductName, UnitPrice, Quantity, Discount, ExtendedPrice, ShippedDate, CustomerID, Wynik: 233 rows

s17

 

 

Rozwiązanie

1. WITH SalesSummary AS
(
SELECT s.OrderID, s.SaleAmount, s.CompanyName, o.ProductID, o.ProductName, o.UnitPrice, o.Quantity, o.Discount, o.ExtendedPrice,  s.ShippedDate
FROM dbo.[Sales Totals by Amount] as s
JOIN dbo.[Order Details Extended] as o on s.OrderID = o.OrderID
)

2. SELECT *, (SELECT c.CustomerID FROM dbo.Customers as c WHERE SalesSummary.CompanyName = c.CompanyName) as CustomerID
FROM SalesSummary
ORDER BY OrderID;

[collapse]

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

Jakie wyniki otrzymamy po uruchomieniu poniższego kodu?

a) SELECT 2 * 2,5

b) SELECT OrderID OrderDate FROM dbo.Orders;

Baza: Northwind

Rozwiązanie


a) 30a

Do określania miejsc dziesiętnych służy kropka, nie przecinek. Powyższy kod inaczej można zapisać jako SELECT (2 * 2), 5. Jeśli chcemy przemnożyć wartości, trzeba zamiast przecinka wstawić kropkę.

b) 30b

Zamiast spodziewanych dwóch kolumn, dostajemy jedną, w dodatku nazwa się nie zgadza. Dzieje się tak dlatego, że nie ma przecinka pomiędzy OrderID a OrderDate, co oznacza nadanie aliasu. Zapis taki jest jest równoważny z "SELECT OrderID as OrderDate".

[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]
« Starsze wpsiy Recent Entries »