Zadanie 19.

Korzystając z tabeli Orders, utwórz zapytanie, które zwróci ID klienta, numer zamówienia, wartość zamówienia, procentowy udział zamówienia we wszystkich zamówieniach klienta oraz łączną wartość zamówień klienta.

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_id, order_total, Wynik: 105

s19

 

 

 

Rozwiązanie

SELECT customer_id, order_id, order_total,
CAST(100* order_total / SUM(order_total) over(partition BY customer_id) AS NUMBER(5,2)) AS order_pct,
SUM(order_total) over(partition BY customer_id) AS all_orders
FROM orders
ORDER BY customer_id, order_id;

[collapse]

Zadanie 18.

Znajdź produkty, które nie były zamówione ani razu. Zadanie rozwiąż więcej niż jednym sposobem.

Schemat: OE, Tabele: Order_Items, Product_Information, Kolumny: product_id, order_id, Wynik: 103

s18

 

 

 

 

 

 

Rozwiązanie

1. Z użyciem MINUS:

SELECT product_id FROM product_information
MINUS
SELECT product_id FROM order_items ORDER BY product_id;

2. Z wykorzystaniem złączenia OUTER JOIN:
SELECT product_id
FROM product_information
FULL OUTER JOIN order_items oi USING(product_id)
WHERE oi.order_id IS NULL
ORDER BY product_id;

3. Z podzapytaniem:
SELECT product_id
FROM product_information
WHERE product_id NOT IN
( SELECT DISTINCT product_id FROM order_items
)
ORDER BY product_id;

[collapse]

Zadanie 17.

Wybierz product_id i pokaż, ile razy był zamówiony (w ilu zamówieniach się znalazł – ilościowo oraz procentowo).

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

s17

 

 

 

Rozwiązanie


SELECT product_id, COUNT(order_id) AS ilosc_zamowien, (SELECT COUNT(*) FROM orders) AS total,
CAST(100* COUNT(order_id) / (SELECT COUNT(*) FROM orders) AS NUMBER(5,2)) AS w_ilu_prct_zamowien
FROM order_items
GROUP BY product_id
ORDER BY ilosc_zamowien DESC;

[collapse]

Zadanie 16.

Podlicz wartość zamówień każdego klienta w podziale na te złożone bezpośrednio (direct) lub online.

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_mode, order_total, Wynik: 62 rows

s16

 

 

 

 

 

 

Rozwiązanie

1. Z podzapytaniami:

WITH subquery AS (
SELECT customer_id,
CASE order_mode
WHEN 'direct' THEN SUM(order_total)
END AS direct,
CASE order_mode
WHEN 'online' THEN SUM(order_total)
END AS onlines
FROM orders
GROUP BY customer_id, order_mode
)

SELECT customer_id, NVL(direct, 0) as direct, NVL(onlines, 0) as "ONLINE"
FROM subquery
ORDER BY customer_id;

2. Bez podzapytań:

SELECT customer_id,
SUM(
CASE order_mode
WHEN 'direct'
THEN order_total
ELSE 0
END)AS direct,
SUM(
CASE order_mode
WHEN 'online'
THEN order_total
ELSE 0
END)AS "ONLINE"
FROM ORDERS
GROUP BY customer_id, order_mode
ORDER BY customer_id;

[collapse]

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

Używając podkwerend, znajdź magazyny, które dostarczają produkty z zamówienia nr 2393.

Schemat: OE, Tabele: Inventories, Order_Items, Kolumny: product_id, warehouse_id, quantity_on_hand, order_id, Wynik: 39 rows

s13

 

 

 

 

Rozwiązanie

SELECT product_id AS product, warehouse_id AS warehouse, quantity_on_hand AS quantity
FROM inventories
WHERE product_id IN
( SELECT product_id FROM order_items WHERE order_id = 2393
)
ORDER BY product, warehouse;

Wersja która dodatkowo podpowiada czy w magazynie jest wystarczająca ilość towaru:

SELECT product_id, warehouse_id, quantity_on_hand,
(SELECT quantity FROM order_items oi WHERE order_id = 2393
AND inv.product_id = oi.product_id) AS ordered_quantity,
CASE
WHEN (SELECT quantity FROM order_items oi
WHERE order_id = 2393
AND inv.product_id = oi.product_id) <= quantity_on_hand
THEN 'yes'
ELSE 'no'
END AS "ENOUGH?"
FROM inventories inv
WHERE product_id IN
(SELECT product_id FROM order_items WHERE order_id=2393)
ORDER BY product_id, warehouse_id;

[collapse]

Zadanie 12.

Używając podkwerend, pokaż klientów, którzy zamówili produkt nr 3106.

Schemat: OE, Tabele: Orders, Order_Items, Kolumny: customer_id, order_id, product_id, Wynik: 21 rows

s12

 

 

 

 

Rozwiązanie

SELECT customer_id, order_id
FROM orders
WHERE order_id in (
SELECT order_id
FROM order_items
WHERE product_id = 3106
)
ORDER BY customer_id;

[collapse]

Zadanie 11.

Znajdź numery tych zamówień, których wartość jest co najmniej trzy razy większa niż średnia wartość zamówienia.

Schemat: OE, Tabela: Orders, Kolumny: order_id, order_total, Wynik: 5 rows

s11

 

 

 

 

Rozwiązanie

SELECT order_id, order_total
FROM orders
WHERE order_total > 3* (SELECT AVG(order_total) FROM orders)
ORDER BY order_total;

[collapse]

Zadanie 10.

Korzystając z widoku Order_Summary (utworzonego w Zadaniu 6.), napisz kwerendę, która zwróci zamówienia o wartości podobnej (plus minus 10%) do wskazanej (użyj w tym celu zmiennej &suma). Wynik posortuj rosnąco.

Screen przedstawia wynik dla &suma = 50 000.

Schemat: OE, Tabela: Order_Summary (widok), Kolumny: order_id, order_mode, order_date, order_total, Wynik: 7 rows

s10

 

 

 

 

Rozwiązanie

SELECT DISTINCT order_id, order_mode, order_date, order_total
FROM order_summary
WHERE order_total BETWEEN &suma *0.9 AND &suma *1.1
ORDER BY order_total;

[collapse]