BI-DBS - středa 14:30, lichý týden, paraleka 1 - Michal Valenta - Výstup SQL příkazů> SQL>
SQL>
SQL> --KOD DOTAZU: d1
SQL> -- Jmena zelenych lodi.
SQL>
SQL> SELECT DISTINCT JMENOL
2 FROM LOD
3 WHERE BARVA = 'zelená';

JMENOL
Anne
Carmen
LodProPriklad7.2

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d2
SQL> -- Jména zákazníků, kteří si rezervovali alespoň jeden modrý parník.
SQL>
SQL> SELECT ZAKAZNIK.JMENOZ, zakaznik.prijmeni
2 FROM ZAKAZNIK JOIN REZERVACE USING(ZID)
3 JOIN LOD USING(LODID)
4 WHERE LOD.LTYP = 'parník' AND
5 LOD.barva= 'modrá';

JMENOZ PRIJMENI
Ford Ford
Ford Ford
Martin Martin

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d3
SQL> -- Seznam typů lodí, které byly na plavbě s průvodcem.
SQL>
SQL> SELECT UNIQUE LTYP
2 FROM LOD L JOIN PLAVBA_S_PRUVODCEM PSP ON(L.LODID= PSP.ID_LOD);

LTYP
 
parník
klipr
ponorka

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d4
SQL> -- Lodě (všechny atributy), které pokryly nějaké pravidelné spoje a byly také na některé plavbě s průvodcem.
SQL>
SQL> SELECT L.*
2 FROM LOD L
3 WHERE L.LODID IN (SELECT LODID
4 FROM POKRYTI P)
5 INTERSECT
6 SELECT L.*
7 FROM LOD L
8 WHERE EXISTS (SELECT 1 FROM PLAVBA_S_PRUVODCEM
9 WHERE PLAVBA_S_PRUVODCEM.ID_LOD = L.LODID);

LODID JMENOL LTYP BARVA POCET_MIST ID_ZAZEMI
1 Chloe ponorka žlutá 100 599
2 Mariane klipr modrá 50 699
6 Carmen klipr zelená 100 699
8 Charibda parník modrá 145 899
9 Anne   zelená 100 899
10 Lilien klipr modrá 10 699

6 rows selected.

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d5
SQL> -- Lodě, (všechny atributy), které pokryly nějaký pravidelný spj a neby byly na plavbě s průvodcem.
SQL>
SQL> SELECT L.*
2 FROM LOD L JOIN POKRYTI P ON (L.LODID = P.LODID)
3 UNION
4 SELECT L.*
5 FROM LOD L JOIN PLAVBA_S_PRUVODCEM P ON (L.LODID = P.ID_LOD);

LODID JMENOL LTYP BARVA POCET_MIST ID_ZAZEMI
1 Chloe ponorka žlutá 100 599
2 Mariane klipr modrá 50 699
5 Shark ponorka pink 22 599
6 Carmen klipr zelená 100 699
8 Charibda parník modrá 145 899
9 Anne   zelená 100 899
10 Lilien klipr modrá 10 699

7 rows selected.

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d6
SQL> -- Lodě (všechny atributy), které nebyly rezervovány
SQL>
SQL> SELECT *
2 FROM LOD L
3 WHERE L.LODID NOT IN (SELECT LODID
4 FROM REZERVACE);

