Tagi: extract

Zadanie 23.

Stwórzmy widok, zawierający dane dot. sprzedaży, który posłuży nam w kolejnych zadaniach jako źródło danych do analizy. Widok powinien zawierać takie informacje, jak: nr zamówienia, nr klienta, rok złożenia zamówienia, miesiąc złożenia zamówienia, kwota zamówienia, id produktu, ilość produktu, id sprzedającego.

Schemat: OE, Tabele: Orders, Order_Items, Kolumny: order_id, order_date, customer_id, order_total, sales_rep_id, product_id, quantity, Wynik: 665 rows

s23

 

 

Rozwiązanie

CREATE OR REPLACE VIEW My_Order_View
AS
SELECT order_id, EXTRACT(YEAR from o.order_date) as ord_year, EXTRACT(MONTH from o.order_date) as ord_month,
oi.product_id, oi.quantity, o.customer_id, o.sales_rep_id, o.order_total
FROM orders o
INNER JOIN order_items oi USING(order_id);

SELECT * FROM My_Order_View
ORDER BY ord_year, ord_month;

 

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

Czy więcej zamówień było złożonych przed godziną 14, czy później?

Schemat: OE, Tabela: Orders, Kolumny: order_id, order_date, Wynik: 2 rows

s2

 

 

 

Rozwiązanie

WITH

Przed14 AS (
SELECT COUNT(order_id) as Zamowienia, EXTRACT(hour from order_date) as Godzina
FROM orders
WHERE EXTRACT(hour from order_date) < 14
GROUP BY EXTRACT(hour from order_date)
),

Po14 AS (
SELECT COUNT(order_id) as Zamowienia, EXTRACT(hour from order_date) as Godzina
FROM orders
WHERE EXTRACT(hour from order_date) >= 14
GROUP BY EXTRACT(hour from order_date)
)

SELECT 'Zamowienia przed godz 14: '||SUM(Zamowienia) as wynik FROM Przed14
UNION
SELECT 'Zamowienia po godz 14: '||SUM(Zamowienia) FROM Po14;

[collapse]

 

Zadanie 23.

Klienci z jakich krajów złożyli zamówienia w 2006 roku? Nazwy krajów uporządkuj alfabetycznie i zadbaj, by się nie powtarzały.

Schemat: OE, Tabele: Customers, Orders, Kolumny: customer_id, order_date, nls_territory, Wynik: 1 row

Rozwiązanie

SELECT DISTINCT c.nls_territory
FROM orders o INNER JOIN customers c
USING(customer_id)
WHERE EXTRACT(YEAR from o.order_date) = '2006';

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

Z tabeli Customers (OE) wybierz:

a) klientów urodzonych w czerwcu, pochodzących z Ameryki

b) wszystkie zamężne włoszki (Italy)

c) klientów których nazwisko nie zaczyna się na L, ale zawiera ‚ea’

Schemat: OE, Tabela: Customers, Kolumny: Date_Of_Birth, nls_territory, gender, marital_status, cust_last_name, Wynik: a) 17 rows b) 8 rows c) 3 rows

Rozwiązanie

a) SELECT *
FROM customers
WHERE Extract(month from date_of_birth) = '06' AND nls_territory = 'AMERICA';

b) SELECT *
FROM customers
WHERE nls_territory = 'ITALY' AND gender = 'F' AND marital_status = 'married';

c) SELECT *
FROM customers
WHERE cust_last_name NOT LIKE 'L%' AND cust_last_name LIKE '%ea%';

[collapse]