/* Semestrální práce z předmětu X36DBS - Nádraží */
/* Autor: Jan Skalický (skalij2) */

/* SQL skript s dotazy nad db */
/******************************/
/* pozn.: v zavorkach za formulaci dotazu v prirozenem jazyce je uvedena kategorie, do ktere dotaz patri (viz Pozadavky na semestralni praci) */
/* pozn.: dotazy, jejichz poradi ve skriptu je delitelne 5 jsou pro lepsi orientaci ve vystupu doplneny pomocnym sloupcem DOTAZ#_? (? je cislo casti)*/


/* CAST 1 - dotazy z postupu uzivatelskych scenaru (viz HMTL zprava), prepsany do SQL (obecne parametry jsou nahrazeny konkretnimi): */
/*************************************************************************************************************************************/

/* 01. Dotaz, zda-li je dotyčný pracovník (s rodným číslem 500521072) strojvedoucí: (A) */
/* STROJVUDCE(PRACOVNIK_RC = 500521072) */
SELECT  1 AS  DOTAZ#_1, STROJVUDCE.* FROM  STROJVUDCE WHERE  PRACOVNIK_RC = 500521072;

/* 02. Dotaz, zda-li dotyčný pracovník (s rodným číslem 7708020118) již požadované oprávnění (pro typ vozu 5) nemá: (B) */
/* OPRAVNENI(OPRAVNEN_PRACOVNIK_RC = 7708020118, K_RIZENI_TYP_VUZ_ID_TYP_VUZ = 5) */
SELECT  * FROM  OPRAVNENI WHERE  (OPRAVNEN_PRACOVNIK_RC = 7708020118) AND  (K_RIZENI_TYP_VUZ_ID_TYP_VUZ = 5);

/* 03. Dotaz, zda-li dotyčná osoba (s rodným číslem 6203150060) již není pracovníkem železnice: (A) */
/* PRACOVNIK(RC = 6203150060) */
SELECT  * FROM  PRACOVNIK WHERE  RC = 6203150060;

/* 04. Dotaz na neobsazená pracoviště v jednotlivých směnách: (GIP) */
/* { SMENA[true]PRACOVISTE }(POCET_MIST > count(ZAMESTNANI(PRACOVISTE_ID_PRACOVISTE = ID_PRACOVISTE, SMENA_CISLO_SMENY = CISLO_SMENY)) */
SELECT  Distinct  CISLO_SMENY, SKUPINA_P_POPIS, SMEROVKA, ID_PRACOVISTE FROM  
        SMENA CROSS  JOIN  PRACOVISTE 
WHERE  
        POCET_MIST > 
                ( SELECT  COUNT (*) FROM  ZAMESTNANI 
                WHERE  (ID_PRACOVISTE = PRACOVISTE_ID_PRACOVISTE) AND  (SMENA_CISLO_SMENY = CISLO_SMENY) ) 
ORDER  BY  ID_PRACOVISTE;

/* 05. jako 04, ale s doplnenim poctu pracovniku, ktere v dane kombinaci SMENA, ZAMESTNANI chybi (GIKP) */
SELECT  Distinct  5 AS  DOTAZ#_1, CISLO_SMENY, SKUPINA_P_POPIS, SMEROVKA, ID_PRACOVISTE, 
        POCET_MIST - ( SELECT  COUNT (*) FROM  ZAMESTNANI WHERE  
                (ID_PRACOVISTE = PRACOVISTE_ID_PRACOVISTE) AND  (SMENA_CISLO_SMENY = CISLO_SMENY) ) 
        AS  SCHAZI 
FROM
        SMENA CROSS  JOIN  PRACOVISTE 
WHERE  
        POCET_MIST > 
                ( SELECT  COUNT (*) FROM  ZAMESTNANI 
                WHERE  (ID_PRACOVISTE = PRACOVISTE_ID_PRACOVISTE) AND  (SMENA_CISLO_SMENY = CISLO_SMENY) ) 
ORDER  BY  ID_PRACOVISTE;

/* 06. Nalezení pracovníka s kvalifikací pro neobsazené pracoviště (s popisem skupiny SERVIS), který je v obsazované směně (1) ještě volný: (GJ) */
/* { {OBECNY - {OBECNY[*]ZAMESTNANI}(SMENA_CISLO_SMENY = 1)[atributy OBECNY] } [SKUPINA_Z_POPIS = POPIS] SKUPINA_PRACOVNIKU }(OD_PRACOVIST_POPIS = "SERVIS")[PRACOVNIK_RC] */
SELECT  PRACOVNIK_RC FROM  
        ( SELECT  * FROM  OBECNY MINUS  
        SELECT  PRACOVNIK_RC, SKUPINA_Z_POPIS FROM  OBECNY NATURAL  JOIN  ZAMESTNANI WHERE  SMENA_CISLO_SMENY = 1 )
        JOIN  SKUPINA_PRACOVNIKU ON  (SKUPINA_Z_POPIS = POPIS )