LODID JMENOL LTYP BARVA POCET_MIST ID_ZAZEMI
6 Carmen klipr zelená 100 699
9 Anne   zelená 100 899
11 Anička plachetnice růžová 10 799
13 LodProPriklad7.1 typProPriklad7 zrzavá 25  
14 LodProPriklad7.2 typProPriklad7 zelená 25  

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d7
SQL> -- Typy lodí, které pokrývají pouze pravidelné spoje.
SQL>
SQL> Select distinct LTYP from
2 ( Select L.*
3 FROM LOD L
4 where exists (select LODID from POKRYTI where pokryti.lodid = l.lodid)
5 MINUS
6 Select L2.*
7 FROM LOD L2
8 where exists (select ID_LOD from PLAVBA_S_PRUVODCEM where plavba_s_pruvodcem.id_lod=l2.lodid )
9 MINUS
10 Select L.*
11 FROM LOD L
12 where exists (select 1 from REZERVACE R where R.LODID=L.LODID)
13 );
no rows selected
SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d8
SQL> -- Zákazníci (id_z, jméno), kteří si rezervovali každou zelenou loď.
SQL>
SQL> WITH
2 T1 AS (SELECT LODID,ZID
3 FROM LOD CROSS JOIN ZAKAZNIK
4 WHERE BARVA = 'zelená' ),
5 T2 AS (SELECT UNIQUE LODID,ZID FROM REZERVACE),
6 T31 AS (SELECT * FROM T1 MINUS SELECT * FROM T2),
7 T32 AS (SELECT UNIQUE ZID FROM T31),
8 T4 AS (SELECT UNIQUE ZID FROM REZERVACE),
9 T5 AS (SELECT * From T4 Where T4.ZID Not In (Select ZID From T32))
10 Select *
11 From T5 Join zakaznik Using(ZID);
no rows selected
SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d9
SQL> -- Dvojice zákazníků, kteří bydlí na stejné adrese.
SQL>
SQL> Select cast(Z1.zid||' '||Z1.jmenoz||' '||Z1.prijmeni||' sousedí s '||
2 Z2.zid||' '||Z2.jmenoz||' '||Z1.prijmeni as varchar(60)) as sousede
3 From zakaznik Z1 Join zakaznik Z2 On (Z1.adresa=Z2.adresa and
4 Z1.zid < Z2.zid);
no rows selected
SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d10
SQL> -- Průměrný věk lodníků.
SQL>
SQL> Select ROUND(AVG(vek),2)
2 From namornik;

ROUND(AVG(VEK),2)
1119.33

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d11
SQL> -- Cekový počet typů lodí, počet různých typů lodí a celkový počet míst na všech lodích.
SQL>
SQL> Select count(distinct ltyp) pocet_typu_lodi
2 , count(distinct barva) pocet_ruznych_barev
3 , sum(pocet_mist) celkovy_pocet_mist
4 From LOD;

POCET_TYPU_LODI POCET_RUZNYCH_BAREV CELKOVY_POCET_MIST
5 8 747

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d12
SQL> -- Lodníci, kteří byli na plavbě s průvodcem méně než 4x.
SQL>
SQL> Select *
2 From namornik N
3 Where (Select count(*)
4 From PLAVBA_S_PRUVODCEM PSP
5 Where N.nid = PSP.nid) < 4;

NID JMENON VEK HODNOST PLAT
1 Dustin 45 7 7000
3 Rusty 35 10 6000
4 Yuppy 35 9 2000
5 Guppy 35 5 4500
7 Horatio 35 7 2500
8 Brutus 33 1 1350
10 John 118 6 14000
11 Charon 999 4 6800
13 Halaska 12000 1  

9 rows selected.

SQL> -- alternativni reseni
SQL> Select nid,N.JMENON,N.VEK,N.HODNOST,N.PLAT
2 From namornik N Left Outer join PLAVBA_S_PRUVODCEM P Using(nid)
3 GROUP BY nid, N.JMENON, N.VEK, N.HODNOST, N.PLAT
4 Having count(ID_LOD) < 4;

NID JMENON VEK HODNOST PLAT
13 Halaska 12000 1  
1 Dustin 45 7 7000
8 Brutus 33 1 1350
11 Charon 999 4 6800
4 Yuppy 35 9 2000
10 John 118 6 14000
5 Guppy 35 5 4500
3 Rusty 35 10 6000
7 Horatio 35 7 2500

9 rows selected.

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d13
SQL> -- Lodníci, kteří pokryli méně než 3 různé pravidelné linky včetně těch, kteří nepokryli žádnou.
SQL>
SQL> Select N.*,
2 (Select count(DISTINCT LID)
3 From pokryti P
4 Where P.nid = N.nid) pocet_ruznych_linek
5 From namornik N
6 Where (Select count(DISTINCT LID)
7 From pokryti P
8 Where P.nid = N.nid) < 3;

NID JMENON VEK HODNOST PLAT POCET_RUZNYCH_LINEK
1 Dustin 45 7 7000 0
2 Lubber 55 8 3000 2
4 Yuppy 35 9 2000 2
5 Guppy 35 5 4500 0
6 Zorba 16 10 7500 0
7 Horatio 35 7 2500 0
8 Brutus 33 1 1350 2
9 Marco 26 2 5200 2
10 John 118 6 14000 2
13 Halaska 12000 1   0

10 rows selected.

