Tagi: case

Zadanie 5.

Napisz procedurę z parametrem, która będzie sprawdzała, czy podana w parametrze liczba jest podzielna przez 2 i/lub 3, oraz zwróci odpowiedni komunikat.

proc-z-parametrem-zad-5

proc-z-parametrem-zad-5a

 

 

 

 

 

Rozwiązanie


create or replace procedure podzielnosc (x in number)
is
begin

case
when x mod 3 = 0 and x mod 2 = 0 then dbms_output.put_line(‚podzielna przez 2 i 3’);
when x mod 3 = 0 then dbms_output.put_line(‚podzielna przez 3’);
when x mod 2 = 0 then dbms_output.put_line(‚podzielna przez 2’);
else dbms_output.put_line(‚niepodzielna przez 2 i 3’);
end case;

end;

[collapse]

Zadanie 3.

Korzystamy z tabel stworzonych w Zadaniu 1 (i zmodyfikowanych w Zadaniu 2):

1. Święta idą – wszystkie płace podstawowe rosną o 10%. Uaktualnij dane.

2. Dokupiono nowy samochód (id 6, biała Toyota Yaris, DBC 1587). Wprowadź go do bazy i przypisz temu, kto zarabia najwięcej z osób nieposiadających jeszcze auta firmowego (podstawa plus premia).

3. Stwórz widok, w którym zostanie wyświetlone podsumowanie firmy: imię i nazwisko pracownika, dział oraz stanowisko, pensja (podstawa plus premia) oraz czy posiada firmowy samochód (tak/nie) – screen:

z3

 

 

 

 

Rozwiązanie

1. UPDATE dzialy
SET placa_podstawowa = placa_podstawowa * 1.1;

 

2. Wprowadzenie auta do bazy:

INSERT INTO Flota (id_auta, marka, model_auta, kolor, rejestracja, id_prac)
VALUES (6, 'Toyota', 'Yaris', 'bialy', 'DBC 1587', null);

 

Sprawdzenie, kto zarabia najwięcej:

SELECT pr.id_prac, pr.imie, pr.nazwisko, (dz.placa_podstawowa + pr.premia) AS zarobki
FROM pracownicy pr
INNER JOIN dzialy dz USING(id_dzialu)
WHERE pr.id_auta IS NULL
ORDER BY zarobki DESC
FETCH FIRST row only;

 

Przypisanie samochodu:

UPDATE Pracownicy
SET id_auta = 6 WHERE id_prac = 3;
UPDATE Flota
SET id_prac = 3 WHERE id_auta = 6;

W powyższym zapytaniu zamiast konkretnych wartości można podstawić podzapytania.

Zamiast id_prac (3) w pierwszym UPDATE można wstawić zmodyfikowaną wersję zapytania sprawdzającego kto najwięcej zarabia:

SELECT pr.id_prac
FROM pracownicy pr
INNER JOIN dzialy dz USING(id_dzialu)
WHERE pr.id_auta IS NULL
ORDER BY (dz.placa_podstawowa + pr.premia) DESC
FETCH FIRST row only;

A zamiast id_auta (6) w drugim można podstawić:

SELECT id_auta
FROM Flota
WHERE id_prac IS NULL;

 

3. CREATE VIEW Podsumowanie AS
SELECT pr.imie ||' '||pr.nazwisko as pracownik, dz.nazwa_dzialu, dz.stanowisko, (dz.placa_podstawowa + pr.premia) as zarobki,
CASE
WHEN pr.id_auta IS NULL THEN 'Nie'
ELSE 'Tak'
END AS Auto_firmowe
FROM Pracownicy pr inner join Dzialy dz
using (id_dzialu);

[collapse]

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

Dla 10 ostatnich (najnowszych) zamówień określ, czy klient zamawiający ma credit_limit wystarczający na pokrycie kosztów zamówienia. Wynik podaj opisowo – order_id, customer_id, oraz „Limit OK” lub „Limit zbyt mały”.

Schemat: OE, Tabele: Customers, Orders, Kolumny: order_id, customer_id, credit_limit, order_total, Wynik: 10 rows

p25

 

 

 

 

 

 

Rozwiązanie

SELECT o.order_id, customer_id,
CASE
WHEN c.credit_limit >= o.order_total THEN 'Limit OK'
ELSE 'Limit zbyt maly'
END as Limity
FROM orders o INNER JOIN customers c
USING(customer_id)
ORDER BY o.order_id DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

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

Stwórz listę pracowników – id, imię, nazwisko oraz podaj informację, czy dana osoba nadal pracuje (pracuje / nie pracuje).

Schemat: HR, Tabele: Employees, Job_History, Kolumny: employee_id, first_name, last_name, end_date, Wynik: 110 rows

p11

 

 

 

Rozwiązanie

SELECT employee_id, e.first_name, e.last_name,
CASE
WHEN jh.end_date IS NULL THEN 'pracuje'
ELSE 'nie pracuje'
END AS Pracownik
FROM employees e LEFT OUTER JOIN job_history jh USING(employee_id)
ORDER BY employee_id;

[collapse]

Zadanie 4.

Dla klientów o id 103, 227, 131, 172, 165 i 213 wyświetl imiona, nazwiska oraz tytuły grzecznościowe (Pan / Pani).

Schemat: OE, Tabela: Customers, Kolumny: cust_first_name, cust_last_name, gender, Wynik: 6 rows

p4

 

 

 

 

Rozwiązanie

SELECT CASE gender
WHEN 'F' THEN 'Pani'
ELSE 'Pan'
END AS tytul,
cust_first_name, cust_last_name
FROM customers
WHERE customer_id IN(103, 227, 131, 172, 165, 213)
ORDER BY customer_id;

[collapse]
« Starsze wpsiy