WHERE  OD_PRACOVIST_POPIS = 'SERVIS';

/* 07. jako 06, ale s doplnenim jmen pracovniku a jejich aktualni vytizenosti: (GJKP) */
SELECT  JMENO, PRACOVNIK_RC, 
        (SELECT  COUNT (*) FROM  (SELECT  PRACOVISTE_ID_PRACOVISTE, SMENA_CISLO_SMENY, PRACOVNIK_RC AS  X FROM  ZAMESTNANI) WHERE  PRACOVNIK_RC = X) AS  VYTIZENOST
FROM  
        ( SELECT  * FROM  OBECNY MINUS  
        SELECT  PRACOVNIK_RC, SKUPINA_Z_POPIS FROM  OBECNY NATURAL  JOIN  ZAMESTNANI WHERE  SMENA_CISLO_SMENY = 1 ) 
        JOIN  SKUPINA_PRACOVNIKU ON  (SKUPINA_Z_POPIS = POPIS) 
        JOIN  PRACOVNIK ON  (PRACOVNIK_RC = RC ) 
WHERE  OD_PRACOVIST_POPIS = 'SERVIS'
ORDER  BY  VYTIZENOST;
/* pozn.: aliasovani sloupce PRACOVNIK_RC v poddotazu v klauzuli SELECT je nutne, protoze jinak by tento sloupec zastinil vnejsi sloupec se stejnym jmenem; tyto 2 potrebujeme porovnat */

/* 08. Zjištení vozů volných pro sestavení řazení nového spoje (ID = 999 - vytvorime ho za ucelem dotazu): (CEN) */
/* VUZ - { VUZ[*]RAZENI[*]SPOJ [ODJEZD_SPOJ < PRIJEZD_SPOJ and PRIJEZD_SPOJ > ODJEZD_SPOJ] {SPOJ(SPOJ_ID_SPOJ = 999) }[ID_VUZ] } - {analogicky pro lokomotiovy} */
ALTER  session set NLS_DATE_FORMAT = 'hh24:mi';
INSERT  into SPOJ (ZRYCHLENY, OSOBNI_NAKLADNI, ID_SPOJ, JEDE_SOBOTA, JEDE_NEDELE, JEDE_SVATEK, PRIJEZD_SPOJ, ODJEZD_SPOJ, POSUN_PRACOVNIK_RC, RIZENI_PRACOVNIK_RC)
          values (        0,               0,     999,           0,           0,           0,      '16:00',     '11:00',          500813051,           500521072);
SELECT  ID_VUZ FROM  VUZ 
MINUS  
SELECT  ID_VUZ FROM  
        VUZ V JOIN  RAZENI R ON  (V.ID_VUZ = R.VUZ_ID_VUZ) 
        JOIN  SPOJ S ON  (R.SPOJ_ID_SPOJ = S.ID_SPOJ) 
        JOIN  SPOJ X ON  (X.ID_SPOJ = 999) AND  (S.ODJEZD_SPOJ < X.PRIJEZD_SPOJ) AND  (S.PRIJEZD_SPOJ > X.ODJEZD_SPOJ) 
MINUS  
SELECT  ID_VUZ FROM  
        VUZ V JOIN  LOKOMOTIVA L ON  (V.ID_VUZ = L.VUZ_ID_VUZ) 
        JOIN  POHON P ON  (L.VUZ_ID_VUZ = P.LOKOMOTIVA_VUZ_ID_VUZ) 
        JOIN  SPOJ S ON  (P.SPOJ_ID_SPOJ = S.ID_SPOJ) 
        JOIN  SPOJ X ON  (X.ID_SPOJ = 999) AND  (S.ODJEZD_SPOJ < X.PRIJEZD_SPOJ) AND  (S.PRIJEZD_SPOJ > X.ODJEZD_SPOJ);
DELETE  FROM  SPOJ WHERE  ID_SPOJ = 999;

/* 09. Test na obsazenost kolejí stanice (ID = 9) pro požadovaný (10:00 - 14:00) časový interval (B) */
/* ZASTAVKA(STANICE_ID_STANICE = 9)(ODJEZD > "10:00")(PRIJEZD < "14:00")[KOLEJ] */
ALTER  session set NLS_DATE_FORMAT = 'hh24:mi';
SELECT  KOLEJ FROM  ZASTAVKA WHERE  (STANICE_ID_STANICE = 9) AND  (ODJEZD > '10:00') AND  (PRIJEZD < '14:00');

/* 10. Generické vlastnosti spoje (ID = 8) (AB) */
/* SPOJ(ID_SPOJ = X)[ZRYCHLENY, ID_SPOJ, JEDE_SOBOTA, JEDE_NEDELE, JEDE_SVATEK] */
SELECT  10 AS  DOTAZ#_1, ZRYCHLENY, ID_SPOJ, JEDE_SOBOTA, JEDE_NEDELE, JEDE_SVATEK FROM  SPOJ WHERE  ID_SPOJ = 8;

/* 11. Zastávky spoje (ID = 3) - nádraží, časy, koleje: (BR1) */
/* { SPOJ(SPOJ_ID_SPOJ = 8) [*] ZASTAVKA [*] STANICE }[NAZEV, MESTO, KOLEJ, NASTUPISTE, PRIJEZD, ODJEZD] */
SELECT  NAZEV, MESTO, KOLEJ, NASTUPISTE, PRIJEZD, ODJEZD FROM  
        SPOJ JOIN  ZASTAVKA ON  (ID_SPOJ = SPOJ_ID_SPOJ) 
        JOIN  STANICE ON  (STANICE_ID_STANICE = ID_STANICE) 
WHERE  ID_SPOJ = 3;
/* ev. efektivneji bez pouziti where (R2) */
SELECT  NAZEV, MESTO, KOLEJ, NASTUPISTE, PRIJEZD, ODJEZD FROM  
        SPOJ JOIN  ZASTAVKA ON  (ID_SPOJ = 3) AND  (ID_SPOJ = SPOJ_ID_SPOJ) 
        JOIN  STANICE ON  (STANICE_ID_STANICE = ID_STANICE);
/* ev. uzitim selectu z vice tabulek najedou bez join (R3)*/
SELECT  NAZEV, MESTO, KOLEJ, NASTUPISTE, PRIJEZD, ODJEZD FROM  
        SPOJ, ZASTAVKA, STANICE 
WHERE  
        (SPOJ.ID_SPOJ = 3) AND  (SPOJ.ID_SPOJ = ZASTAVKA.SPOJ_ID_SPOJ) 
        AND  (ZASTAVKA.STANICE_ID_STANICE = STANICE.ID_STANICE);

/* 12. Řazení vagonů spoje (ID = 1) a jejich vlastnosti: (B) */
/* { RAZENI(SPOJ_ID_SPOJ = 1) [*] VAGON [*] TYP_VAGON [*] TYP_VUZ }[PORADI, KURACKY, KAPACITA, DRUH, HMOTNOST] */
SELECT  PORADI, KURACKY, KAPACITA, DRUH, HMOTNOST FROM  
        RAZENI R JOIN  VAGON V ON  (R.VUZ_ID_VUZ = V.VUZ_ID_VUZ) 
        JOIN  TYP_VAGON ON  (TYP_V_TYP_VUZ_ID_TYP_VUZ = TYP_VUZ_ID_TYP_VUZ) 
        JOIN  TYP_VUZ ON  (TYP_VUZ_ID_TYP_VUZ = ID_TYP_VUZ) 
WHERE  SPOJ_ID_SPOJ = 1 
ORDER  BY  PORADI;

/* 13. Strojvůdce (Rumcajs) - dotaz na jím řízené spoje: (AB) */
/* { SPOJ [RIZENI_PRACOVNIK_RC = RC] PRACOVNIK(JMENO = "Rumcajs") }[SPOJ_ID, ODJEZD_SPOJ, PRIJEZD_SPOJ] */
SELECT  ID_SPOJ, ODJEZD_SPOJ, PRIJEZD_SPOJ FROM  
        SPOJ JOIN  PRACOVNIK ON  (RIZENI_PRACOVNIK_RC = RC)
WHERE  JMENO = 'Rumcajs';

/* 14. Posunovač (Marsal Russworm) - dotaz na jím posunované spoje: (AB) */
/* { SPOJ [POSUN_PRACOVNIK_RC = RC] PRACOVNIK(JMENO = "Maxipes Fik") }[SPOJ_ID, ODJEZD_SPOJ, PRIJEZD_SPOJ] */
SELECT  ID_SPOJ, ODJEZD_SPOJ, PRIJEZD_SPOJ FROM  
        SPOJ JOIN  PRACOVNIK ON  (POSUN_PRACOVNIK_RC = RC)
WHERE  JMENO = 'Marsal Russworm';

/* 15. Výhybkář (Rakosnicek) - dotaz na jím obsluhované výhybky: (B) */
/* { { PRACOVNIK(JMENO = "Rakosnicek") [*] ZAMESTNANI [*] PRACOVISTE } [ID_PRACOVISTE = UMISTENI_V_ID_PRACOVISTE] VYHYBKA }[atributy Vyhybka] */
SELECT  15 AS  DOTAZ#_1, VYHYBKA.* FROM
        (SELECT  * FROM  PRACOVNIK WHERE  JMENO = 'Rakosnicek')
        JOIN  ZAMESTNANI ON  (RC = PRACOVNIK_RC)
        JOIN  PRACOVISTE ON  (PRACOVISTE_ID_PRACOVISTE = ID_PRACOVISTE)
        JOIN  VYHYBKA ON  (ID_PRACOVISTE = UMISTENI_V_ID_PRACOVISTE);

/* 16. Kalkulace ceny - zjištění kilometrické vzdálenosi cesty (mezi stanicemi Praha a Plzen): (AF) */
/* { STANICE(NAZEV = X) [UMISTENI_S_ID_LOKACE = ZACATEK_ID_LOKACE] TRASA [KONEC_ID_LOKACE = UMISTENI_S_ID_LOKACE] STANICE(NAZEV = Y) }[NAZEV][KILOMETRY] */
SELECT  A.NAZEV, B.NAZEV, KILOMETRY FROM  
        STANICE A JOIN  TRASA ON  (A.UMISTENI_S_ID_LOKACE = ZACATEK_ID_LOKACE)
        JOIN  STANICE B ON  (KONEC_ID_LOKACE = B.UMISTENI_S_ID_LOKACE)
WHERE  (A.NAZEV like '%Praha%') AND  (B.NAZEV like '%Plzen%');

/* 17. Vytipovaní vozů zralých na servisní prohlidku (poslední servis před 1.1.2000), které zároveň využívají lokální servis ve své domovské stanici (ID = 1): (AB) */
/* VUZ(POSLEDNI_SERVIS < "01.01.2000", DOMA_LOKALNI_SERVIS = true, DOMOV_ID_STANICE = 1) */
ALTER  session set NLS_DATE_FORMAT = 'DD.MM.YYYY';
SELECT  * FROM  VUZ WHERE  (POSLEDNI_SERVIS < '01.01.2004') AND  (DOMA_LOKALNI_SERVIS = 1) AND  (DOMOV_ID_STANICE = 1);

/* 18. Vytipovaní pracovniků (servisních techniků), které má Vedoucí servisu na svém nádraží (ID = 1) k dispozici: (B) */
/* { OBECNY(SKUPINA_Z_POPIS = "SERVISNI TECHNICI") [*] ZAMESTNANI(PRACOVISTE_ID_PRACOVISTE = 1) }[PRACOVNIK_RC] */
SELECT  Distinct  PRACOVNIK_RC FROM  OBECNY NATURAL  JOIN  ZAMESTNANI 
WHERE  (SKUPINA_Z_POPIS = 'SERVISNI TECHNICI' AND  PRACOVISTE_ID_PRACOVISTE = 1);

/* 19. jako 18, ale s doplnenim jmen pracovniku (B) */
SELECT  Distinct  JMENO, PRACOVNIK_RC FROM  
        PRACOVNIK JOIN  OBECNY ON  (RC = PRACOVNIK_RC) 
        NATURAL  JOIN  ZAMESTNANI 
WHERE  (SKUPINA_Z_POPIS = 'SERVISNI TECHNICI' AND  PRACOVISTE_ID_PRACOVISTE = 1);

/* 20. Jízdní řád pro stanici pracoviště Výpravčího/Hlasatele spojů (NAZEV = Dobrany): (BJ) */
/* { STANICE(NAZEV = "Dobrany") [*] ZASTAVKA [*] SPOJ }[KOLEJ, NASTUPISTE, ZASTAVKA.PRIJEZD, ZASTAVKA.ODJEZD, OSOBNI_NAKLADNI] */
SELECT  20 AS  DOTAZ#_1, KOLEJ, NASTUPISTE, ZASTAVKA.PRIJEZD, ZASTAVKA.ODJEZD, OSOBNI_NAKLADNI FROM
        (SELECT  ID_STANICE FROM  STANICE WHERE  NAZEV = 'Dobrany')
        JOIN  ZASTAVKA ON  (ID_STANICE = STANICE_ID_STANICE)
        JOIN  SPOJ ON  (SPOJ_ID_SPOJ = ID_SPOJ);

/* 21. Pracovník PPS - Dotaz na směrovku k nalezení jemu (se jmenem "Rakosnicek") přiděleného pracoviště: (B) */
/* { PRACOVNIK(JMENO = X) [*] ZAMESTNANI [*] PRACOVISTE }[PRACOVISTE_ID_PRACOVISTE, SMEROVKA] */
SELECT  Distinct  PRACOVISTE_ID_PRACOVISTE, SMEROVKA FROM  
        PRACOVNIK JOIN  ZAMESTNANI ON  (RC = PRACOVNIK_RC) 
        JOIN  PRACOVISTE ON  (PRACOVISTE_ID_PRACOVISTE = ID_PRACOVISTE) 
WHERE  JMENO = 'Rakosnicek';

/* 22. Pracovník PPS - Dotaz na pracovní dobu pro jemu (se jmenem "Rakosnicek") přidělené pracoviště: (B) */
/* { PRACOVNIK(JMENO = X) [*] ZAMESTNANI [*] SMENA }[PRACOVISTE_ID_PRACOVISTE, OD, DO] */
ALTER  session set NLS_DATE_FORMAT = 'hh24:mi';
SELECT  Distinct  PRACOVISTE_ID_PRACOVISTE, OD, DO FROM  
        PRACOVNIK JOIN  ZAMESTNANI ON  (RC = PRACOVNIK_RC) 
        JOIN  SMENA ON  (SMENA_CISLO_SMENY = CISLO_SMENY) 
WHERE  JMENO = 'Rakosnicek';


/* CAST 2 - dalsi SQL dotazy vychazejici z potreb Spravy zeleznice: */
/********************************************************************/

/* 01. vsechny vozy, ktere patri stanicim v Plzni: (AB) */
ALTER  session set NLS_DATE_FORMAT = 'DD.MM.YYYY';
SELECT  1 AS  DOTAZ#_2, STANICE.NAZEV, VUZ.* FROM  
        VUZ JOIN  STANICE ON  (VUZ.DOMOV_ID_STANICE = STANICE.ID_STANICE) 
WHERE  STANICE.NAZEV like '%Plzen%';

/* 02. vsechny lokomotivy, ktere patri stanicim v Praze: (B) */
ALTER  session set NLS_DATE_FORMAT = 'DD.MM.YYYY';
SELECT  STANICE.NAZEV, VUZ.*, LOKOMOTIVA.* FROM  
        LOKOMOTIVA JOIN  VUZ ON  (LOKOMOTIVA.VUZ_ID_VUZ = VUZ.ID_VUZ) 
        JOIN  STANICE ON  (VUZ.DOMOV_ID_STANICE = STANICE.ID_STANICE) 
WHERE  NAZEV like '%Praha%';

/* 03. vsechny stanice, ke kterym patri vice nez 3 vozy: (BGHJP) */
SELECT  ID_STANICE, NAZEV FROM  
        STANICE S1 
WHERE  (SELECT  COUNT (*) FROM  VUZ 
        JOIN  STANICE S2 ON  (DOMOV_ID_STANICE = S2.ID_STANICE) 
        WHERE  S1.ID_STANICE = S2.ID_STANICE) > 3;

/* 04. jako 03, ale vcetne informaci o jednotlivych vozech: (BGHJP) */
ALTER  session set NLS_DATE_FORMAT = 'DD.MM.YYYY';
SELECT  * FROM  
        (SELECT  ID_STANICE, NAZEV FROM  
                STANICE S1 
        WHERE  (SELECT  COUNT (*) FROM  
                VUZ JOIN  STANICE S2 ON  (DOMOV_ID_STANICE = S2.ID_STANICE) 
                WHERE  S1.ID_STANICE = S2.ID_STANICE) > 3) 
JOIN  VUZ ON  (ID_STANICE = DOMOV_ID_STANICE);

