Tagi: podkwerendy

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 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 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]

Zadanie 5.

Pokaż pierwsze i ostatnie zamówienie dla klientów z Niemiec.

Baza: Northwind, Tabele: dbo.Orders, dbo.Customers, Kolumny: CustomerID, OrderID, Country, Wynik: 11 rows

s5

Rozwiązanie

SELECT CustomerID, MIN(OrderID) as FirstOrder, MAX(OrderID) as LastOrder
FROM dbo.Orders
GROUP BY CustomerID
HAVING CustomerID in (SELECT CustomerID FROM dbo.Customers WHERE Country = 'Germany')
ORDER BY CustomerID;

[collapse]

Zadanie 4.

Wybierz tylko parzyste numery zamówień z zakresu 10759 – 10924, które złożyli klienci z USA. Nie używaj złączenia tabel.

Baza: Northwind, Tabele: dbo.Orders, dbo.Customers, Kolumny: OrderID, CustomerID, Country, Wynik: 9 rows

s4

Rozwiązanie

SELECT OrderID, CustomerID
FROM dbo.Orders
WHERE (OrderID %2 = 0) AND (OrderID BETWEEN 10759 AND 10924)
AND CustomerID in (SELECT CustomerID FROM dbo.Customers WHERE Country = 'USA');

[collapse]

Zadanie 3.

Pokaż klientów, którzy w 1996 roku nie złożyli żadnego zamówienia, ale złożyli je później. Zadanie rozwiąż na dwa różne sposoby.

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

s3

Rozwiązanie

1) z grupowaniem:

SELECT CustomerID
FROM dbo.Orders
GROUP BY CustomerID
HAVING MIN(OrderDate) >= '19970101';

2) z użyciem podzapytań:

SELECT DISTINCT O.CustomerID
FROM dbo.Orders as O
WHERE O.CustomerID not in (SELECT o1.CustomerID FROM dbo.Orders as o1 WHERE o1.OrderDate >= '19960101' AND o1.OrderDate < '19970101')
AND O.CustomerID in (SELECT o2.CustomerID FROM dbo.Orders as o2 WHERE o2.OrderDate >= '19970101')
ORDER BY O.CustomerID;

[collapse]
« Starsze wpsiy Recent Entries »