SQL> -- alternativni reseni
SQL> SELECT NID,N.JMENON,N.VEK,N.HODNOST,N.PLAT,COUNT(LODID)
2 FROM NAMORNIK N Left Outer Join POKRYTI P Using (Nid)
3 GROUP BY NID,N.JMENON,N.VEK,N.HODNOST,N.PLAT
4 Having count (LODID) < 3;

NID JMENON VEK HODNOST PLAT COUNT(LODID)
13 Halaska 12000 1   0
9 Marco 26 2 5200 2
1 Dustin 45 7 7000 0
10 John 118 6 14000 2
4 Yuppy 35 9 2000 2
5 Guppy 35 5 4500 0
6 Zorba 16 10 7500 0
2 Lubber 55 8 3000 2
7 Horatio 35 7 2500 0

9 rows selected.

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d14
SQL> -- Pro každého lodníka počet jeho plaveb s průvodcem.
SQL>
SQL> elect N.*,
SP2-0042: unknown command "elect N.*," - rest of line ignored.
SQL> (Select count(*)
2 From PLAVBA_S_PRUVODCEM PSP
3 Where N.nid = PSP.nid) pocet_PSP
4 From namornik N; 

       Where N.nid = PSP.nid) pocet_PSP
                              *
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> -- alternativa
SQL> Select nid,N.JMENON,N.VEK,N.HODNOST,N.PLAT,
2 count(ID_LOD) pocet_PSP
3 From namornik N Left Outer join PLAVBA_S_PRUVODCEM P
4 Using(nid)
5 GROUP BY nid, N.JMENON, N.VEK, N.HODNOST, N.PLAT;

NID JMENON VEK HODNOST PLAT POCET_PSP
13 Halaska 12000 1   0
9 Marco 26 2 5200 5
1 Dustin 45 7 7000 1
8 Brutus 33 1 1350 0
11 Charon 999 4 6800 0
4 Yuppy 35 9 2000 0
10 John 118 6 14000 0
5 Guppy 35 5 4500 0
6 Zorba 16 10 7500 7
2 Lubber 55 8 3000 4
3 Rusty 35 10 6000 1
7 Horatio 35 7 2500 1

12 rows selected.

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d15
SQL> -- Jména lodníků mladších 40 let, kteří mají za sebou alespoň 3 plavby s průvodcem na lodi typu klipr. Výstup bude seřazen dle jmen lodníků.
SQL>
SQL> Select N.nid,N.JMENON
2 From Namornik N
3 Where (Select count(*)
4 From PLAVBA_S_PRUVODCEM PSP Join LOD L On(LODID= ID_LOD)
5 Where PSP.nid = N.nid
6 and L.Ltyp ='klipr'
7 ) >=3
8 and N.vek <40
9 order by N.JMENON desc;

NID JMENON
9 Marco

SQL> -- alternativa
SQL> Select nid,N.JMENON
2 From Namornik N Join PLAVBA_S_PRUVODCEM PSP Using (nid)
3 Join lod L On (LODID= ID_LOD)
4 Where L.Ltyp ='klipr' and N.vek <40
5 Group By nid,N.JMENON
6 having count(*) >= 3
7 order by N.JMENON desc;

NID JMENON
9 Marco

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d16
SQL> -- Námořníkům, kteří mají za sebou alespoň 4 pokrytí spoje zvedněte plat o 15%.
SQL>
SQL> Update namornik N
2 Set plat = plat * 1.15
3 where (Select count (lodid)
4 From pokryti P
5 Where P.nid = N.nid) > 4;
1 row updated.

Commit complete.
SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d17
SQL> -- K tabulce LODNIK p5id8me sloupec pocet_plaveb_s_pruvodcem a prvedeme jednorázový dopočet hodnot tohoto sloupce.
SQL>
SQL> -- pridani sloupce
SQL> Alter Table NAMORNIK
2 Add (pocet_plaveb_s_pruvodcem integer Default 0);
Table altered.

SQL> -- dopocitani
SQL> Update namornik N
2 Set pocet_plaveb_s_pruvodcem = (Select count(*)
3 From PLAVBA_S_PRUVODCEM PSP
4 Where PSP.nid=N.nid);
12 rows updated.

Commit complete.
SQL> Commit;
Commit complete.

SQL> -- overeni
SQL> select *
2 from namornik;

