-- 1 
--Jmena zelenych lodi. 
-- (Co kdyz se dve zelene lodi jmenuji stejne?)
-- LOD(BARVA = 'zelena')[JMENOL]
SELECT JMENOL
FROM LOD
WHERE BARVA = 'zelena';

-- 2 
-- Typy modrych lodi 
-- LOD(BARVA = 'modra')[LTYP]
SELECT LTYP
FROM LOD
WHERE BARVA = 'modra';

-- jsou tyto zapisy ekvivalentni (co kdyz je jednoho typu vice modrych lodi)
SELECT UNIQUE LTYP
FROM LOD
WHERE BARVA = 'modra';

-- 3 
-- Jmena zakazniku, kteri si rezervovali alespon 
-- jednu ruzovou lod typu 'parnik'
-- {ZAKAZNIK * REZERVACE * LOD}(LTYP = 'parnik')[JMENOZ]
SELECT ZAKAZNIK.JMENOZ 
FROM ZAKAZNIK JOIN REZERVACE USING(ZID)
     JOIN LOD USING(LODID)
WHERE LOD.LTYP = 'parnik' AND
      LOD.barva= 'ruzova';
-- jsou zapisy  v RA a SQL ekvivalentni?
SELECT UNIQUE Z.JMENOZ 
FROM ZAKAZNIK Z JOIN REZERVACE R USING(ZID)
     JOIN LOD L USING(LODID)
WHERE L.LTYP = 'parnik'AND
      LOD.barva= 'ruzova';-- Typy lodi, ktere byly na plavbe s pruvodcem.
--
-- {LOD [LODID=LODID] PLAVBA_S_PRUVODCEM }[LTYP]
SELECT UNIQUE LTYP 
FROM LOD L JOIN PLAVBA_S_PRUVODCEM PSP ON(L.LODID= PSP.LODID);
--
-- LOD <* PLAVBA_S_PRUVODCEM
--
SELECT UNIQUE LTYP 
FROM LOD L
WHERE EXISTS (SELECT 1 
              FROM PLAVBA_S_PRUVODCEM P
              WHERE P.LODID = L.LODID);
-- varinta 2
SELECT UNIQUE LTYP 
FROM LOD L
WHERE LODID IN (SELECT LODID 
                FROM PLAVBA_S_PRUVODCEM P);

-- 4
--Lode (vsechny atributy), ktere pokryly nejaky pravidelny 
-- spoj a zaroven byly na plavbe s pruvodcem.
-- {LOD[LODID=LODID]POKRYTI}[LOD.LODID,JMENOL,LTYP,BARVA,POCET_MIST]
-- "prunik"
-- {LOD [LODID=LODID] PLAVBA_S_PRUVODCEM}[LOD.LODID,JMENOL,LTYP,BARVA,POCET_MIST]
SELECT L.* 
FROM LOD L JOIN POKRYTI P ON (L.LODID = P.LODID)
INTERSECT
SELECT L.* 
FROM LOD L JOIN PLAVBA_S_PRUVODCEM P
ON (L.LODID = P.LODID);
-- LOD <* POKRYTI
-- "prunik"
-- LOD <* PLAVBA_S_PRUVODCEM
SELECT L.* 
FROM LOD L 
WHERE L.LODID IN (SELECT LODID
                  FROM POKRYTI P)
INTERSECT
SELECT L.* 
FROM LOD L 
WHERE EXISTS (SELECT 1 FROM PLAVBA_S_PRUVODCEM
              WHERE PLAVBA_S_PRUVODCEM.LODID = L.LODID);

-- 5
-- Lode (vaechny atributy), ktere pokryly nejaky pravidelny 
-- spoj a nebo byly na plavbe s pruvodcem.
--Lode (vaechny atributy), ktere pokryly nejaky pravidelny 
-- spoj a zaroven byly na plavbe s pruvodcem.
-- {LOD [L.LODID = P.LODID] POKRYTI}[LOD.LODID,JMENOL,LTYP,BARVA] 
-- "sjednoceni"
-- {LOD [L.LODID = P.LODID] PLAVBA_S_PRUVODCEM}[LOD.LODID,JMENOL,LTYP,BARVA] 
SELECT L.* 
FROM LOD L JOIN POKRYTI P ON (L.LODID = P.LODID)
UNION
SELECT L.* 
FROM LOD L JOIN PLAVBA_S_PRUVODCEM P ON (L.LODID = P.LODID);
--
-- {LOD <* POKRYTI} "sjednoceni" {LOD <* PLAVBA_S_PRUVODCEM}
--
SELECT L.* 
FROM LOD L 
WHERE L.LODID IN (SELECT LODID 
                  FROM POKRYTI P)
