Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --vyberte zoznam zamestnancov spolocnosti “NejakaSpolocnost”,
- --ktori maju narodeniny v nasledujucich 14 dnoch
- SELECT meno, priezvisko, rod_cislo
- FROM l_zamestnanec
- JOIN l_osoba ON l_osoba.cislo_dokladu=l_zamestnanec.cislo_dokladu AND
- l_osoba.typ_dokladu=l_zamestnanec.typ_dokladu
- JOIN l_let_spolocnost USING(id_spolocnosti)
- WHERE l_let_spolocnost.NAZOV_SPOL='Ryanair'
- AND to_date(SUBSTR(rod_cislo,5,2)||'.'||MOD(SUBSTR(rod_cislo,3,2),50), 'dd.mm') BETWEEN sysdate AND (sysdate+14);
- --vypiste pocet priletov a odletov z letiska v prahe za vcerajsok
- --prilety + odlety z Prahy a v datume<dnes-100dni, dnes>
- SELECT id_letu, mesto_z.nazov, mesto_do.nazov, datum_letu
- FROM l_let
- JOIN l_letisko let_z ON l_let.letisko_z=let_z.id_letiska
- JOIN l_letisko let_do ON l_let.letisko_do=let_do.id_letiska
- JOIN l_mesto mesto_z ON let_z.psc=mesto_z.PSC
- JOIN l_mesto mesto_do ON let_do.psc=mesto_do.psc
- WHERE (mesto_do.nazov='Praha' OR mesto_z.nazov='Praha') AND datum_letu BETWEEN (sysdate-100) AND sysdate
- ORDER BY datum_letu DESC;
- --vypiste zoznam zamestnancov spolocnosti “NejakaSpolocnost”,
- --ktori so spolocnostou 'XY' zatial neleteli
- SELECT id_zamestnanca
- FROM l_letenka
- JOIN l_zamestnanec USING(id_zamestnanca)
- WHERE l_zamestnanec.ID_SPOLOCNOSTI=2 --zamestnanec je zo spolocnosti s id=2
- AND id_zamestnanca NOT IN(
- -- select zamestnancov, ktory leteli spolocnostou XY
- SELECT id_zamestnanca
- FROM l_letenka
- JOIN l_let USING(id_letu)
- JOIN l_let_spolocnost USING(id_spolocnosti)
- WHERE ID_SPOLOCNOSTI='4'
- );
- --vypiste meno spol, id letu… a kopec dalsi info o lete z bratislavy alebo viedne do londyna,
- --ktore su 19.3.2016 +-3 dni
- SELECT nazov_spol, id_letu, datum_letu, mesto_z.NAZOV, mesto_do.NAZOV
- FROM l_let
- JOIN l_let_spolocnost USING(id_spolocnosti)
- JOIN l_letisko let_z ON l_let.letisko_z=let_z.ID_LETISKA
- JOIN l_letisko let_do ON l_let.letisko_do=let_do.ID_LETISKA
- JOIN l_mesto mesto_z ON let_z.psc=mesto_z.psc
- JOIN l_mesto mesto_do ON let_do.psc=mesto_do.psc
- WHERE (mesto_z.nazov='Bratislava' OR mesto_z.nazov='Vieden') AND mesto_do.nazov='Londyn' AND datum_letu BETWEEN to_date('16.3.2016', 'dd.mm.YYYY') AND to_date('22.3.2016', 'dd.mm.YYYY');
- SELECT * FROM l_mesto ORDER BY id_krajiny ASC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement