Tagi: join

Zadanie 4.

Napisz funkcję, która będzie przyjmować jako parametr ID pracownika, a w wyniku wyświetlać dane tego pracownika – imię, nazwisko oraz stanowisko.

Schemat: HR, Tabele: Employees, Jobs, Kolumny: first_name, last_name, job_title

fun-z-param

 

 

Rozwiązanie

create or replace function pracownik (id_in in number)
return varchar is
wynik varchar(200);
begin
select e.first_name ||' '|| e.last_name ||' '||' - '|| j.job_title into wynik
from employees e inner join jobs j
on e.job_id = j.job_id
where e.employee_id = id_in;

return wynik;
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 7.

Zróbmy sobie symulację banku (mocno uproszczoną).

1. Stwórz tabelę Bank_klienci, zawierającą pola ID (typ identity, primary key), imię, nazwisko, miasto.

Stwórz drugą tabelę, Bank_konta, zawierającą pola ID (klucz obcy do tabeli Bank_klienci), nr_konta (dowolny 26-znakowy numer; primary key), saldo.

2. Wypełnij tabelę danymi:

DANE

Dane dla Bank_klienci:

('Anna', 'Braun', 'Warsaw'),
('Celia', 'Donut', 'Berlin'),
('Ellie', 'Fine', 'Berlin'),
('Gloria', 'Hint', 'Paris'),
('Ian', 'Johnson', 'Warsaw'),
('Kelly', 'Long', 'Paris'),
('Merry', 'Nice', 'Paris'),
('Olaf', 'Priceless', 'Berlin'),
('Richard', 'Short', 'Warsaw'),
('Tim', 'Uncle', 'Warsaw')

Dane dla Bank_konta:

(1, '12345678932165498714785236', 1652),
(2, '85235678932165498714765416', 15698),
(3, '74135678932165498714789632', 9652),
(4, '96815678932165498714783258', 10367),
(5, '32175678932165498714783699', 357),
(6, '86115678932165498714781147', 14631),
(7, '33585678932165498714783247', 11900),
(8, '77565678932165498714786874', 3549),
(9, '49635678932165498714780258', 2463),
(10, '10285678932165498714783049', 7681)

[collapse]

3. Utwórz widok (Bank_widok), który uwzględni osoby z saldem 1000 lub mniejszym oraz tych z saldem 10000 lub większym.

4. Utwórz transakcję, w której klient o ID 3 przeleje klientowi o ID 7 kwotę 1450zł. Odwołaj transakcję.

5. Utwórz transakcję, w której klient o ID 6 przeleje klientowi o ID 5 kwotę 220zł. Potwierdź transakcję.

6. Dodaj kolejnego klienta – Wu X’ian z saldem 31500. Miasto – Paris, nr konta: 25025536548520147930286057.

Baza: Northwind, Tabela: Bank_klienci, Bank_konta, kolumny: ID, Imie, Nazwisko, Miaso, Saldo, nrKonta, Wynik: 11 rows

Screen przedstawia dane z obu tabel po wykonaniu powyższych punktów:

z7

 

 

 

 

 

Rozwiązanie

1.Tworzenie tabel

CREATE TABLE dbo.Bank_klienci
(
ID INT NOT NULL identity(1,1) PRIMARY KEY,
Imie varchar(20) NOT NULL,
Nazwisko varchar(20) NOT NULL,
Miasto varchar(20) NOT NULL
);

CREATE TABLE dbo.Bank_konta
(
ID INT NOT NULL,
nrKonta varchar(26) NOT NULL PRIMARY KEY,
Saldo INT NOT NULL,
CONSTRAINT fk_ID FOREIGN KEY (ID) REFERENCES dbo.Bank_klienci (ID)

);

2.Wypełnianie tabel danymi

INSERT INTO  dbo.Bank_klienci(Imie, Nazwisko, Miasto) VALUES
('Anna', 'Braun', 'Warsaw'),
('Celia', 'Donut', 'Berlin'),
('Ellie', 'Fine', 'Berlin'),
('Gloria', 'Hint', 'Paris'),
('Ian', 'Johnson', 'Warsaw'),
('Kelly', 'Long', 'Paris'),
('Merry', 'Nice', 'Paris'),
('Olaf', 'Priceless', 'Berlin'),
('Richard', 'Short', 'Warsaw'),
('Tim', 'Uncle', 'Warsaw');

INSERT INTO dbo.Bank_konta(ID, nrKonta, Saldo) VALUES
(1, '12345678932165498714785236', 1652),
(2, '85235678932165498714765416', 15698),
(3, '74135678932165498714789632', 9652),
(4, '96815678932165498714783258', 10367),
(5, '32175678932165498714783699', 357),
(6, '86115678932165498714781147', 14631),
(7, '33585678932165498714783247', 11900),
(8, '77565678932165498714786874', 3549),
(9, '49635678932165498714780258', 2463),
(10, '10285678932165498714783049', 7681);

3.Widok