/* 05. jizdni rad pro spoj s id = 4: (B) */
ALTER  session set NLS_DATE_FORMAT = 'hh24:mi';
SELECT  5 AS  DOTAZ#_2, MESTO, NAZEV, KOLEJ, PRIJEZD, ODJEZD, (ODJEZD-PRIJEZD)*1440 AS  STANI, ID_SPOJ FROM  
        SPOJ JOIN  ZASTAVKA ON  (ID_SPOJ = SPOJ_ID_SPOJ) 
        JOIN  STANICE ON  (ID_STANICE = STANICE_ID_STANICE) 
WHERE  ID_SPOJ = 2;

/* 06. jizdni rad s dobou zastavek pro stanici s nazvem = "Praha-Smichov": (B) */
ALTER  session set NLS_DATE_FORMAT = 'hh24:mi';
SELECT  MESTO, NAZEV, KOLEJ, PRIJEZD, ODJEZD, (ODJEZD-PRIJEZD)*1440 AS  STANI, ID_SPOJ FROM  
        SPOJ JOIN  ZASTAVKA ON  (ID_SPOJ = SPOJ_ID_SPOJ) 
        JOIN  STANICE ON  (ID_STANICE = STANICE_ID_STANICE) 
WHERE  NAZEV = 'Praha-Smichov';

/* 07. prijezdy a odjezdy strojvudcu ve stanici "Praha-Smichov" a "Praha-Stred": (B) */
SELECT  JMENO, PRIJEZD, ODJEZD, NAZEV, ID_SPOJ FROM  
        PRACOVNIK JOIN  STROJVUDCE ON  (RC = PRACOVNIK_RC) 
        JOIN  SPOJ ON  (RC = RIZENI_PRACOVNIK_RC) 
        JOIN  ZASTAVKA ON  (ID_SPOJ = SPOJ_ID_SPOJ) 
        JOIN  STANICE ON  (ID_STANICE = STANICE_ID_STANICE) 
WHERE  STANICE.NAZEV like '%Praha-S%' ORDER  BY  ZASTAVKA.PRIJEZD;

/* 08. seznam spoju, ktere se mohou potkat na zastavce: (B) */
SELECT  Z1.SPOJ_ID_SPOJ AS  SPOJ1, Z2.SPOJ_ID_SPOJ AS  SPOJ2 FROM  
        ZASTAVKA Z1 JOIN  ZASTAVKA Z2 ON  (Z1.STANICE_ID_STANICE = Z2.STANICE_ID_STANICE) 
WHERE  (Z1.SPOJ_ID_SPOJ < Z2.SPOJ_ID_SPOJ) AND  (Z1.ODJEZD > Z2.PRIJEZD) AND  (Z2.ODJEZD > Z1.PRIJEZD);

/* 09. jako predchozi, ale s informacemi o jejich ridicich a nazvech stanic: (B) */
SELECT  ST.NAZEV AS  NADRAZI, SP1.ID_SPOJ AS  SPOJ1, Z1.PRIJEZD AS  PRIJEZD1, Z1.ODJEZD AS  ODJEZD1, Z1.KOLEJ AS  KOLEJ1, PR1.JMENO AS  RIDIC1, 
        SP2.ID_SPOJ AS  SPOJ2, Z2.PRIJEZD AS  PRIJEZD2, Z2.ODJEZD AS  ODJEZD2, Z2.KOLEJ AS  KOLEJ2, PR2.JMENO AS  RIDIC2 FROM  
        ZASTAVKA Z1 JOIN  ZASTAVKA Z2 ON  (Z1.STANICE_ID_STANICE = Z2.STANICE_ID_STANICE) 
        JOIN  STANICE ST ON  (ST.ID_STANICE = Z1.STANICE_ID_STANICE ) 
        JOIN  SPOJ SP1 ON  (SP1.ID_SPOJ = Z1.SPOJ_ID_SPOJ) 
        JOIN  PRACOVNIK PR1 ON  (SP1.RIZENI_PRACOVNIK_RC = PR1.RC) 
        JOIN  SPOJ SP2 ON  (SP2.ID_SPOJ = Z2.SPOJ_ID_SPOJ) 
        JOIN  PRACOVNIK PR2 ON  (SP2.RIZENI_PRACOVNIK_RC = PR2.RC) 
WHERE  (Z1.SPOJ_ID_SPOJ != Z2.SPOJ_ID_SPOJ) AND  (Z1.ODJEZD > Z2.PRIJEZD) AND  (Z1.PRIJEZD < Z2.ODJEZD);

