drop table letenka;
drop table lety;
drop table z_zakaznik;
drop table pracovnik;



create table z_zakaznik
(
z_rc char(11),
z_meno varchar(20) not null,
z_priezvisko varchar(25) not null,
z_telefon varchar(15),
z_ulica varchar(25),
z_mesto varchar(25),
z_vek number(2),
z_pocet_letov number(3),
primary key (z_rc)
);

create table lety
(
c_letu number(4) not null,
typ_letu varchar(10) not null,
odkial varchar(25) not null,
kam varchar(25) not null,
cas char(5) not null,
constraint pk_c_letu primary key (c_letu)
);

create table pracovnik
(
c_prac number(5),
p_meno varchar(20) not null,
p_priezvisko varchar(25) not null,
p_telefon varchar(15),
p_ulica varchar(25),
p_mesto varchar(25),
p_vek number(2),
p_plat number(5),
constraint pk_id_pr primary key (c_prac)
);

create table v_pracovnik
(
c_prac number(5),
p_meno varchar(20) not null,
p_priezvisko varchar(25) not null,
p_telefon varchar(15),
p_ulica varchar(25),
p_mesto varchar(25),
p_vek number(2),
p_plat number(5),
primary key (c_prac)
);

create table letenka
(
id_letenky char(6),
z_rcislo char(11) not null,
id_letu number(4) not null,
id_prac number(5),
datum date,
zlava number(5),
cena number(6),
trieda char(3),
typ_letenky char(2),
check (typ_letenky in ('OW','RE')),
constraint pk_letenka primary key (id_letenky),
constraint fk_zak foreign key (z_rcislo) references z_zakaznik(z_rc),
constraint fk_let foreign key (id_letu) references lety(c_letu),
constraint fk_prac foreign key (id_prac) references pracovnik (c_prac)
);


insert into pracovnik values('1','Lubko','Benko','0905987475','Dohnanyho 6','Bratislava',29,15000);
insert into pracovnik values(2,'Martin','Barus','0905987474','Dolna 12','Bratislava',44,18500);
insert into pracovnik values(3,'Tomas','Noha','0905987476','Horna 5','Bratislava',32,15000);
insert into pracovnik values(4,'Petra','Horvathova','0905987477','Prazdna 9','Bratislava',21,14000);
insert into pracovnik values(5,'Zuzana','Hlavova','0905987478','Plna 18','Kosice',50,17000);
insert into pracovnik values(6,'Eva','Plucna','0905987479','Stredna 55/a','Kosice',48,16500);
insert into pracovnik values(7,'Vlado','Prst','0905987480','Vysoka 1','Kosice',36,14500);
insert into pracovnik values(8,'Marek','Koza','0905987481','Nizka 4','Kosice',27,13500);

insert into lety values(2001,'Boeing 747','BA','KO','21:00');
insert into lety values(2002,'Boeing 747','BA','KO','21:00');
insert into lety values(2031,'Boeing 777','BA','KO','09:20');
insert into lety values(2201,'Boeing 767','KO','BA','19:15');
insert into lety values(2221,'DC 10','VI','KO','22:00');
insert into lety values(2021,'IL 147','BA','PR','11:30');
insert into lety values(2041,'DC 10','PR','BA','07:05');
insert into lety values(2005,'A 444','KO','VI','10:55');
insert into lety values(2006,'A 444','PR','VI','20:25');


insert into z_zakaznik values('791115/9214','Peter','Hornak','0905242575','Dohnanyho 6','Bratislava',23,1);
insert into z_zakaznik values('796120/1234','Elena','Langerova','0907152575','Riecna 12','Holic',23,1);
insert into z_zakaznik values('491115/9214','Peter','Maly','0905242575','Kvetna 8','Bratislava',54,2);
insert into z_zakaznik values('761115/9214','Juro','Plochy','0905242578','Nam.SNP 1','Bratislava',61,1);
insert into z_zakaznik values('71115/9214','Martin','Velky','0905242576','Lesna 12','Kosice',32,1);
insert into z_zakaznik values('551115/9214','Janko','Strmy','0905242577','Horska 4','Kosice',48,3);
insert into z_zakaznik values('740115/9214','Jaro','Uzky','0905242579','Domova 5','Kosice',29,1);
insert into z_zakaznik values('751115/9214','Dodo','Tucny','0905242580','Vlastna 9','Presov',28,2);