CREATE VIEW dbo.Bank_widok AS
SELECT KL.ID, KL.Imie, KL.Nazwisko, KL.Miasto, KO.Saldo
FROM Bank_klienci KL inner join Bank_konta KO
ON KL.ID = KO.ID
WHERE KO.Saldo <= 1000 OR KO.Saldo >= 10000;

4.Transakcja odwołana

BEGIN TRAN;
UPDATE dbo.Bank_konta SET Saldo = Saldo - 1450 WHERE ID = 3;
UPDATE dbo.Bank_konta SET Saldo = Saldo + 1450 WHERE ID = 7;
ROLLBACK TRAN;

5.Transakcja zatwierdzona

BEGIN TRAN;
UPDATE dbo.Bank_konta SET Saldo = Saldo - 220 WHERE ID = 6;
UPDATE dbo.Bank_konta SET Saldo = Saldo + 220 WHERE ID = 5;
COMMIT TRAN;

6.Dodanie kolejnego klienta

INSERT INTO  dbo.Bank_klienci(Imie, Nazwisko, Miasto) VALUES
('Wu', 'X''ian', 'Paris');

INSERT INTO dbo.Bank_konta(ID, nrKonta, Saldo) VALUES
(11, '25025536548520147930286057', 31500);

[collapse]

Zadanie 23.

Stwórzmy widok, zawierający dane dot. sprzedaży, który posłuży nam w kolejnych zadaniach jako źródło danych do analizy. Widok powinien zawierać takie informacje, jak: nr zamówienia, nr klienta, rok złożenia zamówienia, miesiąc złożenia zamówienia, kwota zamówienia, id produktu, ilość produktu, id sprzedającego.

Schemat: OE, Tabele: Orders, Order_Items, Kolumny: order_id, order_date, customer_id, order_total, sales_rep_id, product_id, quantity, Wynik: 665 rows

s23

 

 

Rozwiązanie

CREATE OR REPLACE VIEW My_Order_View
AS
SELECT order_id, EXTRACT(YEAR from o.order_date) as ord_year, EXTRACT(MONTH from o.order_date) as ord_month,
oi.product_id, oi.quantity, o.customer_id, o.sales_rep_id, o.order_total
FROM orders o
INNER JOIN order_items oi USING(order_id);

SELECT * FROM My_Order_View
ORDER BY ord_year, ord_month;

 

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

Utwórz widok Order_Summary, który będzie zawierał informacje o zamówieniach: ID zamówienia, datę (bez części godzinowej), tryb złożenia zamówienia, łączną wartość, produkty, ich ilość i cenę jednostkową.

Schemat: OE, Tabele: Order_Items, Orders, Kolumny: order_id, order_mode, order_date, order_total, product_id, quantity, unit_price, Wynik: 665 rows

s6

 

 

Rozwiązanie


CREATE OR REPLACE VIEW Order_Summary
AS
SELECT order_id, o.order_mode, TO_CHAR(o.order_date, 'DD.MM.YYYY') AS order_date,
o.order_total, oi.product_id, oi.quantity, oi.unit_price
FROM orders o
INNER JOIN order_items oi USING(order_id);

SELECT * FROM order_summary;

[collapse]

Zadanie 5.

Którzy dostawcy dostarczają produkty zamówień złożonych przez klientów o ID 107 i 149? Zadanie rozwiąż na dwa sposoby.

Schemat: OE, Tabele: Product_Information, Order_Items, Orders, Kolumny: supplier_id, order_id, product_id, Wynik: 32 rows

s5

 

 

 

 

 

Rozwiązanie

1. Łączenie tabel:

SELECT DISTINCT pi.supplier_id
FROM order_items oi
INNER JOIN product_information pi USING(product_id)
INNER JOIN orders ord USING(order_id)
WHERE ord.customer_id IN(107, 149)
ORDER BY supplier_id;

2. Podzapytania:

SELECT DISTINCT supplier_id
FROM product_information
WHERE product_id IN
(SELECT product_id
FROM order_items
WHERE order_id IN
( SELECT order_id FROM orders WHERE customer_id IN(107, 149)
)
)

ORDER BY supplier_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 24.

Dla zamówień o numerach 2355, 2413, 2377 i 2361 policz:

a) ile produktów zawiera każde z nich oraz podaj średnią cenę produktu

b) podaj kraj, z którego zostało złożone zamówienie; ile zamówień ma na koncie klient, który je złożył?

Schemat: OE, Tabele: Orders, Customers, Order_Items, Kolumny: order_id, product_id, unit_price, nls_territory, Wynik: 4 rows

Rozwiązanie

a) SELECT order_id, COUNT(product_id) as Products, to_char(AVG(unit_price), '999D99')as Avg_Price
FROM orders o INNER JOIN order_items oi USING(order_id)
WHERE order_id IN(2355, 2413, 2377, 2361)
GROUP BY order_id;

b) SELECT customer_id, MIN(c.nls_territory) as Country, COUNT(o.order_id) as Orders
FROM orders o INNER JOIN customers c USING(customer_id)
WHERE o.order_id IN(2355, 2413, 2377, 2361)
GROUP BY customer_id;

[collapse]

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]
« Starsze wpsiy