/* 10. seznam obsazeni variabilnich pracovist pracovniky: (A) */
SELECT  10 AS  DOTAZ#_2, NAZEV, SMEROVKA, JMENO, OD, DO FROM  
        ZAMESTNANI JOIN  PRACOVNIK ON  (RC = PRACOVNIK_RC) 
        JOIN  PRACOVISTE ON  (ID_PRACOVISTE = PRACOVISTE_ID_PRACOVISTE) 
        JOIN  STANICE ON  (ID_STANICE = UMISTENI_P_ID_STANICE) 
        JOIN  SMENA ON  (SMENA_CISLO_SMENY = CISLO_SMENY);

/* 11. vypis strojvudcu a jejich opravneni k rizeni typu lokomotiv: (A) */
SELECT  RIZENI_PRACOVNIK_RC, ID_TYP_VUZ FROM  
        SPOJ JOIN  POHON ON  (ID_SPOJ = SPOJ_ID_SPOJ) 
        JOIN  VUZ ON  (LOKOMOTIVA_VUZ_ID_VUZ = ID_VUZ) 
        JOIN  LOKOMOTIVA ON  (ID_VUZ = VUZ_ID_VUZ) 
        JOIN  TYP_VUZ ON  (TYP_L_TYP_VUZ_ID_TYP_VUZ = ID_TYP_VUZ);

/* 12. vypis strojvudcu, ktere neridi typy lokomotiv, ktere by ale dle pridelenych opravneni ridit mohli: (CN) */
SELECT  * FROM  OPRAVNENI 
MINUS
SELECT  ID_TYP_VUZ AS  K_RIZENI_TYP_VUZ_ID_TYP_VUZ, RIZENI_PRACOVNIK_RC AS  OPRAVNEN_PRACOVNIK_RC FROM  
        SPOJ JOIN  POHON ON  (ID_SPOJ = SPOJ_ID_SPOJ) 
        JOIN  VUZ ON  (LOKOMOTIVA_VUZ_ID_VUZ = ID_VUZ) 
        JOIN  LOKOMOTIVA ON  (ID_VUZ = VUZ_ID_VUZ) 
        JOIN  TYP_VUZ ON  (TYP_L_TYP_VUZ_ID_TYP_VUZ = ID_TYP_VUZ);

/* 13. vypis vyhybek, jejich parametru a nadrazi ktere spojuji: (A) */
SELECT  S0.NAZEV AS  SMER_Z, S1.NAZEV AS  SMER_1, S2.NAZEV AS  SMER_2, MECHANICKA, SMEROVKA FROM  
        VYHYBKA JOIN  PRACOVISTE ON  (UMISTENI_V_ID_PRACOVISTE = ID_PRACOVISTE) 
        JOIN  STANICE S0 ON  (UMISTENI_P_ID_STANICE = S0.ID_STANICE) 
        JOIN  STANICE S1 ON  (SMER_1_ID_STANICE = S1.ID_STANICE) 
        JOIN  STANICE S2 ON  (SMER_2_ID_STANICE = S2.ID_STANICE);

/* 14. prodane jizdenky a jmeno pokladniho, ktery je prodal: (A) */
SELECT  PRACOVNIK.JMENO AS  PRODEJCE, X.NAZEV AS  Z, Y.NAZEV AS  DO, DETSKA, OSOBY, ZPATECNI, CENA FROM  
        JIZDENKA JOIN  STANICE X ON  (JIZDENKA.START_ID_STANICE = X.ID_STANICE) 
        JOIN  STANICE Y ON  (JIZDENKA.CIL_ID_STANICE = Y.ID_STANICE) 
        JOIN  PRACOVNIK ON  (JIZDENKA.VYDEJ_KYM_PRACOVNIK_RC = PRACOVNIK.RC) 
ORDER  BY  CENA;

/* CAST 3 - SQL dotazy vybrane tak, aby spolu s predchozimi pokryly pozadovane kategorie (viz Pozadavky na semestralni praci) */
/******************************************************************************************************************************/

/* 01. vyber vsechny vagony, ktere nejsou zapojeny v zadnem z pravidelnych spoju: (CN) */
SELECT  1 AS  DOTAZ#_3, VAGON.* FROM  VAGON 
MINUS  
SELECT  1 AS  DOTAZ#_3, VAGON.* FROM  VAGON JOIN  RAZENI ON  (VAGON.VUZ_ID_VUZ = RAZENI.VUZ_ID_VUZ);

/* 02. vyber vsechny spoje (jejich ID), jejichz vsechny vagony vyuzivaji lokalni servis: (DN) */
SELECT  Distinct  ID_SPOJ FROM  SPOJ 
MINUS  
SELECT  SPOJ_ID_SPOJ AS  ID_SPOJ FROM  
        VUZ JOIN  RAZENI ON  (ID_VUZ = VUZ_ID_VUZ) 