UNION
SELECT L.* 
FROM LOD L 
WHERE EXISTS (SELECT 1 FROM PLAVBA_S_PRUVODCEM PSP
              WHERE PSP.LODID = L.LODID);


-- 6
-- Lode (vaechny atributy), ktere nebyly rezervovany.
--
-- LOD "levy prirozeny antijoin" REZERVACE
--
SELECT * 
FROM LOD L
WHERE L.LODID NOT IN (SELECT LODID
                      FROM REZERVACE);
--
SELECT * 
FROM LOD L
WHERE NOT EXISTS (SELECT LODID
                  FROM REZERVACE R
                  WHERE R.LODID = L.LODID);
-- LOD "mnozinovy rozdil" LOD <* REZERVACE
Select * from LOD
MINUS
SELECT * 
FROM LOD L
WHERE L.LODID IN (SELECT LODID
                  FROM REZERVACE);

-- 7
/* Typy lodi, ktere pokryvaji pouze pravidelne spoje
{LOD* POKRYTI} [LTYP]
\
{LOD <*PLAVBA_S_PRUVODCEM }[LTYP]
*/
Select LTYP
FROM LOD L JOIN POKRYTI USING (LODID)
MINUS
Select LTYP
FROM LOD L JOIN PLAVBA_S_PRUVODCEM  USING (LODID)
MINUS 
Select LTYP
FROM LOD L JOIN REZERVACE USING(LODID);

-- v SQL muzeme pouzit vnoreny dotaz:
-- !!! POZOR, protoze vnoreny select vraci v mnozine hodnot take NULL
-- bude vysledek vnejsiho selectu prazdna mnozina (spatne)
-- pokud si to neohlidame "where ltyp is not null"
-- tohle je casty problem z praxe
-- je to v souladu s definovanym chovanim NOT IN
select distinct ltyp
from pokryti p join lod l on (p.lid = l.lodid)
where ltyp not in
(select distinct ltyp
from plavba_s_pruvodcem p join lod l on (p.lodid = l.lodid)
where ltyp is not null);

-- v tomto kontextu je bezpecnejsi pouzit namisto NOT IN
-- klauzuli NOT EXiSTS

select distinct l1.ltyp
from pokryti p1 join lod l1 on (p1.lid = l1.lodid)
where not exists
(select 'X'
from plavba_s_pruvodcem p2 join lod l2 on (p2.lodid = l2.lodid)
where l1.ltyp=l2.ltyp);


