Tagi: MIN

Zadanie 15.

Z każdego departamentu wybierz pracownika, który zarabia najmniej.

Schemat: HR, Tabela: Employees, Kolumny: department_id, first_name, last_name, salary, Wynik: 12 rows

s15

 

 

 

Rozwiązanie

SELECT department_id, first_name, last_name, salary
FROM employees
WHERE (department_id, salary) in (
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id)

ORDER BY department_id;

[collapse]

Zadanie 14.

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

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_date, Wynik: 34 rows

s14

 

 

 

 

 

Rozwiązanie

1. Z podzapytaniem:

SELECT DISTINCT customer_id
FROM orders
WHERE customer_id not in
(
SELECT customer_id FROM orders WHERE extract(YEAR FROM order_date) = '2006'
)

ORDER BY customer_id;

2. Z grupowaniem:

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING extract(year from MIN(order_date)) > '2006'

ORDER BY customer_id;

[collapse]

Zadanie 24.

Dla zamówień o numerach 2355, 2413, 2377 i 2361 policz:

a) ile produktów zawiera każde z nich oraz podaj średnią cenę produktu

b) podaj kraj, z którego zostało złożone zamówienie; ile zamówień ma na koncie klient, który je złożył?

Schemat: OE, Tabele: Orders, Customers, Order_Items, Kolumny: order_id, product_id, unit_price, nls_territory, Wynik: 4 rows

Rozwiązanie

a) SELECT order_id, COUNT(product_id) as Products, to_char(AVG(unit_price), '999D99')as Avg_Price
FROM orders o INNER JOIN order_items oi USING(order_id)
WHERE order_id IN(2355, 2413, 2377, 2361)
GROUP BY order_id;

b) SELECT customer_id, MIN(c.nls_territory) as Country, COUNT(o.order_id) as Orders
FROM orders o INNER JOIN customers c USING(customer_id)
WHERE o.order_id IN(2355, 2413, 2377, 2361)
GROUP BY customer_id;

[collapse]

Zadanie 17.

Dla każdego klienta pokaż datę oraz numer pierwszego złożonego zamówienia, a także łączną ilość złożonych przez niego zamówień. Datę zamówienia sformatuj wg wzoru (screen).

Schemat: OE, tabela: Orders, Kolumny: customer_id, order_id, order_date, Wynik: 47 rows

p17

 

 

 

Rozwiązanie

SELECT customer_id||' ' as Customer, to_char(MIN(order_date), 'DD.MM.YYYY HH:MM:SS') as "First Order Date", MIN(order_id)as "First Order ID",
COUNT(order_id) "Total Orders"
FROM orders
GROUP BY customer_id
ORDER BY Customer;

Uwaga: Konkatencja przy customer_id w klauzuli SELECT ma na celu wyłącznie poprawienie czytelności wyniku; w innym wypadku kolumny customer_id i order_date zlewają się ze sobą i trudniej odczytać dane. Ciąg customer_id z „doklejoną” spacją (można dokleić też „nic” czyli ”) jest traktowany jako ciąg tekstowy i wyrównywany do lewej, w przeciwieństwie do typów liczbowych, które są wyrównywane do prawej.

[collapse]

Zadanie 3.

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

b) Policz, ile zamówień złożył każdy z klientów.

c) Pokaż klientów, którzy złożyli tylko jedno zamówienie – rozwiąż to na dwa sposoby.

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_id, order_date, Wynik: a) 47 rows b) 47 rows c) 29 rows

Rozwiązanie

a) SELECT customer_id, MIN(order_id), MIN(order_date)
FROM orders
GROUP BY customer_id;

b) SELECT customer_id, COUNT(order_id)
FROM orders
GROUP BY customer_id;

c) SELECT customer_id, COUNT(order_id)
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) = 1;

lub:

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING MIN(order_date) = MAX(order_date);

[collapse]

Zadanie 5.

Korzystając z tabeli Jobs, znajdź:

a) największą płacę minimalną

b) najmniejszą płacę maksymalną

c) dla każdego stanowiska określ różnicę między płacą minimalną a maksymalną

Schemat: HR, Kolumny: Min_Salary, Max_Salary, Job_Title, Wynik: a) 1 row b) 1 row c) 19 rows

Rozwiązanie

a) SELECT max(min_salary)
FROM Jobs;

b) SELECT min(max_salary)
FROM Jobs;

c) SELECT Job_Title, (Max_salary - Min_Salary) as "Max - Min"
FROM Jobs;

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