NID JMENON VEK HODNOST PLAT POCET_PLAVEB_S_PRUVODCEM
1 Dustin 45 7 7000 1
2 Lubber 55 8 3000 4
3 Rusty 35 10 6000 1
4 Yuppy 35 9 2000 0
5 Guppy 35 5 4500 0
6 Zorba 16 10 7500 7
7 Horatio 35 7 2500 1
8 Brutus 33 1 1350 0
9 Marco 26 2 5200 5
10 John 118 6 14000 0
11 Charon 999 4 7820 0
13 Halaska 12000 1   0

12 rows selected.

SQL> -- uklid
SQL> alter table namornik drop column pocet_plaveb_s_pruvodcem;
Table altered.

SQL> select *
2 from namornik;

NID JMENON VEK HODNOST PLAT
1 Dustin 45 7 7000
2 Lubber 55 8 3000
3 Rusty 35 10 6000
4 Yuppy 35 9 2000
5 Guppy 35 5 4500
6 Zorba 16 10 7500
7 Horatio 35 7 2500
8 Brutus 33 1 1350
9 Marco 26 2 5200
10 John 118 6 14000
11 Charon 999 4 7820
13 Halaska 12000 1  

12 rows selected.

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d18
SQL> -- Zákazníci, kteří si rezervovali každý parník s počtem míst větším než 150.
SQL>
SQL> with
2 T1 as (select distinct zid,lodid from REZERVACE)
3 , T2 as (select zid,lodid from
4 (Select ZID from ZAKAZNIK) cross join
5 (select LODID from lod where LTYP= 'parník'and POCET_MIST > 150)
6 )
7 , T3 as ((select * from T2) minus (select * from T1))
8 , T4 as (select distinct zid from T3)
9 Select *
10 from zakaznik Z
11 where not exists (select 1 from T3 where z.zid = T3.zid); 

          (Select ZID from ZAKAZNIK) cross join
                           *
ERROR at line 4:
ORA-32035: unreferenced query name defined in WITH clause
SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d19
SQL> -- Vytvoření pohledu se seznamem spojů, u nichž budou podrobnosti jejich linek.
SQL>
SQL> create view vspoj as
2 select lid,linka.strt,linka.cil,
3 spoj.spid, spoj.scas
4 from spoj natural join linka
5 order by lid,spoj.scas;
View created.

SQL>
SQL> -- vyber vsechny spoje, ktere by jely dnes po 12. hodine
SQL> select strt, cil, trunc(current_date) + scas as kdy
2 from vspoj
3 where trunc(current_date) + scas > trunc(current_date) + INTERVAL '12' HOUR(2)
4 order by scas;

