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

Korzystając z tabel stworzonych w Zadaniu 1:

1. Stwórzmy nowy dział – IT. Dwa stanowiska – Administrator (ID działu – 500) oraz Programista (ID działu – 550). Płaca podstawowa dla obu 3500.

2. Nowy dział trzeba “zaludnić”. Administratorem będzie Andrzej Szyszka (id 12, zatrudniony 5 listopada 2015, urodzony 10 września 1973, nadal pracuje, telefon 667667667, premia 500). Programistą zaś został Adrian Klon (id 13, zatrudniony 2 listopada 2015, urodzony 14 marca 1984, nadal pracuje, telefon 665665665, premia 700).

3. Administratorowi przypisz wolny samochód, jeśli taki jest.

4. Nowa polityka firmy mówi, że nikt nie może dostawać premii niższej niż 350. Uaktualnij dane tak, by były zgodne z tą polityką.

5. Tabela Flota. Każdemu samochodowi przypisz odpowiadający mu numer pracownika (id_prac).

 

Rozwiązanie


1. INSERT ALL
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES (500, 'IT', 'Administrator', 3500)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(550, 'IT', 'Programista', 3500)
SELECT * FROM dual;

 

2.  INSERT ALL
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (12, 'Andrzej', 'Szyszka', 500, null, 500, '2015-11-05', '1973-09-10', null, '667667667')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (13, 'Adrian', 'Klon', 550, null, 700, '2015-11-02', '1984-03-14', null, '665665665')
SELECT * FROM dual;

 

3. Najpierw sprawdzamy, czy mamy na stanie wolne auto:

SELECT *
FROM Flota
WHERE id_auta not in (SELECT id_auta FROM Pracownicy WHERE id_auta is not null);

Mamy jedno wolne auto, więc możemy je przypisać Adminowi:

UPDATE Pracownicy
SET id_auta = '9'
WHERE id_dzialu = '500';

 

4. UPDATE Pracownicy
SET premia = 350
WHERE premia < 350;

 

5. UPDATE Flota
SET id_prac = 1 WHERE id_auta = 12;
UPDATE Flota
SET id_prac = 2 WHERE id_auta = 11;
UPDATE Flota
SET id_prac = 5 WHERE id_auta = 10;
UPDATE Flota
SET id_prac = 12 WHERE id_auta = 9;
UPDATE Flota
SET id_prac = 7 WHERE id_auta = 8;
UPDATE Flota
SET id_prac = 6 WHERE id_auta = 7;

[collapse]

Zadanie 1.

Na potrzeby kolejnych zadań stwórzmy sobie mini-bazę, składającą się z trzech tabel: Pracownicy (dane pracowników), Działy (informacje o działach firmy) i Flota (samochody w firmie).

Dane do stworzenia tabel – poniżej.

Ja stworzyłam je w schemacie HR.

Screen przedstawia dane wynikowe po połączeniu wszystkich trzech tabel:

z1

 

 

Tworzenie tabel


-- najpierw tworzymy tabelę Dzialy:

CREATE TABLE Dzialy (
id_dzialu INTEGER CONSTRAINT id_dzialu_pk PRIMARY KEY,
nazwa_dzialu VARCHAR2(20) NOT NULL,
stanowisko VARCHAR2(30) NOT NULL,
placa_podstawowa INTEGER NOT NULL
);

-- potem tworzymy tabelę Flota:

CREATE TABLE Flota (
id_auta INTEGER CONSTRAINT id_auta_pk PRIMARY KEY,
marka VARCHAR2(20) NOT NULL,
model_auta VARCHAR2(20) NOT NULL,
kolor VARCHAR2(20),
rejestracja VARCHAR2(10)
);

-- teraz czas na Pracowników:

CREATE TABLE Pracownicy (

id_prac INTEGER CONSTRAINT id_prac_pk PRIMARY KEY,
imie VARCHAR2(20) NOT NULL,
nazwisko VARCHAR2(20) NOT NULL,
data_zatrudnienia DATE NOT NULL,
data_urodzenia DATE NOT NULL,
data_zakonczenia DATE,
telefon VARCHAR2(9),
premia INTEGER DEFAULT '0',
id_dzialu INTEGER,
id_auta INTEGER,
CONSTRAINT id_dzialu_fk FOREIGN KEY (id_dzialu) REFERENCES Dzialy(id_dzialu),
CONSTRAINT id_auta_fk FOREIGN KEY (id_auta) REFERENCES Flota(id_auta)
);

-- modyfikujemy Flotę dodając powiązania z tabelą Pracowników:

ALTER TABLE Flota
ADD id_prac INTEGER;

ALTER TABLE Flota
ADD CONSTRAINT id_prac_fk FOREIGN KEY (id_prac) REFERENCES Pracownicy(id_prac);

--...i na koniec wypelniamy wszystkie trzy tabelki danymi:

INSERT ALL
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES (100, 'Zarząd', 'Prezes', 7000)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(150, 'Zarząd', 'Zastępca Prezesa', 5000)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(200, 'Kadry', 'Glówny Specjalista', 3500)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(250, 'Kadry', 'Mlodszy Specjalista', 2600)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(300, 'Sprzedaż', 'Manager Sprzedaży', 3000)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(350, 'Sprzedaż', 'Handlowiec', 2650)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(400, 'Obsluga', 'Ochrona', 2500)
INTO Dzialy(id_dzialu, nazwa_dzialu, stanowisko, placa_podstawowa) VALUES(450, 'Obsluga', 'Sprzątanie', 2300)
SELECT * FROM dual;

INSERT ALL
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (7, 'Toyota', 'Yaris', 'biay', 'ABC 1258')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (8, 'Toyota', 'Yaris', 'biay', 'ABC 2423')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (9, 'Toyota', 'Yaris', 'czarny', 'ABB 3537')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (10, 'Toyota', 'Auris', 'srebrny', 'ABA 2587')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (11, 'Suzuki', 'SX4', 'srebrny', 'CBC 3285')
INTO Flota(id_auta, marka, model_auta, kolor, rejestracja) VALUES (12, 'Suzuki', 'SX4', 'czerwony', 'ABA 8327')
SELECT * FROM dual;

INSERT ALL
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (1, 'Anna', 'Kowalska', 100, 12, 1600, '2005-10-19', '1975-12-03', null, '776776776')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (2, 'Jaromir', 'Cieśla', 150, 11, 1300, '2005-10-19', '1978-10-05', null, '775775775')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (3, 'Janina', 'Nowak', 200, null, 950, '2005-10-19', '1966-02-15', null, '774774774')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (4, 'Amelia', 'Beryl', 250, null, 600, '2007-06-23', '1987-07-13', null, '773773773')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (5, 'Jacek', 'Onyks', 300, 10, 500, '2005-10-30', '1982-08-09', null, '772772772')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (6, 'Ernest', 'Opal', 350, 7, 500, '2009-05-01', '1976-07-05', null, '771771771')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (7, 'Anna', 'Nowakowska', 350, 8, 400, '2010-06-05', '1985-11-04', null, '770770770')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (8, 'Jan', 'Rębacz', 350, null, 0, '2015-10-01', '1973-05-12', null, '779779779')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (9, 'Roman', 'Kowal', 400, null, 300, '2008-08-01', '1953-07-09', null, '778778778')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (10, 'Stefan', 'Rolnik', 400, null, 350, '2009-09-01', '1966-08-17', null, '778778778')
INTO Pracownicy(id_prac, imie, nazwisko, id_dzialu, id_auta, premia, data_zatrudnienia, data_urodzenia, data_zakonczenia, telefon)
VALUES (11, 'Renata', 'Gąbka', 450, null, 400, '2011-06-15', '1968-09-09', null, '765765765')
SELECT * FROM dual;

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

Korzystając z widoku utworzonego w zadaniu 23, podsumujmy sprzedaż według produktów.

a) które produkty sprzedawały się najlepiej – ranking top 10

s24a

 

 

 

 

b) który produkt sprzedawał się najlepiej w konkretnych latach – ranking top 5 produktów na dany rok

s24b

 

 

 

 

 

c) który produkt sprzedawał się najlepiej w każdej kategorii?

s24c

 

 

 

 

 

d) który produkt sprzedawał się najlepiej w pierwszych trzech miesiącach lat 2006-2007? Ranking top 3 produktów.

s24d

 

 

 

 

Schemat: OE, Tabela: Product_Information oraz widok utworzony w zadaniu 23, Kolumny: product_id, quantity, ord_year, ord_month, category_id, Wynik: a) 10 rows b) 20 rows c) 17 rows d) 15 rows

Rozwiązanie

a) SELECT product_id, SUM(quantity) AS quantity, dense_rank() over(order by SUM(quantity) DESC) AS rank
FROM my_order_view
GROUP BY product_id
ORDER BY quantity DESC
FETCH FIRST 10 rows WITH ties;

b) WITH Subquery AS (
SELECT ord_year, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by ord_year order by SUM(quantity) DESC) AS rank
FROM my_order_view
GROUP BY product_id, ord_year
)

SELECT * FROM Subquery
WHERE rank <= 5
ORDER BY ord_year, rank;

c) WITH Subquery AS (
SELECT pi.category_id, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by pi.category_id order by SUM(quantity) DESC) AS rank
FROM my_order_view inner join product_information pi using(product_id)
GROUP BY pi.category_id, product_id
)

SELECT category_id, product_id, quantity
FROM Subquery
WHERE rank = 1;

d) WITH Subquery AS (
SELECT ord_year, ord_month, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by ord_year, ord_month order by SUM(quantity) DESC) AS rank
FROM my_order_view
WHERE ord_month in(1,2,3) and ord_year in(2006, 2007)
GROUP BY ord_year, ord_month, product_id
)

SELECT * FROM Subquery
WHERE rank <= 3;

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