SQL příklady - půjčovna lodí:

1. Seznam člunů (BID, BNAME), které nikdy nejely na pravidelné lince (nejsou v tabulce pokrytí).

   select BID, BNAME from BOAT
   where BID not in (select BID from REG_LINE_REL_COVER);

         BID BNAME
---------- --------------------
         3     Skyla
         4     Pooh
         7     Yeanifer

2. Seznam námořníků (SNAME, AGE), kteří jeli jako průvodci na žluté lodi typu "SUBMARINE".

   select distinct SNAME, AGE
   from SAILOR S, SAIL_WITH_GUIDE SG, BOAT B
   where S.SID = SG.SID and B.BID = SG.BID and
         COLOR = 'yellow' and BTYPE = 'submarine' ;

SNAME                              AGE
-------------------- ----------
Zorba                                16

3. Kolik zelených členů je registrováno v půjčovně?

   select count(*) "Green Boats"
   from BOAT
   where COLOR = 'green';

Green Boats
-----------
          2

4. Jaký je průměrný věk námořníků?

   select avg(AGE) as "Avagage Age of Sailors"
   from SAILOR;

Avagage Age of Sailors
----------------------
            130.181818

5. Najděte námořníky (SID, SNAME), kteří jeli alespoň třikrát pravidelnou linku.

   select SID, SNAME
   from SAILOR S
   where 4 <= (select count(*)
                from REG_LINE_REL_COVER R
                where S.SID = R.SID);

       SID         SNAME
---------- --------------------
         8         Brutus
        11         Charon

6. Jména námořníků a počet jejich plaveb coby průvodce, výstup seřaďte podle vzestupně podle počtu plaveb.

   select SNAME, SG_TIMES "Number of Sails"
   from SAILOR S, (select count(*)as SG_TIMES, SID
                   from SAIL_WITH_GUIDE
                   group by SID) NS
   where S.SID = NS.SID
   order by SG_TIMES;

SNAME                Number of Sails
-------------------- ---------------
Dustin                             1
Rusty                              1
Horatio                            1
Lubber                             4
Marco                              5
Zorba                              7

7. Jména námořníků a počet jejich plaveb coby průvodece, vypusťte námořníky, kteří jeli méně než třikrát, výstup seřaďte sestupně podle počtu plaveb.

   select SNAME, SG_TIMES "Number of Sails"
   from SAILOR S, (select count(*) as SG_TIMES, SID
                   from SAIL_WITH_GUIDE
           group by SID) NS
   where SG_TIMES > 3 and S.SID = NS.SID
   order by SG_TIMES;

SNAME                Number of Sails
-------------------- ---------------
Lubber                             4
Marco                              5
Zorba                              7

8. Seznam námořníků a počet jejich plaveb (coby průvodce i na linkách). Výstup seřaďte podle počtu plaveb.

   select SNAME, nvl(SG_TIMES,0) + nvl(RG_TIMES,0) as "Total # of Sails"
   from SAILOR S, (select count(*) as SG_TIMES, SID
                   from SAIL_WITH_GUIDE
                   group by SID) RG,
                   (select count(*) as RG_TIMES, SID
                   from REG_LINE_REL_COVER
                   group by SID) SG
where S.SID = RG.SID(+) and S.SID = SG.SID(+)
order by "Total # of Sails", SNAME ;

SNAME                Total # of Sails
-------------------- ----------------
Guppy                               0
Dustin                              1
Horatio                             1
John                                2
Yuppy                               2
Brutus                              4
Rusty                               4
Charon                              5
Lubber                              6
Marco                               7
Zorba                               7

9. Vytvořte pohle Favorit_Boats (BID, BNAME, COLOR, TYPE). Jsou zde čluny, které byly pronajaty k soukromé plavbě více než dvakrát.

   create or replace view FAVORITE_BOATS as
   select BID, BNAME, COLOR, BTYPE
   from BOAT B
   where 2 <= (select count(*)
               from SAIL_WITH_GUIDE SG
               where B.BID = SG.BID);

select * from favorite_boats;

       BID BNAME                COLOR                BTYPE
------- -------------------- -------------------- --------------------
      1 Jane                 yellow               submarine
      9 Anne                 green
     10 Lilien               blue                 clipper

10. Vytvořte pohled Popular_Sailor (SID, SNAME, AGE). Jsou zde námořníci, kteří jeli více jak 5x (jako průvodci nebo na pravidelné lince)

   create or replace view POPULAR_SAILOR as
   select S.SID, SNAME, AGE
   from SAILOR S, (select count(*) as SG_TIMES, SID
                   from SAIL_WITH_GUIDE
                   group by SID) RG,
                   (select count(*) as RG_TIMES, SID
                   from REG_LINE_REL_COVER
                   group by SID) SG
   where S.SID = RG.SID(+) and S.SID = SG.SID(+)
       and (nvl(SG_TIMES,0) + nvl(RG_TIMES,0)) > 5;

   select * from popular_sailor;

       SID SNAME                       AGE
---------- -------------------- ----------
         2 Lubber                       55
         6 Zorba                        16
         9 Marco                        26

11. Pokuste se vložit nového námořníka jménem Yarwen, 23 let, rating 8.

insert into sailor values (12, 'Yarvin', 23, 8);

1 row created.

SQL> commit;

Commit complete.

12. Pokuste se vložit nového námořníka jménem Dustin, 56 let, rating 9.

insert into sailor values (13, 'Dustin', 56, 9);
insert into sailor values (13, 'Dustin', 56, 9)
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$VALENTA.SAILOR_UK) violated

13. Zkuste z databáze vymazat loď jménem Pooh.

delete from boat
where BNAME = 'Pooh';

1 row deleted.

SQL> rollback;

Rollback complete.

14 Zkuste z databáze vymazat loď jménem Jane.

delete from boat
where BNAME = 'Jane';

delete from boat
*
ERROR at line 1:
ORA-02292: integrity constraint (OPS$VALENTA.SAIL_WITH_GUIDE_FK2) violated -
child record found

15 Změnte barvu lodi Yenifer z černé na modrou.

update BOAT
set COLOR = 'blue'
where BNAME = 'Yeanifer';

1 row updated.

SQL> commit;

Commit complete.

16. Zkuste změnit BID člunu 5 na hodnotu 13.

update BOAT
set BID = 13
where BID = 5;

update BOAT
*
ERROR at line 1:
ORA-02292: integrity constraint (OPS$VALENTA.REG_LINE_REL_COVER_FK2) violated -
child record found