STRT CIL KDY
Nová Pec Hůrka 15.09.2013 12:12
Hůrka Jestřabí 15.09.2013 13:10
Florenc Skalka 15.09.2013 14:41
Depo Hostivař Bezděkovská 15.09.2013 14:41
Dejvická Letiště 15.09.2013 14:41
Sídliště Čakovice Jesenická 15.09.2013 14:41
Sídliště Spořilov Smíchovské nádraží 15.09.2013 14:41
Nádraží Hostivař Tolstého 15.09.2013 14:41
Chodov Poliklinika Mazurská 15.09.2013 14:41
Bořislavka Hradčanská 15.09.2013 14:41
Florenc Koleje Jižní Město 15.09.2013 14:41
Skalka Spořilov 15.09.2013 14:41
Dejvická Letiště 15.09.2013 15:11
Sídliště Spořilov Smíchovské nádraží 15.09.2013 15:11
Skalka Spořilov 15.09.2013 15:11
Nádraží Hostivař Tolstého 15.09.2013 15:11
Bořislavka Hradčanská 15.09.2013 15:11
Depo Hostivař Bezděkovská 15.09.2013 15:11
Sídliště Čakovice Jesenická 15.09.2013 15:11
Chodov Poliklinika Mazurská 15.09.2013 15:11
Florenc Skalka 15.09.2013 15:11
Florenc Koleje Jižní Město 15.09.2013 15:11
Florenc Skalka 15.09.2013 15:31
Sídliště Spořilov Smíchovské nádraží 15.09.2013 15:31
Sídliště Čakovice Jesenická 15.09.2013 15:31
Chodov Poliklinika Mazurská 15.09.2013 15:31
Bořislavka Hradčanská 15.09.2013 15:31
Dejvická Letiště 15.09.2013 15:31
Florenc Koleje Jižní Město 15.09.2013 15:31
Skalka Spořilov 15.09.2013 15:31
Depo Hostivař Bezděkovská 15.09.2013 15:31
Nádraží Hostivař Tolstého 15.09.2013 15:31
Horní Planá Hůrka 15.09.2013 15:50
Nádraží Hostivař Tolstého 15.09.2013 15:51
Sídliště Čakovice Jesenická 15.09.2013 15:51
Chodov Poliklinika Mazurská 15.09.2013 15:51
Depo Hostivař Bezděkovská 15.09.2013 15:51
Sídliště Spořilov Smíchovské nádraží 15.09.2013 15:51
Florenc Skalka 15.09.2013 15:51
Skalka Spořilov 15.09.2013 15:51
Dejvická Letiště 15.09.2013 15:51
Florenc Koleje Jižní Město 15.09.2013 15:51
Bořislavka Hradčanská 15.09.2013 15:51
Florenc Skalka 15.09.2013 16:11
Bořislavka Hradčanská 15.09.2013 16:11
Depo Hostivař Bezděkovská 15.09.2013 16:11
Skalka Spořilov 15.09.2013 16:11
Chodov Poliklinika Mazurská 15.09.2013 16:11
Sídliště Spořilov Smíchovské nádraží 15.09.2013 16:11
Dejvická Letiště 15.09.2013 16:11
Florenc Koleje Jižní Město 15.09.2013 16:11
Nádraží Hostivař Tolstého 15.09.2013 16:11
Sídliště Čakovice Jesenická 15.09.2013 16:11
Florenc Koleje Jižní Město 15.09.2013 16:26
Sídliště Čakovice Jesenická 15.09.2013 16:26
Dejvická Letiště 15.09.2013 16:26
Sídliště Spořilov Smíchovské nádraží 15.09.2013 16:26
Florenc Skalka 15.09.2013 16:26
Depo Hostivař Bezděkovská 15.09.2013 16:26
Nádraží Hostivař Tolstého 15.09.2013 16:26
Chodov Poliklinika Mazurská 15.09.2013 16:26
Bořislavka Hradčanská 15.09.2013 16:26
Skalka Spořilov 15.09.2013 16:26
Bořislavka Hradčanská 15.09.2013 16:41
Dejvická Letiště 15.09.2013 16:41
Sídliště Spořilov Smíchovské nádraží 15.09.2013 16:41
Skalka Spořilov 15.09.2013 16:41
Nádraží Hostivař Tolstého 15.09.2013 16:41
Depo Hostivař Bezděkovská 15.09.2013 16:41
Sídliště Čakovice Jesenická 15.09.2013 16:41
Chodov Poliklinika Mazurská 15.09.2013 16:41
Florenc Skalka 15.09.2013 16:41
Florenc Koleje Jižní Město 15.09.2013 16:41
Dejvická Letiště 15.09.2013 16:56
Bořislavka Hradčanská 15.09.2013 16:56
Florenc Koleje Jižní Město 15.09.2013 16:56
Florenc Skalka 15.09.2013 16:56
Depo Hostivař Bezděkovská 15.09.2013 16:56
Nádraží Hostivař Tolstého 15.09.2013 16:56
Sídliště Spořilov Smíchovské nádraží 15.09.2013 16:56
Sídliště Čakovice Jesenická 15.09.2013 16:56
Chodov Poliklinika Mazurská 15.09.2013 16:56
Skalka Spořilov 15.09.2013 16:56
Skalka Spořilov 15.09.2013 17:09
Skalka Spořilov 15.09.2013 17:19
Horní Planá Hůrka 15.09.2013 18:00
Karlovy Dvory Radislav 15.09.2013 18:45
Kyselov Kovářov 15.09.2013 19:35
Dolní Vlatavice Kyselov 15.09.2013 19:35
Hůrka Jestřabí 15.09.2013 22:20
Karlovy Dvory Radislav 15.09.2013 23:00
Jenišov Bližší Lhota 15.09.2013 23:59

92 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d20
SQL> -- Kteří lodníci sloužili na jednotlivých lodích?
SQL>
SQL> select distinct lod.lodid,n.nid
2 From lod
3 left outer join (SELECT distinct NID, LODID FROM POKRYTI
4 union
5 SELECT unique NID, id_lod FROM PLAVBA_S_PRUVODCEM) A
6 on (lod.lodid=A.lodid)
7 right outer join namornik N on (A.nid=N.nid)
8 order by lodid, nid;