insert into letenka values('AA0005','791115/9214',2001,3,'13/11/03',0,2599,'com','OW');
insert into letenka values('AA0015','791115/9214','2002',3,'13/11/03',0,2599,'com','OW');
insert into letenka values('AA006','491115/9214','2021',4,'16/11/03',0,3599,'bus','OW');
insert into letenka values('AA0007','491115/9214','2221',5,'17/11/03',0,4599,'bus','RE');
insert into letenka values('AA0013','796120/1234','2006',6,'02/12/03',0,7199,'bus','RE');
insert into letenka values('AA0011','761115/9214','2005',6,'22/11/03',0,5499,'bus','OW');
insert into letenka values('AA0145','761115/9214','2006',6,'23/11/03',0,4499,'com','OW');


Zadanie:
Na foliu sa zada logicky model (vid priloha v doc)


1) Vyber meno, priezvisko, vek najstarsieho a najmladsieho pracovnika.

Riesenie:
select p_meno, p_priezvisko, p_vek
from pracovnik
where p_vek=(select min(p_vek) from pracovnik) OR p_vek=(select max(p_vek) from pracovnik);

2) Vyber pracovnika, ktory predal najviac leteniek

Riesenie:
select p.c_prac, p.p_meno, p.p_priezvisko, count(*) as pocet_leteniek
from pracovnik p, letenka l
where p.c_prac=l.id_prac
group by p.c_prac,p.p_meno,p.p_priezvisko
having count(*)=(select max(count(*)) from letenka group by id_prac);

3)
Vypiste meno a priezvisko zakaznika, ktori leteli Airbusom 444, cenu letenky, datum kedy mu bola vystavena letenka a kym, pre tych zakaznikov, ktori leteli po Novembri 2003.

Riesenie:
select z.z_meno, z.z_priezvisko, l.typ_letu as Lietadlo,n.datum as Datum, n.cena as Cena_letenky, p.p_priezvisko as Vystavil
from z_zakaznik z, lety l, letenka n, pracovnik p
where z.z_rc=n.z_rcislo and l.c_letu=n.id_letu AND n.id_prac=p.c_prac AND l.typ_letu='A 444' AND n.datum>'01/12/03';

4)
Vypiste priemernu cenu, maximalnu cenu, minimalnu cenu leteniek pre jednotlive typy lietadiel podla ceny vzostupne

Riesenie:
select l.typ_letu, AVG(n.cena) as Priemerna_cena, Max(n.cena) as Maximalna_cena, min(n.cena) as Minimalna_cena
from lety l, letenka n
where n.id_letu=l.c_letu
group by l.typ_letu
order by priemerna_cena asc;

5)
Vypiste, aku sumu penazi jednotlivy zakaznici prelietali zostupne.

Riesenie:
select z.z_rc, z.z_meno, z.z_priezvisko, SUM(n.cena) AS prelietal
from z_zakaznik z, letenka n
where n.z_rcislo=z.z_rc
group by z.z_rc, z.z_meno, z.z_priezvisko
order by prelietal desc;

6)
Kvoli zvyseniu nakladov na prevadzkovanie business triedy je potrebne zvysit cenu pre tuto triedu. Vytvorte proceduru, ktora nam zabezpeci, ze zmena
ceny pre business class bude vo vyske 15%.

create procedure zmenaceny (o_kolko in number)
as begin
update letenka
set cena=cena*o_kolko
where trieda='bus';
END;
.
run;

7)
Vytvorte proceduru pre zvysenie platu vybraneho zamestnanca o vybranu sumu (napr. pracovnik c.1 o 200Sk)


create procedure zvys_plat ( komu in number, o_kolko in number)
as begin
update pracovnik
set p_plat=p_plat+o_kolko
where c_prac=komu;
end;


Bonusovy priklad:

8) Vytvorte trigger, ktory pri vyradeni pracovnika z tabulky pracovnik automaticky vytvori novy zaznam do tabulky byvalych pracovnikov (v_pracovnik)

Riesenie (musi byt vytvorena tabulka v_pracovnik):

Create or replace trigger vyrad_prac
Before delete on pracovnik
For each row
Begin
Insert into v_pracovnik values(:old.c_prac,:old.p_meno,:old.p_priezvisko,:old.p_telefon,:old.p_ulica,:old.p_mesto,:old.p_vek,:old.p_plat);
End vyrad_prac;
.