WHERE  VUZ.DOMA_LOKALNI_SERVIS = 0;

/* 03. vyber vsechny stanice, jejichz vsechny vyhybky (ex. pracoviste na teto stanici, jejich pocet vyhybek > 0) jsou mechanicke: (DN) */
SELECT  STANICE.* FROM  
        STANICE JOIN  PRACOVISTE ON  (UMISTENI_P_ID_STANICE = ID_STANICE)
        JOIN  VYHYBKA ON  (UMISTENI_V_ID_PRACOVISTE = ID_PRACOVISTE)
MINUS  
SELECT  STANICE.* FROM  
        (SELECT  UMISTENI_V_ID_PRACOVISTE FROM  VYHYBKA WHERE  MECHANICKA = 0) 
        JOIN  PRACOVISTE ON  (UMISTENI_V_ID_PRACOVISTE = ID_PRACOVISTE) 
        JOIN  STANICE ON  (UMISTENI_P_ID_STANICE = ID_STANICE);

/* 04. prumerny pocet vagonu spoje: (BGJPQ) */
SELECT  AVG (CNT) FROM  
        (SELECT  SPOJ_ID_SPOJ, COUNT (SPOJ_ID_SPOJ) AS  CNT FROM  
                RAZENI
        GROUP  BY  SPOJ_ID_SPOJ);

/* 05. vyber vsechny elektrifikovane stanice, pro nez zadny projizdejici spoj nema nadprumerny pocet vagonu: (EGKNPQS) */
SELECT  5 AS  DOTAZ#_3, STANICE.* FROM  STANICE 
WHERE  ELEKTRIFIKACE = 1 
MINUS  
SELECT  5 AS  DOTAZ#_3, STANICE.* FROM  
        (SELECT  SPOJ_ID_SPOJ FROM  RAZENI 
        GROUP  BY  SPOJ_ID_SPOJ 
        HAVING  ( COUNT (1) > (SELECT  AVG (CNT) FROM  
                        (SELECT  SPOJ_ID_SPOJ, COUNT (SPOJ_ID_SPOJ) AS  CNT FROM  
                                RAZENI GROUP  BY  SPOJ_ID_SPOJ)
                        ))
        )
        NATURAL  JOIN  ZASTAVKA 
        JOIN  STANICE ON  (STANICE_ID_STANICE = ID_STANICE);

/* 06. pocet jizdenek vydanych jednotlivymi stanicemi: (GJLPQ) */
SELECT  NAZEV, coalesce(CNT, 0) AS  POCET_VYDANYCH FROM  
        (SELECT  NAZEV, CNT FROM  
                STANICE, 
                (SELECT  VYDEJ_KDE_ID_STANICE, COUNT (*) AS  CNT FROM  
                        JIZDENKA 
                GROUP  BY  VYDEJ_KDE_ID_STANICE) 
        WHERE  STANICE.ID_STANICE = VYDEJ_KDE_ID_STANICE (+))
ORDER  BY  POCET_VYDANYCH DESC ;

/* 07. spoje jedouci v sobotu nebo v nedeli: (M) */
SELECT  * FROM  SPOJ WHERE  JEDE_SOBOTA = 1
UNION
SELECT  * FROM  SPOJ WHERE  JEDE_NEDELE = 1;

/* 08. stanice, ktere  jsou elektrifikovane a zaroven maji nadrazni halu (O) */
SELECT  * FROM  STANICE WHERE  ELEKTRIFIKACE = 1
intersect
SELECT  * FROM  STANICE WHERE  HALA = 1;


/* CAST 4 - statistika obsazeni kategorii dotazu (viz Pozadavky na semestralni praci) */
/**************************************************************************************/
/*
 typ        vyskyty
-------------------------
 A:        iiiiiiiiii+ (10+)
 B:        iiiiiiiiii+ (10+)
 C:        iii
 D:        ii
 E:        ii
 F:        cross-join, natural-join, join-on, outer-join (left)
 G:        iiiiiiiiii (10)
 H:        ii
 I:        ii
 J:        iiiiiiii (8)
 K:        iii
 L:        i
 M:        i
 N:        iiiiii (6)
 O:        i
 P:        iiiiiiiii (9)
 Q:        iii
 R:        i
 S:        i
------------------------
celkem dotazu: 14+22+8 = 44
*/