LODID NID
1 2
1 6
1 11
2 2
2 3
2 4
5 3
5 9
5 11
6 4
6 7
6 10
8 1
8 8
9 2
9 3
9 9
10 9
10 10
  5
  13

21 rows selected.

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d21
SQL> -- Seznam rezervací včetně lodí, které nebyly rezervovány a zákazníků, kteří si nic nerezervovali.
SQL>
SQL> SELECT REZERVACE.DATUM_RES,
2 ZAKAZNIK.ZID,
3 ZAKAZNIK.JMENOZ,
4 ZAKAZNIK.PRIJMENI,
5 ZAKAZNIK.CREDITLIMIT,
6 ZAKAZNIK.ADRESA,
7 LOD.LODID,
8 LOD.JMENOL,
9 LOD.BARVA,
10 LOD.LTYP,
11 LOD.POCET_MIST
12 FROM ZAKAZNIK LEFT OUTER JOIN REZERVACE ON ZAKAZNIK.ZID = REZERVACE.ZID
13 FULL OUTER JOIN LOD ON LOD.LODID = REZERVACE.LODID
14 order by datum_res, zakaznik.prijmeni,zakaznik.jmenoz,lod.jmenol;

DATUM_RES ZID JMENOZ PRIJMENI CREDITLIMIT ADRESA LODID JMENOL BARVA LTYP POCET_MIST
24.12.2000 00:00 9 Adams Adams 550 Adamsovice 2 Mariane modrá klipr 50
24.12.2000 00:00 1 Ford Ford 100 Fordovice 10 8 Charibda modrá parník 145
24.12.2000 00:00 6 Martin Martin 330 Martinovice 10 1 Chloe žlutá ponorka 100
24.12.2000 00:00 7 Scott Scott 400 Scottovice 7 Yeanifer černá parník 110
01.01.2001 00:00 1 Ford Ford 100 Fordovice 10 8 Charibda modrá parník 145
01.01.2001 00:00 6 Martin Martin 330 Martinovice 10 8 Charibda modrá parník 145
01.01.2001 00:00 6 Martin Martin 330 Martinovice 10 1 Chloe žlutá ponorka 100
01.01.2001 00:00 6 Martin Martin 330 Martinovice 10 10 Lilien modrá klipr 10
01.01.2001 00:00 6 Martin Martin 330 Martinovice 10 2 Mariane modrá klipr 50
01.01.2001 00:00 6 Martin Martin 330 Martinovice 10 4 Pooh hnědá plachetnice 20
01.01.2001 00:00 6 Martin Martin 330 Martinovice 10 5 Shark pink ponorka 22
01.01.2001 00:00 6 Martin Martin 330 Martinovice 10 3 Skyla černá ponorka 30
01.01.2001 00:00 6 Martin Martin 330 Martinovice 10 7 Yeanifer černá parník 110
01.01.2001 00:00 7 Scott Scott 400 Scottovice 5 Shark pink ponorka 22
  4 Allen Allen 300 Allenovice 10          
  3 Blake Blake 120 Blakeovice 10          
  10 James James 1000 Jamesovice          
  12 Odysseus Laertiadés 1700 Ithaka 1          
  2 Smith Smith 110 Smithovice 10          
  11 Iásón Syn Krétheův 1500 Iólkos          
  8 Turner Turner 440 Turnerovice          
  5 Ward Ward 310 Wardovice 10          
            11 Anička růžová plachetnice 10
            9 Anne zelená   100
            6 Carmen zelená klipr 100
            13 LodProPriklad7.1 zrzavá typProPriklad7 25
            14 LodProPriklad7.2 zelená typProPriklad7 25

27 rows selected.

SQL>
SQL>
SQL> --KOD DOTAZU: d22
SQL> -- Seznam linek, které jsou pokryty pouze autobusem SPZ101
SQL>
SQL> -- Seznam linek, které jsou pokryty pouze autobusem SPZ101
SQL> SELECT distinct LINKA.LID, STRT,CIL
2 FROM LINKA
3 INNER JOIN SPOJ ON LINKA.LID = SPOJ.LID
4 INNER JOIN POKRYTI ON SPOJ.LID = POKRYTI.LID
5 AND SPOJ.SPID = POKRYTI.SPID
6 INNER JOIN AUTOBUS ON AUTOBUS.INV_CISLO = POKRYTI.INV_CISLO
7 AND SPZ='SPZ101'
8 MINUS
9 SELECT distinct LINKA.LID, STRT,CIL
10 FROM LINKA
11 INNER JOIN SPOJ ON LINKA.LID = SPOJ.LID
12 INNER JOIN POKRYTI ON SPOJ.LID = POKRYTI.LID
13 AND SPOJ.SPID = POKRYTI.SPID
14 INNER JOIN AUTOBUS ON AUTOBUS.INV_CISLO = POKRYTI.INV_CISLO
15 AND SPZ != 'SPZ101';
no rows selected
SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d23
SQL> -- Seznam namorniku, kteri jsou volni dne 23.09.2013 od 8 do 14 hod.
SQL>
SQL>
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';
Session altered.

