Kategoria: Oracle SQL

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

Dla każdego klienta, który złożył zamówienie, określ średnią wartość zamówienia. Ponumeruj klientów wg najwyższej średniej wartości zamówienia.

Schemat: OE, Tabele: Customers, Orders, Kolumny: customer_id, order_total, Wynik: 47 rows

p22

 

 

 

 

 

Rozwiązanie

SELECT customer_id, AVG(o.order_total) as "Avg Order Value", ROW_NUMBER() OVER(ORDER BY AVG(o.order_total) DESC) as Num
FROM orders o INNER JOIN customers c
USING(customer_id)
GROUP BY customer_id
ORDER BY Num;

[collapse]

Zadanie 21.

Dla klientów o ID 101, 111, 121, 141, 171 i 201 pokaż numer oraz datę ostatniego złożonego zamówienia. Jeśli klient nie ma na swoim koncie zamówień, powinna pojawić się stosowna informacja.

Schemat: OE, Tabele: Orders, Customers, Kolumny: customer-id, order_id, order_date, Wynik: 6 rows

p21

 

 

Rozwiązanie

SELECT customer_id as Customer, MAX(o.order_id) as "Last order",
to_char(MAX(o.order_date), 'DD.MM.YYYY') as "Last ord. date",
CASE
WHEN MAX(o.order_id) IS NULL THEN 'Ten klient nie ma zamówień'
ELSE ' '
END as Opis
FROM customers c LEFT OUTER JOIN orders o
USING(customer_id)
WHERE customer_id IN(101, 111, 121, 141, 171, 201)
GROUP BY customer_id
ORDER BY customer_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 19.

Określ, ile produktów (product_id) zawiera które zamówienie; wynik opisz przedziałami: „Jeden lub dwa”, „Od trzech do pięciu”, „Od sześciu do dziesięciu”, „Powyżej dziesięciu”.

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

p19

 

 

 

 

 

Rozwiązanie

SELECT order_id,
CASE
WHEN COUNT(product_id) <=2 THEN 'Jeden lub dwa'
WHEN COUNT(product_id) BETWEEN 3 AND 5 THEN 'Od trzech do pięciu'
WHEN COUNT(product_id) BETWEEN 6 AND 10 THEN 'Od sześciu do dziesięciu'
WHEN COUNT(product_id) >10 THEN 'Powyżej dziesięciu'
END as Produkty
FROM order_items
GROUP BY order_id
ORDER BY order_id;

[collapse]

 

Zadanie 18.

Znajdź klientów, którzy nie złożyli żadnego zamówienia.

Schemat: OE, Tabele: Customers, Orders, Kolumny: customer_id, cust_first_name, cust_last_name, order_id, Wynik: 272 rows

p18

 

 

 

 

 

Rozwiązanie

SELECT customer_id, c.cust_first_name, c.cust_last_name
FROM customers c LEFT OUTER JOIN orders o USING(customer_id)
WHERE o.order_id IS NULL
ORDER BY customer_id;

[collapse]

Zadanie 17.

Dla każdego klienta pokaż datę oraz numer pierwszego złożonego zamówienia, a także łączną ilość złożonych przez niego zamówień. Datę zamówienia sformatuj wg wzoru (screen).

Schemat: OE, tabela: Orders, Kolumny: customer_id, order_id, order_date, Wynik: 47 rows

p17

 

 

 

Rozwiązanie

SELECT customer_id||' ' as Customer, to_char(MIN(order_date), 'DD.MM.YYYY HH:MM:SS') as "First Order Date", MIN(order_id)as "First Order ID",
COUNT(order_id) "Total Orders"
FROM orders
GROUP BY customer_id
ORDER BY Customer;

Uwaga: Konkatencja przy customer_id w klauzuli SELECT ma na celu wyłącznie poprawienie czytelności wyniku; w innym wypadku kolumny customer_id i order_date zlewają się ze sobą i trudniej odczytać dane. Ciąg customer_id z „doklejoną” spacją (można dokleić też „nic” czyli ”) jest traktowany jako ciąg tekstowy i wyrównywany do lewej, w przeciwieństwie do typów liczbowych, które są wyrównywane do prawej.

[collapse]

Zadanie 16.

Korzystając z tabeli Employees w schemacie HR:

a) wyświetl listę numerów departamentów w taki sposób, by wszystkie były trzycyfrowe (uzupełnij je zerami z lewej) oraz nie powtarzały się.

b) wyświetl id pracownika oraz jego datę urodzenia i pensję wg określonego formatu:

p16

 

 

 

 

c) wygeneruj każdemu pracownikowi nowy adres mailowy składający się z nazwiska, kropki, pierwszej litery imienia, numeru departamentu, z domeną @abc.com (przykład: kowalska.a90@abc.com):

p16c

 

 

 

 

Schemat: HR, Tabela: Employees, Kolumny: department_id, employee_id, first_name, last_name, hire_date, salary, Wynik: a) 12 rows, b) oraz c) 107 rows

Rozwiązanie

a) SELECT DISTINCT LPAD(department_id, 3, '0') as Department
FROM employees
ORDER BY Department;

b) SELECT employee_id, to_char(salary, '$99G999D99') as Salary, to_char(hire_date, 'DD MON YYYY') as "Hire Date"
FROM employees
ORDER BY employee_id;

c) SELECT employee_id, lower(last_name ||'.'||SUBSTR(first_name, 1, 1)||department_id||'@abc.com') as "New Email"
FROM employees
ORDER BY employee_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 14.

Korzystając z tabeli Employees, Zrób zestawienie pracownik – podwładny w formie: „Jan Kowalski jest managerem Anna Nowak”.

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

p14

 

 

Rozwiązanie

SELECT mgr.first_name ||' '||mgr.last_name||' jest managerem '||' '||emp.first_name ||' '||emp.last_name as "Manager"
FROM employees emp INNER JOIN employees mgr
ON mgr.employee_id = emp.manager_id
ORDER BY mgr.employee_id;

[collapse]
« Starsze wpsiy Recent Entries »