Tagi: NVL

Zadanie 25.

Korzystając z widoku z zadania 23, stwórzmy podsumowanie sprzedażowe z punktu widzenia sprzedawców i klientów:

a) Który klient złożył najwięcej zamówień, a który miał największą wartość zamówień?

s25a

 

 

 

b) podsumowanie sprzedaży według klienta i sprzedawcy (rollup)

s25b

 

 

 

 

 

 

 

c) wyznacz sprzedawców, którzy odpowiadają (łącznie) za ok. 70% sprzedaży.

s25c

 

 

 

d) wyznacz średnią wartość sprzedaży dla każdego sprzedawcy w danym roku oraz ilość obsłużonych klientów

 

s25d

 

 

 

Schemat: OE, Tabela: my_order_view (widok z zadania 23) , Kolumny: customer_id, order_id, order_total, sales_rep_id, ord_year, Wynik: a) 2 rows b) 112 rows c) 4 rows d) 20 rows

Rozwiązanie

a) WITH cte1 AS
(SELECT customer_id,
COUNT(order_id) AS orders
FROM my_order_view
GROUP BY customer_id
ORDER BY COUNT(order_id) DESC
FETCH FIRST row only
),
cte2 AS
(SELECT customer_id,
SUM(order_total) AS total
FROM my_order_view
GROUP BY customer_id
ORDER BY COUNT(order_total) DESC
FETCH FIRST row only
)
SELECT 'Max orders:' AS description, customer_id, orders AS total FROM cte1
UNION
SELECT 'Max value:', customer_id, total FROM cte2;

 

b) SELECT NVL(TO_CHAR(customer_id), 'total') AS customer_id,
NVL(TO_CHAR(sales_rep_id), 'total')     AS sales_rep_id,
SUM(order_total)  AS order_total
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
GROUP BY cube(customer_id, sales_rep_id)
ORDER BY customer_id, sales_rep_id;

 

c) WITH cte AS
( SELECT DISTINCT sales_rep_id,
SUM(order_total) over(partition BY sales_rep_id) AS sales_rep_total,
SUM(order_total) over() AS total,
CAST(100* SUM(order_total) over(partition BY sales_rep_id) / SUM(order_total) over() AS NUMBER(5,2)) AS sales_rep_pct
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
),
cte2 AS
(SELECT sales_rep_id,
sales_rep_total,
sales_rep_pct,
SUM(sales_rep_pct) over(order by sales_rep_pct DESC) AS total_pct
FROM cte
)
SELECT * FROM cte2 WHERE total_pct <= 70 ORDER BY sales_rep_pct DESC;

 

d) SELECT ord_year, sales_rep_id, TO_CHAR(AVG(order_total), '99999D99') AS orders_total,
COUNT(customer_id) AS customers
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
GROUP BY sales_rep_id, ord_year
ORDER BY ord_year, sales_rep_id;

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

Rozwinięcie zadania 14: zmodyfikuj kwerendę tak, by uwzględniała i obsługiwała NULLe.

Schemat: HR, Tabela: Employees, Kolumny: first_name, last_name, employee_id, manager_id, Wynik: 196 rows

p28

 

 

 

Rozwiązanie

SELECT NVL(mgr.first_name, 'Nikt nie')
||' '
||NVL(mgr.last_name, '')
||' jest managerem '
||' '
||NVL(emp.first_name, 'niczyim')
||' '
||NVL(emp.last_name, '') AS "Manager"
FROM employees emp
FULL OUTER JOIN employees mgr
ON mgr.employee_id = emp.manager_id
ORDER BY mgr.employee_id;

[collapse]

 

 

Zadanie 9.

Tabela Employees (HR).

a) Policz wartość wynagrodzenia powiększoną o premię (commission_pct). Weź pod uwagę, że niektórzy nie mają premii (wtedy commission_pct ma wartość NULL).

b) Policz, który pracownik ile dostaje premii (jak wyżej – bierzemy pod uwagę istnienie NULL)

c) Do zestawienia z punktu b) dodaj obliczony staż pracy w latach (by wyniki były jednolite zakładamy, że wszyscy pracowali do 1 sierpnia 2008).

Schemat: HR, Tabela: Employees, Kolumny: First_Name, Last_Name, Salary, Commission_pct, Hire_Date, Wynik: 107 rows (a, b, c)

Rozwiązanie

a) SELECT first_name, last_name, salary * (1 + NVL(COMMISSION_PCT, 0)) as FullSalary
FROM Employees;

b) SELECT first_name, last_name, salary * NVL(COMMISSION_PCT, 0) as Premia
FROM Employees;

c) SELECT first_name, last_name, salary * NVL(COMMISSION_PCT, 0) as Premia,
TRUNC((to_date('2008-08-01', 'YYYY-MM-DD') - Hire_Date) / 365, 1) as Staz_Pracy_Lata
FROM Employees
ORDER BY Premia DESC, Staz_Pracy_Lata DESC;

[collapse]

Zadanie 3.

Z tabeli Locations wybierz:

a) Wszystkie adresy w Londynie

b) Wszystkie lokalizacje które są w państwach o id IT, US i CH

c) Lokalizacje, w których nazwa miasta nie zaczyna się na literę S ale ma tą literę w nazwie

d) Lokacje, które nie mają regionu. Pole region powinno zawierać informację ‚<brak regionu>’.

Schemat: HR, Tabela: Locations, Kolumny: City, Country-ID, State_Province  Wynik: a) 1 row b) 8 rows c) 2 rows d) 6 rows

Rozwiązanie

a) SELECT *
FROM LOCATIONS
WHERE City = 'London';

b) SELECT *
FROM LOCATIONS
WHERE COUNTRY_ID IN('IT', 'US', 'CH');

c) SELECT *
FROM LOCATIONS
WHERE City NOT LIKE 'S%' AND City LIKE '_%s%';

d) SELECT STREET_ADDRESS, POSTAL_CODE, CITY, NVL(STATE_PROVINCE, '<brak regionu>'), COUNTRY_ID
FROM LOCATIONS
WHERE STATE_PROVINCE IS NULL;

[collapse]