Zadanie 22.

Oblicz łączną sumę tych zamówień, których wartość wynosi powyżej 35000. Zadanie rozwiąż na kilka sposobów.

Schemat: OE, Tabela: Orders, Kolumny: order_total, Wynik: 1

s22

 

Rozwiązanie

1. Wersja najprostsza:

SELECT SUM(order_total) AS wynik
FROM orders
WHERE order_total > 35000;

2. Wersja z CASE:

SELECT SUM(
CASE
WHEN order_total < 35000
THEN 0
ELSE order_total
END) AS wynik
FROM orders;

3. Z subquery factoring:

WITH subquery AS (
SELECT order_total
FROM orders
WHERE order_total > 35000
)
SELECT SUM(order_total) AS wynik FROM subquery;

4. Z podzapytaniem w klauzuli FROM:

SELECT SUM(order_total) AS wynik
FROM
( SELECT order_total
FROM orders
WHERE order_total > 35000
) Tab;

[collapse]

Zadanie 21.

Stwórz podsumowanie sprzedażowe – który ze sprzedawców jakich klientów obsłużył, na jaką kwotę ci klienci złożyli zamówienia, oraz jaką łączną wartość zamówień obsłużył sprzedawca.

Schemat: OE, Tabela: Orders, Kolumny: sales_rep_id, customer_id, order_total, Wynik: 76

s21

 

 

 

Rozwiązanie

SELECT NVL(TO_CHAR(sales_rep_id), 'total') AS "sales rep. id", NVL(TO_CHAR(customer_id), 'Sales Rep. total') AS "customer id", SUM(order_total) AS "Sum"
FROM orders
WHERE sales_rep_id IS NOT NULL
GROUP BY rollup(sales_rep_id, customer_id)
ORDER BY sales_rep_id;

[collapse]

Zadanie 20.

Znajdź zamówienia złożone przez klientów, których credit_limit jest większy niż 1000. Zadanie rozwiąż na dwa sposoby.

Schemat: OE, Tabele: Orders, Customers, Kolumny: order_id, customer_id, Wynik: 7

s20

 

 

 

 

 

Rozwiązanie

1. Łączenie tabel:

SELECT o.order_id, customer_id
FROM orders o
INNER JOIN customers c USING (customer_id)
WHERE c.credit_limit > 1000;

2. Podzapytania:

SELECT order_id, customer_id
FROM orders
WHERE customer_id IN
( SELECT customer_id FROM customers WHERE credit_limit > 1000
);

[collapse]

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]