Tagi: having

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

Korzystając z tabeli Order_Items znajdź te zamówienia, które zawierają produkty o id 3127 i 3106 (oba jednocześnie). Zadanie rozwiąż na co najmniej dwa sposoby.

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

s1

 

 

 

 

 

 

 

 

 

Rozwiązanie

1. Z użyciem INTERSECT:

SELECT order_id
FROM order_items
WHERE product_id = 3127

INTERSECT

SELECT order_id
FROM order_items
WHERE product_id = 3106

ORDER BY order_id;

2. Z użyciem EXISTS:

SELECT order_id
FROM order_items oi
WHERE product_id = 3127
AND EXISTS (
SELECT order_id
FROM order_items oi2
WHERE product_id = 3106 AND oi.order_id = oi2.order_id
)
ORDER BY order_id;

3. Z użyciem IN:

SELECT order_id
FROM order_items
WHERE product_id = 3127
AND order_id IN
( SELECT order_id FROM order_items WHERE product_id = 3106);

4. Z użyciem klauzuli WITH:

WITH Subquery AS (
SELECT order_id
FROM order_items
WHERE product_id = 3127 OR product_id = 3106
)

SELECT *
FROM Subquery
GROUP BY order_id
HAVING COUNT(*) >1
ORDER BY order_id;

5. Z podzapytaniem w klauzuli FROM:

SELECT order_id
FROM (
SELECT order_id
FROM order_items
WHERE product_id = 3127 OR product_id = 3106
) Tab
GROUP BY order_id
HAVING COUNT(*) >1
ORDER BY order_id;

[collapse]

Zadanie 20.

Pokaż klientów o parzystym ID, którzy złożyli więcej niż 3 zamówienia lub nie złożyli żadnego.

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

p20

 

 

 

 

Rozwiązanie

SELECT customer_id as Customer, COUNT(order_id) as Orders
FROM customers c LEFT OUTER JOIN orders o
USING(customer_id)
WHERE MOD(customer_id, 2) = 0
GROUP BY customer_id
HAVING COUNT(order_id) >= 3 OR COUNT(order_id) = 0
ORDER BY customer_id;

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

Zadanie 20.

Które produkty najlepiej się sprzedają? Wyznacz te, na które złożono ponad 40 zamówień.

Baza: Northwind, Tabele: dbo.Order Details, dbo.Products, Kolumny: OrderID, ProductID, ProductName, Wynik: 13 rows

25

Rozwiązanie


SELECT P.ProductID, P.ProductName, COUNT(OD.OrderID) as Zamowienia

FROM dbo.[Order Details] as OD

JOIN dbo.Products as P on OD.ProductID = P.ProductID

GROUP BY P.ProductID, P.ProductName

HAVING COUNT(OD.OrderID) >= 40

ORDER BY Zamowienia DESC;

[collapse]
« Starsze wpsiy