Tagi: group by

Zadanie 13.

Znajdź te zamówienia, w których zamówiono 5 lub więcej różnych produktów.

Schemat: OE, Tabela: Order_Items, Kolumny: order_id, product_id, Wynik: 73 rows

p13

 

 

 

 

Rozwiązanie

SELECT order_id, COUNT(product_id) as Products
FROM order_items
GROUP BY order_id
HAVING COUNT(product_id) >= 5
ORDER BY order_id;

[collapse]

Zadanie 12.

Podlicz, ile jest produktów w każdej kategorii oraz policz średnią cenę produktu w kategorii.

Schemat: OE, Tabela: Product-Information, Kolumny: category_id, product_id, list_price, Wynik: 17 rows

p12

 

 

 

 

 

Rozwiązanie

SELECT category_id as "Category", COUNT(product_id) as "Count", to_char(AVG(list_price), '9999D99') as "Avg price"
FROM product_information
GROUP BY category_id
ORDER BY category_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 9.

Stwórz raport sprzedażowy – policz wartość zamówień w każdym miesiącu każdego roku. Kolumnę z wartościami zamówień sformatuj według wzoru (screen).

Schemat: OE, Tabela: Orders, Kolumny: order_date, order_total, Wynik: 25 rows

p9

 

 

 

 

 

Rozwiązanie

SELECT to_char(order_date, 'YYYY') as "Year", to_char(order_date, 'MM') as "Month", to_char(SUM(order_total), '999,999.99') as "Total"
FROM orders
GROUP BY to_char(order_date, 'YYYY'), to_char(order_date, 'MM')
ORDER BY "Year", "Month";

[collapse]

Zadanie 7.

Policz, ile zamówień i na jaką łączną wartość złożył każdy z klientów w 2006 roku.

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_id, order_total, order_date, Wynik: 13 rows

p7

 

 

 

Rozwiązanie

SELECT customer_id, COUNT(order_id) as Zamowienia, SUM(order_total) as Wartosc
FROM Orders
WHERE EXTRACT(YEAR FROM order_date) = '2006'
GROUP BY customer_id;

[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 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 1.

a) Policz średnią długość stażu pracy dla poszczególnych stanowisk.

b) W którym departamencie staż pracy jest najniższy, a w którym najwyższy? (średnie)

c) Znajdź pracowników, którzy mają więcej niż jeden okres zatrudnienia.

Schemat: HR, Tabela: Job_History, Kolumny: job_id, employee_id, start_date, end_date, department_id, Wynik: a) 8 rows b) 6 rows c) 3 rows

Rozwiązanie

a) SELECT job_id, AVG(end_date - start_date) as AVGHireDays
FROM Job_History
GROUP BY job_id
ORDER BY AVGHireDays DESC;

b) SELECT department_id, AVG(end_date - start_date) as AVGHireDays
FROM Job_History
GROUP BY department_id
ORDER BY AVGHireDays DESC;

c) SELECT employee_id, COUNT(*)
FROM Job_History
GROUP BY employee_id
HAVING COUNT (*) > 1;

[collapse]

Zadanie 22.

Utwórz kwerendę, która policzy, ile jest niepowtarzających się imion. Zadanie rozwiąż na co najmniej dwa sposoby.

Baza: AdventureWorks2012, Tabela: Person.Person, Kolumna: FirstName, Wynik: 1018 rows

s22

 

 

 

 

 

 

 

Rozwiązanie

1. Z użyciem funkcji row_number i grupowaniem:

SELECT FirstName, ROW_NUMBER() OVER(ORDER BY FirstName) as nr
FROM Person.Person
GROUP BY FirstName
ORDER BY FirstName;

2. Z użyciem funkcji dense_rank i distinct:

SELECT DISTINCT FirstName, DENSE_RANK() OVER(ORDER BY FirstName) as nr
FROM Person.Person
ORDER BY FirstName;

3. Z użyciem tabeli pochodnej i row_number:

SELECT FirstName, ROW_NUMBER() OVER(ORDER BY FirstName) as nr
FROM ( SELECT DISTINCT FirstName FROM Person.Person ) as Tab
ORDER BY FirstName;

[collapse]
« Starsze wpsiy Recent Entries »