Tagi: join

Zadanie 22.

Dla każdego klienta, który złożył zamówienie, określ średnią wartość zamówienia. Ponumeruj klientów wg najwyższej średniej wartości zamówienia.

Schemat: OE, Tabele: Customers, Orders, Kolumny: customer_id, order_total, Wynik: 47 rows

p22

 

 

 

 

 

Rozwiązanie

SELECT customer_id, AVG(o.order_total) as "Avg Order Value", ROW_NUMBER() OVER(ORDER BY AVG(o.order_total) DESC) as Num
FROM orders o INNER JOIN customers c
USING(customer_id)
GROUP BY customer_id
ORDER BY Num;

[collapse]

Zadanie 15.

Policz, ile który klient złożył zamówień w każdym roku. Wybierz tych, którzy złożyli więcej niż jedno zamówienie. Zaznacz, który klient jest na którym miejscu względem ilości zamówień (funkcja dense_rank() połączona z funkcją okna).

Schemat: OE, Tabele: Customers, Orders, Kolumny: order_id, order_date, customer_id, Wynik: 23 rows

p15

 

 

 

 

 

 

Rozwiązanie

SELECT EXTRACT(YEAR from o.order_date) as "Year", customer_id as "Customer", COUNT(o.order_id) as "Orders",
DENSE_RANK() OVER(PARTITION BY EXTRACT(YEAR from o.order_date) ORDER BY COUNT(o.order_id) DESC) as "YrNum"
FROM orders o INNER JOIN customers c
USING(customer_id)
GROUP BY EXTRACT(YEAR from o.order_date), customer_id
HAVING COUNT(o.order_id) > 1
ORDER BY "Year", "YrNum";

[collapse]

Zadanie 14.

Korzystając z tabeli Employees, Zrób zestawienie pracownik – podwładny w formie: „Jan Kowalski jest managerem Anna Nowak”.

Schemat: HR, Tabela: Employees, Kolumny: first_name, last_name, employee_id, manager_id, Wynik: 106 rows

p14

 

 

Rozwiązanie

SELECT mgr.first_name ||' '||mgr.last_name||' jest managerem '||' '||emp.first_name ||' '||emp.last_name as "Manager"
FROM employees emp INNER JOIN employees mgr
ON mgr.employee_id = emp.manager_id
ORDER BY mgr.employee_id;

[collapse]

Zadanie 10.

Policz, ile było zamówień na produkty z każdej kategorii w każdym roku.

Schemat: OE, Tabele: Orders, Order_Items, Product_Information, Kolumny: order_id, product_id, order_date, category_id, Wynik: 52 rows

p10

 

 

 

 

Rozwiązanie

SELECT to_char(o.order_date, 'YYYY') "Year", pi.category_id "Category", COUNT(product_id) "Amount"
FROM orders o INNER JOIN order_items oi
USING(order_id)
INNER JOIN product_information pi
USING(product_id)
GROUP BY to_char(o.order_date, 'YYYY'), pi.category_id
ORDER BY "Year", "Category";

[collapse]

Zadanie 8.

W których magazynach dostępne są produkty o id 2457, 3391, 1799 i 3003? W wyniku podaj nr produktu, nazwę magazynu oraz dostępną ilość towaru.

Schemat: OE, Tabele: Inventories, Warehouses, Kolumny: product_id, quantity_on_hand, warehouse_name, warehouse_id, Wynik: 32 rows

p8

 

 

 

 

Rozwiązanie

SELECT i.product_id, i.quantity_on_hand, w.warehouse_name
FROM inventories i INNER JOIN warehouses w
USING(warehouse_id)
WHERE i.product_id IN(2457, 3391, 1799, 3003)
ORDER BY i.product_id, i.quantity_on_hand DESC;

[collapse]

Zadanie 6.

Zrób zestawienie – który departament jakie ma stanowiska? Podaj nazwy departamentów i stanowisk oraz określ, ilu ludzi pracuje w każdym z nich.

Schemat: HR, Tabele: Departaments, Jobs, Employees, Kolumny: Department_id, department_name, job_id, job_title, Wynik: 19 rows

p6

 

 

 

Rozwiązanie

SELECT d.department_name as Departament, j.job_title as Stanowisko, COUNT(*) as Pracownicy
FROM Departments d INNER JOIN Employees e
USING(department_id) INNER JOIN Jobs j
USING(job_id)
GROUP BY d.department_name, j.job_title
ORDER BY Departament, Stanowisko;

[collapse]

Zadanie 2.

Sprawdź, którzy pracownicy „są bliżej niż dalej” pensji maksymalnej dla swojego stanowiska (tzn. przekroczyli próg połowy sumy będącej różnicą między płacą minimalną a maksymalną).

Schemat: HR, Tabele: Employees, Jobs, Kolumny: first_name, last_name, job_title, min_salary, max_salary, salary, Wynik: 25 rows

p2

 

 

Rozwiązanie

SELECT e.first_name, e.last_name, j.job_title, j.min_salary, e.salary, j.max_salary
FROM employees e INNER JOIN jobs j USING (job_id)
WHERE e.salary > j.min_salary + (j.max_salary - j.min_salary)/2;

[collapse]

Zadanie 26.

Wybierz wszystkich klientów, którzy zamówili produkty o id 1 i 55. Zadanie rozwiąż na dwa sposoby.

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

s26

Rozwiązanie

1. Z użyciem podzapytań i Exists:

SELECT DISTINCT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 1
AND EXISTS (
SELECT *
FROM dbo.[Order Details] OD2 JOIN dbo.Orders O2
ON OD2.OrderID = O2.OrderID
WHERE O.CustomerID = O2.CustomerID
AND OD2.ProductID = 55
)
ORDER BY O.CustomerID;

2. Z użyciem operatorów zbiorowych:

SELECT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 1

INTERSECT

SELECT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 55

ORDER BY O.CustomerID;

[collapse]

Zadanie 20.

1. Utwórz wspólne wyrażenie tablicowe (CTE), które zwróci nazwy miast i państw, w których są zarówno klienci jak i dostawcy.

2. W ostatecznym wyniku zapytania chcemy zobaczyć klientów i dostawców (CompanyName, SupplierID / CustomerID) z miast określonych w wyrażeniu tablicowym.

Baza: Northwind, Tabele: dbo.Suppliers, dbo.Customers, Kolumny: City, Country, CompanyName, SupplierID, CustomerID, Wynik: 14 rows

s20

 

 

 

 

 

Rozwiązanie

WITH CTE AS (

SELECT Country, City
FROM dbo.Suppliers

INTERSECT

SELECT Country, City
FROM dbo.Customers

)

SELECT s.SupplierID, s.CompanyName, s.City, c.CustomerID, c.CompanyName
FROM dbo.Suppliers as s JOIN dbo.Customers as c on s.City = c.City
WHERE s.City in (SELECT City FROM CTE);

[collapse]

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