Kategoria: T-SQL

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]

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]

Zadanie 29.

Dla każdego klienta pokaż datę oraz numer pierwszego złożonego zamówienia.

Baza: Northwind, Tabela: dbo.Orders, Kolumny: CustomerID, OrderID, OrderDate, Wynik: 89 rows

p29

Rozwiązanie

SELECT CustomerID, MIN(OrderID) as FirstOrder, MIN(OrderDate) as FirstOrderDate
FROM dbo.Orders
GROUP BY CustomerID;

[collapse]

Zadanie 6.

Pokaż zamówienia, które złożyli klienci z USA i które były obsługiwane przez pracownika na stanowisku Sales Representative.

Baza: Northwind, Tabele: dbo.Orders, dbo.Customers, dbo.Employees, Kolumny: OrderID, CustomerID, EmployeeID, Country, Title, Wynik: 88 rows

s6

Rozwiązanie

SELECT OrderID, CustomerID, EmployeeID
FROM dbo.Orders
WHERE CustomerID in (SELECT CustomerID FROM dbo.Customers WHERE Country = 'USA')
AND EmployeeID in (SELECT EmployeeID FROM dbo.Employees WHERE Title = 'Sales Representative')
ORDER BY CustomerID, OrderID;

[collapse]
« Starsze wpsiy Recent Entries »