Kategoria: Oracle SQL

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

Korzystając z kursora i pętli for, wyświetl wszystkie auta floty marki Suzuki (marka, model, numer rejestracyjny).

Schemat: HR, Tabela: Flota, Kolumny: marka, model_auta, rejestracja

Rozwiązanie

set serveruotput on;

declare
cursor crs_flota is
select marka, model_auta, rejestracja
from flota
where marka = 'Suzuki';

begin
for v_car in crs_flota loop
dbms_output.put_line(v_car.marka || ' ' || v_car.model_auta || ', ' || v_car.rejestracja);
end loop;
end;
/

[collapse]

Zadanie 2.

Korzystając z tabel Pracownicy oraz Działy (stworzone w Zadaniu 1 poziomu Zaawansowanego) napisz kursor, który pobierze z tabel imię, nazwisko oraz płacę podstawową każdego pracownika, a następnie wyświetli je w formie: Ania Kowalska zarabia 2000.

a) zmodyfikuj kod tak, by pracownicy byli wyświetlani w kolejności alfabetycznej wg nazwiska.

Schemat: HR, Tabele: Pracownicy, Działy, Kolumny: imie, nazwisko, placa_podstawowa.

pl2

 

 

 

 

Rozwiązanie

set serveroutput on;
-- deklaracja zmiennych
declare
cv_imie pracownicy.imie%TYPE;
cv_nazwisko pracownicy.nazwisko%type;
cv_placa DZIALY.PLACA_PODSTAWOWA%type;
-- deklaracja kursora
cursor crs_prac is
  select p.imie, p.nazwisko, d.PLACA_PODSTAWOWA
  from pracownicy p inner join dzialy d
  on p.id_dzialu = d.id_dzialu;
-- otwarcie kursora
begin
open crs_prac;
-- pobieranie wierszy z kursora
loop
  fetch crs_prac
  into cv_imie, cv_nazwisko, cv_placa;
  
  exit when crs_prac%notfound;
  
  DBMS_OUTPUT.PUT_LINE(cv_imie ||' ' || cv_nazwisko || ' zarabia ' || cv_placa);
end loop;

-- zamkniecie kursora
close crs_prac;
end;
/

a) deklarację kursora należy zmienić dopisując klauzulę order by:

-- deklaracja kursora
cursor crs_prac is
  select p.imie, p.nazwisko, d.PLACA_PODSTAWOWA
  from pracownicy p inner join dzialy d
  on p.id_dzialu = d.id_dzialu
  order by p.nazwisko;

[collapse]

			
		

Zadanie 1.

Chcemy wyświetlić kolejne liczby od 10 do 1. Stwórz trzy rozwiązania, korzystając z trzech różnych pętli (Loop, While, For).

Rozwiązanie

Pętla Loop:

set serveroutput on;

declare
  n number := 10;
begin
  loop
  dbms_output.put_line(n);
  n:= n-1;
  exit when n = 0;
  end loop;
end;
/

 

Pętla While:

set serveroutput on;

declare
  n number := 10;
begin
  while n > 0 loop
  dbms_output.put_line(n);
  n:= n-1;
  end loop;
end;
/

 

Pętla For:

set serveroutput on;

declare
  n number := 10;
begin
  for n in reverse 1..10 loop
  dbms_output.put_line(n);
  end loop;
end;
/

[collapse]

Zadanie 4.

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

Szef przysłał nowy wykaz premii. Jest on w pliku tekstowym, wygląda tak:

Wykaz premii

1;1500
2;110
3;800
4;500
5;450
6;450
7;300
8;375
9;300
10;500
11;400
12;400
13;600

[collapse]

Gdzie 1..13 to id pracownika, a liczba po średniku to nowa wartość premii.
Naszym zadaniem jest zaktualizowanie danych w bazie, jednak nie chcemy pisać ręcznie kilkunastu poleceń update.
Jak można to zautomatyzować?

Rozwiązanie

Dane z pliku kopiujemy i wklejamy do pustego arkusza Excela. Następnie rozdzielamy je, tak by w jednej kolumnie było id pracownika, a w drugiej odpowiadająca mu wartość premii.
Następnie w kolejnej kolumnie wpisujemy formułkę postaci: =”update pracownicy set premia=”&B1&” where id_prac=”&A1&”;” (zakładając, że wartość premii mamy w kolumnie B, a id pracowników w A). Teraz wystarczy przeciągnąć formułkę na pozostałe pola (lub kliknąć dwa razy na mały czarny kwadrat w prawym dolnym rogu zaznaczonej komórki) i reszta poleceń wygeneruje się automatycznie.
z4

 

 

 

 

Jedyne co nam pozostaje to przekopiować otrzymane zapytania do SQL Developera i uruchomić je. Pamiętajmy o zatwierdzeniu transakcji (po ostatnim zapytaniu dopisać commit lub kliknąć w odpowiednią ikonkę powyżej okna edytora; domyślnie F11).

W ten sposób możemy wygenerować właściwie dowolną liczbę kwerend.

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