Autor Kategorii: Agnieszka Włodarczyk

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 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]

Zadanie 29.

Stwórz zapytanie, które pokaże nam najczęściej zamawiane produkty. Wybierz 15 najczęściej zamawianych produktów. (Uwaga: więcej niż jeden produkt może być zamówiony daną ilość razy)

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

p29

 

 

 

 

 

Rozwiązanie

SELECT product_id, COUNT(order_id) AS Orders
FROM order_items
GROUP BY product_id
ORDER BY COUNT(order_id) DESC
FETCH FIRST 15 rows WITH ties;

[collapse]

Zadanie 9.

Znajdź produkty, których cena jest wyższa niż średnia cena z ich kategorii.

Schemat: OE, Tabela: Product_Information, Kolumny: product_id, product_name, category_id, list_price, Wynik: 91 rows

s9

 

 

 

Rozwiązanie

SELECT pi1.product_id, pi1.product_name, pi1.category_id, pi1.list_price
FROM product_information pi1
WHERE pi1.list_price > (
SELECT AVG(pi2.list_price)
FROM product_information pi2
WHERE pi1.category_id = pi2.category_id
GROUP BY pi2.category_id)
ORDER BY pi1.category_id, pi1.list_price;

[collapse]

Zadanie 8.

Napisz kwerendę, która zwróci listę nazwisk, które noszą zarówno klienci, jak i pracownicy.

Schemat: OE, HR, Tabele: Employees, Customers, Kolumny: cust_last_name, last_name, Wynik: 6 rows

s8

 

 

 

 

Rozwiązanie

1. Z operatorem INTERSECT:

SELECT DISTINCT last_name FROM HR.employees
INTERSECT
SELECT cust_last_name FROM customers;

2. Z użyciem EXISTS:

SELECT DISTINCT last_name FROM HR.employees
WHERE EXISTS (
SELECT cust_last_name
FROM customers
WHERE HR.employees.last_name = customers.cust_last_name );

[collapse]
« Starsze wpsiy Recent Entries »