SQL> select *
2 from namornik
3 where nid in ( select distinct nid
4 from kalendar_namornika
5 where od not in ('23.09.2013 08:00', '23.09.2013 14:00')
6 );

NID JMENON VEK HODNOST PLAT
1 Dustin 45 7 7000
2 Lubber 55 8 3000
3 Rusty 35 10 6000
4 Yuppy 35 9 2000
6 Zorba 16 10 7500
7 Horatio 35 7 2500
8 Brutus 33 1 1350
9 Marco 26 2 5200
10 John 118 6 14000
11 Charon 999 4 7820

10 rows selected.

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d24
SQL> -- Seznam lodi, ktere jsou volne dne 23.09.2013 od 8 do 14 hod. a maji kapacitu od 10 do 25 mist.
SQL>
SQL>
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';
Session altered.

SQL> select distinct lodid
2 from lod l1
3 where lodid not in (select lodid
4 from rezervace
5 where datum_res = '23.09.2013')
6 and not exists (select lodid
7 from pokryti p
8 where l1.lodid = p.lodid
9 and p.DATUM_POKRYTI = '23.09.2013'
10 and ( p.od in ('23.09.2013 08:00', '23.09.2013 14:00')
11 or p.do in ('23.09.2013 08:00', '23.09.2013 14:00')
12 )
13 )
14 and l1.pocet_mist in (10,25);

LODID
13
14
10
11

SQL>
SQL>
SQL>
SQL> --KOD DOTAZU: d25
SQL> -- Pridejte novou plavbu s pruvodcem pro zakaznika s prijmenim Scott na 23.09.2013 od 8 do 14 hod.
SQL>
SQL> -- Nasaďte na to lod, ktera je na tu dobu volna a ma kapacitu 10 až 25 míst.
SQL>
SQL> -- Nasaďte na to kterehokoliv námornika, který je na tuto dobu volný.
SQL>
SQL>
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';
Session altered.

SQL> -- set autocommit off
SQL> -- nejdrive je nutne vyplnit kalendar namornika
SQL> INSERT
2 INTO KALENDAR_NAMORNIKA (NID,OD,DO)
3 VALUES( (select nid
4 from kalendar_namornika
5 where od not in ('23.09.2013 08:00', '23.09.2013 14:00')
6 and rownum =1
7 ),
8 '23.09.2013 08:00',
9 '23.09.2013 14:00'
10 );
1 row created.

Commit complete.
SQL> -- zaplanovani plavby
SQL> INSERT
2 INTO PLAVBA_S_PRUVODCEM( NID,OD,DO,ID_LOD,ZID)
3 VALUES ( (select nid
4 from kalendar_namornika
5 where od ='23.09.2013 08:00' and do= '23.09.2013 14:00'
6 and rownum=1
7 ),
8 '23.09.2013 08:00',
9 '23.09.2013 14:00',
10 ( select distinct lodid
11 from lod l1
12 where lodid not in ( select lodid
13 from rezervace
14 where datum_res = '23.09.2013')
15 and not exists ( select lodid
16 from pokryti p
17 where l1.lodid = p.lodid
18 and p.DATUM_POKRYTI = '23.09.2013'
19 and ( p.od in ('23.09.2013 08:00', '23.09.2013 14:00')
20 or p.do in ('23.09.2013 08:00', '23.09.2013 14:00')
21 )
22 )
23 and l1.pocet_mist in (10,25)
24 and rownum = 1
25 ),
26 ( SELECT ZID
27 FROM ZAKAZNIK
28 WHERE prijmeni='Scott' and jmenoz='Scott' and rownum=1
29 )
30 );
1 row created.

Commit complete.
SQL> rollback;
Rollback complete.

SQL>
SQL>
SQL>
SQL> set markup html off
SQL> spool off