-- 8
-- Zakazniky (id_z, jmeno), kteri si rezervovaly kazdou zelenou lod.
-- REZERVACE[ZID,LODID] DIV LOD(BARVA='Zelena'[LODID]
--
-- T1:= LOD(BARVA='zelena')[LODID] x ZAKAZNIK[ZID] ... teoreticke rezervace
-- T2:= REZERVACE[LODID,ZID] ... uskutecnene rezervace
-- T3:= {T1\T2}[ZID] ... zakaznici, kteri si nerezervovali nekterou zelenou lod
-- T4:= REZERVACE[ZID]... zakaznici, kteri neco rezervovali
-- T5:= T4 <levy antijoin> T3 ... zakaznici, kteri rezervovali vsechny zelene lodi
WITH 
T1  AS (SELECT LODID,ZID
        FROM LOD CROSS JOIN ZAKAZNIK
        WHERE BARVA = 'zelena' ),
T2  AS (SELECT UNIQUE LODID,ZID FROM REZERVACE),
T31 AS (SELECT * FROM  T1 MINUS SELECT * FROM T2),
T32 AS (SELECT UNIQUE ZID FROM T31),
T4  AS (SELECT UNIQUE ZID FROM REZERVACE),
T5  AS (SELECT * From T4 Where T4.ZID Not In (Select ZID From T32)) 
Select *
From T5 Join zakaznik Using(ZID);

-- SQL umoznuje i toto reseni:
select *
from zakaznik Z
where (Select count(DISTINCT LODID) 
       From REZERVACE R Join lod using(lodid)
       Where R.ZID = Z.ZID and barva='zelena')
       =
      (Select count(*)
       From LOD
       where barva='zelena');

-- 9
-- Dvojice jmen zakazniku, kteri bydli na stejne adrese
-- {{ZAKAZNIK[ADRESA -> ADR1,ZID-> ZID1,JMENOZ->JMENO1]}
--    [ADR1=ADR2 & ZID1 < ZID2] 
--    {ZAKAZNIK[ADRESA -> ADR2,ZID-> ZID2,JMENOZ->JMENO2]}
--   }[ZID1,JM1,ADR1,ZID2,JM2,ADR2]
--
Select Z1.zid,Z1.jmenoz,Z2.zid,Z2.jmenoz
From zakaznik Z1 Join zakaznik Z2 On (Z1.adresa=Z2.adresa and
                                      Z1.zid < Z2.zid);

-- 10
-- Prumerny vek lodniku.
--
Select AVG(vek)
From namornik;
--
Select ROUND(AVG(vek),2)
From namornik;


-- 11
-- Seznam id namorniku a pocet jejich rezervaci  
--
Select nid, count(*) pocet_rezervaci 
From rezervace
Group By nid;


-- 12
-- Lodnici, kteri byli na plavbe s pruvodcem mene nez 4x
--
set echo on
Select *
From namornik N
Where (Select count(*) 
       From PLAVBA_S_PRUVODCEM PSP
       Where N.nid = PSP.nid) < 4;
-- alternativni reaeni
Select nid,N.JMENON,N.VEK,N.HODNOST,N.PLAT
From namornik N Left Outer join PLAVBA_S_PRUVODCEM P
     Using(nid)
GROUP BY nid, N.JMENON, N.VEK, N.HODNOST, N.PLAT
Having  count(LODID) < 4;


-- 13
-- Lodnici, kteri pokryli mene nez 3 ruzne pravidelne linky
-- (vcetne tech, kteri nepokryli zadnou).
Select N.*,
       (Select count(DISTINCT LID) 
        From pokryti P
        Where P.nid = N.nid) pocet_ruznych_linek
From namornik N
Where (Select count(DISTINCT LID) 
       From pokryti P
       Where P.nid = N.nid) < 3;
-- alternativni reseni
SELECT NID,N.JMENON,N.VEK,N.HODNOST,N.PLAT,COUNT(LODID)
FROM NAMORNIK N Left Outer Join POKRYTI P Using (Nid)
GROUP BY NID,N.JMENON,N.VEK,N.HODNOST,N.PLAT
--Having count (LODID) < 3;

-- 14
---- Pro kazdeho lodnika vypiste pocet jeho plaveb s pruvodcem
SET AUTOTRACE on
Select N.*,
       (Select count(*) 
       From PLAVBA_S_PRUVODCEM PSP
       Where N.nid = PSP.nid) pocet_PSP
From namornik N;
-- alternativa
Select nid,N.JMENON,N.VEK,N.HODNOST,N.PLAT,
       count(LODID) pocet_PSP
From namornik N Left Outer join PLAVBA_S_PRUVODCEM P
     Using(nid)
GROUP BY nid, N.JMENON, N.VEK, N.HODNOST, N.PLAT;


--15
-- Jmena lodniku mladaich 40 let, kteri maji za sebou 
-- alespon 3 plavby s pruvodcem na lodi typu kliper. 
-- Vystup radte sestupne dle jmen namorniku.
Select N.nid,N.JMENON
From Namornik N 
Where (Select count(*) 
       From PLAVBA_S_PRUVODCEM PSP Join LOD L Using(LODID)
       Where PSP.nid = N.nid and L.Ltyp ='klipr') >=3      and
       N.vek <40;
-- alternativa
Select nid,N.JMENON
From Namornik N Join PLAVBA_S_PRUVODCEM PSP Using (nid)
     Join lod L Using (lodid)
Where L.Ltyp ='klipr' and N.vek <40
Group By nid,N.JMENON
having count(*) >= 3;

-- 16
-- Namornikum, kteri maji za sebou alespon 4 pokryti 
-- zvednete plat o 15%.
Update namornik N
Set plat = plat*1.15
where (Select count (*)
       From pokryti P
       Where P.nid = N.nid) > 4;
Commit;

-- 17
-- Pridejte k lodnikum sloupec pocet_plaveb_s_pruvodcem 
-- a provedte jednorazovy dopocet hodnot do tohoto sloupce
Alter Table NAMORNIK
Add (pocet_plaveb integer Default 0);
--
Update namornik N
Set pocet_plaveb = (Select count(*)
                    From PLAVBA_S_PRUVODCEM PSP
                    Where PSP.nid=N.nid);
Commit;