home_site

Lab03 - Bazy danych cw.03 [ lab:17.06.2023, ver. SPN.2023.06.16.003 ]

Zawartość strony

Bazy danych

Tematyka zajęć:

  1. Przykładowa baza KURS v.2.1
  2. Operatory UNION, INTERSECT i EXCEPT
  3. Operator porównania CASE
  4. Polecenie WITH
  5. Widok (perspektywa) w bazie relacyjnej

  1. Baza danych KURS v.2.1

    1. W ramach tego zadania przedstawiona została kolejna wersja bazy zarządzająca kursami, ich uczestnikami oraz wykładowcami - KURS v.2.1. W bazie danych mamy uczestników, którzy mogą uczestniczyć w kilku kursach oraz wykładowców którzy mogą prowadzić różne kursy. Dodatkowo dany kurs może być realizowany w kilku grupach zajęciowych. Nazwa kursu jest dostępna w dodatkowej tabeli.
    2. W bazie danych KURS v.2.1 wyróżniamy następujące encje:
      • UCZESTNIK - opisuje uczestnika kursu,
      • WYKLADOWCA - opisuje wykladowcę kursu,
      • KURS_NAZWA - zawiera nazwy kursów,
      • KURS - opisuje kurs,
      • UCZEST_KURS - encja asocjacyjna, łącząca tabele uczestnik i kurs,
      • WYKL_KURS - encja asocjacyjna, łącząca tabele wykladowca i kurs,
    3. Struktura tabel (encji) w bazie
      • Tabela kurs2.UCZESTNIK
        id_uczestnikintPRIMARY KEYKlucz główny
        imievarchar(30)NOT NULL
        nazwiskovarchar(30)NOT NULL
        telefonchar(12)NOT NULL
      • Tabela kurs2.WYKLADOWCA
        id_wykladowcaintPRIMARY KEYKlucz główny
        imievarchar(30)NOT NULL
        nazwiskovarchar(30)NOT NULL
        telefonchar(12)
        emailvarchar(30)
      • Tabela kurs2.KURS_NAZWA
        id_nazwaintPRIMARY KEYKlucz główny
        nazwavarchar(30)Nazwa kursu
      • Tabela kurs2.KURS
        id_kursintPRIMARY KEYKlucz główny
        terminvarchar(30)NOT NULL
        id_nazwaintFOREIGN KEYKlucz obcy do tabeli KURS_NAZWA
      • Tabela kurs2.UCZEST_KURS
        id_uczestintFOREIGN KEYKlucz obcy do tabeli uczestnik
        id_kursintFOREIGN KEYKlucz obcy do tabeli kurs
        ocenaNymeric(5,1)
        oplataNumeric(10,2)
      • Tabela kurs2.WYKL_KURS
        id_wyklintFOREIGN KEYKlucz obcy do tabeli uczestnik
        id_kursintFOREIGN KEYKlucz obcy do tabeli kurs
    4. Diagram ERD bazy danych KURS v.2.1 przedstawiono na rys.1.
      Lab03_img01
      Rys.1 Diagram ERD dla bazy danych KURS v.2.1
    5. Utworzenie struktury bazy danych KURS v.2.1 - polecenia DDL tworzące tabele i budujące pomiędzy nimi zależności typu FK. 
      --==============================================================================
-- Cwiczenie 07 - jezyk DDL - baza danych KURS v.2.1
--==============================================================================
-- *****************************************************************************
--
-- Przygotawanie schematu kurs2
CREATE SCHEMA kurs2 ;
--
-- Przygotowanie tabeli kurs2.UCZESTNIK
create table kurs2.uczestnik ( id_uczestnik int, nazwisko varchar(30), imie varchar(30), telefon char(12),
     CONSTRAINT PK_uczestnik PRIMARY KEY ( id_uczestnik ) ) ;
--
-- Przygotowanie tabeli kurs2.WYKLADOWCA
create table kurs2.wykladowca ( id_wykladowca int, nazwisko varchar(30), imie varchar(30), telefon char(12), email varchar(20), 
     CONSTRAINT PK_wykladowca PRIMARY KEY ( id_wykladowca ) ) ;	 
--
-- Przygotowanie tabeli kurs2.KURS_NAZWA
create table kurs2.kurs_nazwa ( id_nazwa int, nazwa varchar(30), 
     CONSTRAINT PK_kurs_nazwa PRIMARY KEY ( id_nazwa ) ) ;	 
--
-- Przygotowanie tabeli KURS
create table kurs2.kurs ( id_kurs int, id_nazwa int, termin varchar(30), 
     CONSTRAINT PK_kurs PRIMARY KEY (id_kurs),
     CONSTRAINT FK_nazwa FOREIGN KEY (id_nazwa) REFERENCES kurs2.kurs_nazwa (id_nazwa) );
--
-- Przygotowanie tabeli kurs2.UCZEST_KURS
create table kurs2.uczest_kurs ( id_uczest int, id_kurs int , ocena numeric(5,1), oplata numeric(10,2),
     CONSTRAINT PK_uczest_kurs PRIMARY KEY (id_uczest, id_kurs),
     CONSTRAINT FK_kurs FOREIGN KEY (id_kurs) REFERENCES kurs2.kurs (id_kurs),
     CONSTRAINT FK_uczest FOREIGN KEY(id_uczest) REFERENCES kurs2.uczestnik (id_uczestnik) ) ;
-- 
-- Przygotowanie tabeli kurs2.WYKL_KURS
create table kurs2.wykl_kurs ( id_wykl int, id_kurs int,  
     CONSTRAINT PK_wykl_kurs PRIMARY KEY (id_wykl, id_kurs), 
     CONSTRAINT FK_kurs FOREIGN KEY (id_kurs) REFERENCES kurs2.kurs (id_kurs),
     CONSTRAINT FK_wykl FOREIGN KEY (id_wykl) REFERENCES kurs2.wykladowca (id_wykladowca) ) ;
--	 
-- **********************************************************************************
    6. Wprowadzenie przykładowych danych do tabel.

      Skrypt data1.sql ( [listing dokumentu] [link do dokumentu] )

         -- wstawienie danych - tabela kurs2.uczestnik
insert into kurs2.uczestnik ( id_uczestnik, nazwisko, imie ) values
( 1, 'Flisikowski', 'Jan'),
( 2, 'Olech', 'Andrzej'       ),
( 3, 'Płochocki', 'Piotr'    ),
( 4, 'Stachyra', 'Krzysztof' ),
( 5, 'Sztuka', 'Stanisław'   ),
( 6, 'Sosin', 'Tomasz'       ),
( 7, 'Głowala', 'Paweł'      ),
( 8, 'Straszewski', 'Józef'  ),
( 9, 'Dwojak', 'Marcin'      ),
(10, 'Kotulski', 'Marek'    ),
(11, 'Łaski', 'Michał'       ),
(12, 'Iwanowicz', 'Grzegorz' ),
(13, 'Barnaś', 'Jerzy'       ),
(14, 'Stachera', 'Tadeusz'   ),
(15, 'Gzik', 'Adam'          ),
(16, 'Całus', 'Łukasz'       ),
(17, 'Kołodziejek', 'Zbigniew'),
(18, 'Bukowiecki', 'Ryszard' ),
(19, 'Sielicki', 'Dariusz'   ),
(20, 'Radziszewski', 'Henryk'),
(21, 'Szcześniak', 'Mariusz' ),
(22, 'Nawara', 'Kazimierz'   ),
(23, 'Kęski', 'Wojciech'     ),
(24, 'Rafalski', 'Robert'    ),
(25, 'Hołownia', 'Mateusz'   ),
(26, 'Niedziałek', 'Marian'  ),
(27, 'Matuszczak', 'Rafał'   ),
(28, 'Wolf', 'Jacek'         ),
(29, 'Kolczyński', 'Janusz'  ),
(30, 'Chrobok', 'Mirosław'   )  ;

-- wstawienie danych - tabela kurs2.wykladowca
insert into kurs2.wykladowca ( id_wykladowca, imie, nazwisko ) values
( 1, 'Marcin','Szymczak'),
( 2, 'Joanna','Baranowska'),
( 3, 'Maciej','Szczepański'),
( 4, 'Czesław','Wróbel'),
( 5, 'Grażyna','Górska'),
( 6, 'Wanda','Krawczyk'),
( 7, 'Renata','Urbańska'),
( 8, 'Wiesława','Tomaszewska'),
( 9, 'Bożena','Baranowska'),
(10, 'Ewelina','Malinowska'),
(11, 'Anna','Krajewska'),
(12, 'Mieczysław','Zając'),
(13, 'Wiesław','Przybylski'),
(14, 'Dorota','Tomaszewska'),
(15, 'Jerzy','Wróblewski') ;

-- wstawienie danych - tabela kurs2.kurs_nazwa
insert into kurs2.kurs_nazwa ( id_nazwa, nazwa ) values
( 1, 'Język angielski, stopień 1'),
( 2, 'Język angielski, stopień 2'),
( 3, 'Język angielski, stopień 3'), 
( 4, 'Język angielski, stopień 4'),
( 5, 'Język angielski, stopień 5'),
( 6, 'Język niemiecki, stopień 1'),
( 7, 'Język niemiecki, stopień 2'),
( 8, 'Język niemiecki, stopień 3'),
( 9, 'Język niemiecki, stopień 4'),
(10, 'Język hiszpański, stopień 1'),
(11, 'Język hiszpański, stopień 2'),
(12, 'Język hiszpański, stopień 3') ;
 

 
-- wstawienie danych - tabela kurs
insert into kurs2.kurs ( id_kurs, id_nazwa, termin ) values
( 1, 1, '1.01.2017-31.03.2017'),
( 2, 1, '1.01.2017-31.03.2017'),
( 3, 2, '1.04.2017-30.06.2017'),
( 4, 3, '1.08.2017-10.10.2017'),
( 5, 4, '1.11.2017-23.12.2017'),
( 6, 6, '1.01.2017-31.03.2017'),
( 7, 6, '1.01.2017-31.03.2017'),
( 8, 7, '1.04.2017-30.06.2017'),
( 9, 8, '1.07.2017-31.07.2017'),
(10, 10, '1.02.2017-31.05.2017'),
(11, 11, '1.09.2017-30.11.2017') ; 
 
-- wstawienie danych - tabela wykl_kurs - wykladowcy na kursach
insert into kurs2.wykl_kurs ( id_kurs, id_wykl ) values
( 1, 1 ),
( 2, 2 ),
( 3, 1 ),
( 4, 1 ),
( 5, 3 ),
( 6, 4 ),
( 7, 5 ),
( 8, 4 ),
( 9, 4 ),
(10, 11 ),
(11, 11 ) ; 
 
-- wstawienie danych - tabela uczest_kurs - uczestnicy na kursach
insert into kurs2.uczest_kurs ( id_kurs, id_uczest ) values
-- kurs 1 - angielski 1 gr 1
( 1, 1 ),
( 1, 3 ),
( 1, 5 ),
( 1, 7 ),
( 1, 8 ),
( 1, 10 ),
( 1, 11 ),
( 1, 12 ),
-- kurs 2 - angielski 1 gr 2
( 2, 2 ),
( 2, 16 ),
( 2, 17 ),
( 2, 18 ),
( 2, 20 ),
-- kurs 3 - angielski 2 gr 1
( 3, 1 ),
( 3, 2 ),
( 3, 3 ),
( 3, 5 ),
( 3, 7 ),
( 3, 17 ),
( 3, 18 ),
( 3, 20 ),
-- kurs 4 - angielski 3 gr 1
( 4, 1 ),
( 4, 2 ),
( 4, 3 ),
( 4, 5 ),
( 4, 21 ),
( 4, 22 ),
( 4, 25 ),
-- kurs 5 - angielski 4 gr 1
( 5, 1 ),
( 5, 2 ),
( 5, 3 ),
( 5, 5 ),
( 5, 21 ),
( 5, 22 ),
-- kurs 6 - niemiecki 1 gr 1
( 6, 8 ),
( 6, 9 ),
( 6, 13 ),
( 6, 15 ),
( 6, 19 ),
( 6, 24 ),
( 6, 27 ),
-- kurs 7 - niemiecki 1 gr 2
( 7, 11 ),
( 7, 17 ),
( 7, 18 ),
( 7, 23 ),
( 7, 25 ),
( 7, 28 ),
( 7, 30 ),
-- kurs 8 - niemiecki 2 gr 1
( 8, 8 ),
( 8, 9 ),
( 8, 13 ),
( 8, 15 ),
( 8, 19 ),
( 8, 24 ),
( 8, 27 ),
-- kurs 9 - niemiecki 3 gr 1
( 9, 8 ),
( 9, 9 ),
( 9, 13 ),
( 9, 24 ),
( 9, 27 ),
-- kurs 10 - hiszpanski 1 gr 1
(10, 6 ),
(10, 16 ),
(10, 18 ),
(10, 22 ),
(10, 24 ),
(10, 29 ),
(10, 30 ),
-- kurs 11 - hiszpanski 2 gr 1
(11, 6 ),
(11, 16 ),
(11, 18 ),
(11, 22 ),
(11, 24 ),
(11, 29 ),
(11, 30 ) ;
    7. Modyfikacja tabeli kurs2.UCZEST_KURS - dodatkowe pola oena i oplata oraz przykładowe dane.

      Skrypt data2.sql ( [listing dokumentu] [link do dokumentu] )

      -- wstawienie danych - tabela kurs2.uczest_kurs - uczestnicy na kursach
-- kurs 1 - angielski 1 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=1 and id_uczest=1;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 4 )  where  id_kurs=1 and id_uczest=3;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 5 )  where  id_kurs=1 and id_uczest=5;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=1 and id_uczest=7;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 4 )  where  id_kurs=1 and id_uczest=8;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 4 )  where  id_kurs=1 and id_uczest=10;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 5 )  where  id_kurs=1 and id_uczest=11;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=1 and id_uczest=12;
-- kurs 2 - angielski 1 gr 2
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=2 and id_uczest=2;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=2 and id_uczest=16;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 5 )  where  id_kurs=2 and id_uczest=17;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=2 and id_uczest=18;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 500., 4 )  where  id_kurs=2 and id_uczest=20;
-- kurs 3 - angielski 2 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=3 and id_uczest=1;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=3 and id_uczest=2;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 5 )  where  id_kurs=3 and id_uczest=3;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=3 and id_uczest=5;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=3 and id_uczest=7;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=3 and id_uczest=17;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 5 )  where  id_kurs=3 and id_uczest=18;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=3 and id_uczest=20;
-- kurs 4 - angielski 3 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=4 and id_uczest=1;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=4 and id_uczest=2;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=4 and id_uczest=3;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 3 )  where  id_kurs=4 and id_uczest=5;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 3 )  where  id_kurs=4 and id_uczest=21;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 5 )  where  id_kurs=4 and id_uczest=22;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 5 )  where  id_kurs=4 and id_uczest=25;
-- kurs 5 - angielski 4 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 850., 4 )  where  id_kurs=5 and id_uczest=1;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 850., 4 )  where  id_kurs=5 and id_uczest=2;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 850., 4 )  where  id_kurs=5 and id_uczest=3;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 850., 3 )  where  id_kurs=5 and id_uczest=5;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 850., 3 )  where  id_kurs=5 and id_uczest=21;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 850., 5 )  where  id_kurs=5 and id_uczest=22;
-- kurs 6 - niemiecki 1 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=6 and id_uczest=8;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=6 and id_uczest=9;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=6 and id_uczest=13;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=6 and id_uczest=15;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 5 )  where  id_kurs=6 and id_uczest=19;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=6 and id_uczest=24;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=6 and id_uczest=27;
-- kurs 7 - niemiecki 1 gr 2
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=7 and id_uczest=11;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=7 and id_uczest=17;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=7 and id_uczest=18;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=7 and id_uczest=23;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 5 )  where  id_kurs=7 and id_uczest=25;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=7 and id_uczest=28;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=7 and id_uczest=30;
-- kurs 8 - niemiecki 2 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 650., 4 )  where  id_kurs=8 and id_uczest=8;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 650., 4 )  where  id_kurs=8 and id_uczest=9;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 650., 3 )  where  id_kurs=8 and id_uczest=13;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 650., 3 )  where  id_kurs=8 and id_uczest=15;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 650., 5 )  where  id_kurs=8 and id_uczest=19;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 650., 3 )  where  id_kurs=8 and id_uczest=24;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 650., 3 )  where  id_kurs=8 and id_uczest=27;
-- kurs 9 - niemiecki 3 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=9 and id_uczest=8;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=9 and id_uczest=9;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=9 and id_uczest=13;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 5 )  where  id_kurs=9 and id_uczest=24;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 800., 5 )  where  id_kurs=9 and id_uczest=27;
-- kurs 10 - hiszpanski 1 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=10 and id_uczest=6;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=10 and id_uczest=16;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=10 and id_uczest=18;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=10 and id_uczest=22;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 5 )  where  id_kurs=10 and id_uczest=24;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=10 and id_uczest=29;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=10 and id_uczest=30;
-- kurs 11 - hiszpanski 2 gr 1
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 900., 4 )  where  id_kurs=11 and id_uczest=6;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 900., 4 )  where  id_kurs=11 and id_uczest=16;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 900., 4 )  where  id_kurs=11 and id_uczest=18;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 900., 4 )  where  id_kurs=11 and id_uczest=22;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 900., 5 )  where  id_kurs=11 and id_uczest=24;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 900., 3 )  where  id_kurs=11 and id_uczest=29;
update kurs2.uczest_kurs set ( oplata, ocena ) = ( 900., 3 )  where  id_kurs=11 and id_uczest=30;
    8. Zapytanie generujace tabelę przedstawiającą wykładowcę, kurs który prowadzi i jego termin.
      Lab03_img02
      Rys.2 Realizacja zapytania
      select w.nazwisko as "Nazwisko", w.imie as "Imie", ko.nazwa as "Nazwa kursu", 
       k.termin as "Termin kursu" from
kurs2.wykladowca w join kurs2.wykl_kurs wk on w.id_wykladowca = wk.id_wykl
                            join kurs2.kurs k on k.id_kurs=wk.id_kurs 
                            join kurs2.kurs_nazwa ko on ko.id_nazwa=k.id_nazwa
order by w.nazwisko;
    9. Zapytanie generujące tabelę przedstawiającą uczestnika, kurs i jego termin.
      Lab03_img03
      Rys.3 Realizacja zapytania
      select ko.nazwa as "Nazwa kursu", k.termin as "Termin kursu", k.id_kurs as „Kurs",
       u.nazwisko as "Nazwisko", u.imie as "Imie" from
kurs2.uczestnik u join kurs2.uczest_kurs uk on u.id_uczestnik = uk.id_uczest
                      join kurs2.kurs k on uk.id_kurs = k.id_kurs 
	      join kurs2.kurs_nazwa ko on k.id_nazwa = ko.id_nazwa
order by ko.opis, k.id_kurs, u.nazwisko;

  2. Operatory: UNION, INTERSECT i EXCEPT

    1. W ramach operatorów działających na zbiorach wyróżniamy polecenia w języku SQL, które działają na co najmniej dwóch oddzielnych poleceniach SQL. Jeżeli mamy dwa zbiory danych możemy wyróżnić następujące metody wybrania danych:
      • dane są w jednym lub drugim zbiorze - operacja sumy, w SQL operator UNION 
        SELECT a1, a2, a3 FROM R1
UNION [ALL]
SELECT a1, a2, a3 FROM R2;
      • dane są zarówno w zbiorze pierwszym i drugim - operator przecięcia, w SQL operator INTERSECT 
        SELECT a1, a2, a3 FROM R1
INTERSECT
SELECT a1, a2, a3 FROM R2;
      • dane są w zbiorze pierwszym a nie ma ich w zbiorze drugim (i odwrotnie) - operator różnicy, w SQL operator EXCEPT 
        SELECT a1, a2, a3 FROM R1
EXCEPT
SELECT a1, a2, a3 FROM R2 ;
    2. Występujące w poleceniach relacje (tabele) muszą mieć zgodną liczbę atrybutów oraz domeny poszczególnych atrybutów muszą być zgodne.
    3. Przykłady wykorzystania operatorów UNION, EXCEPT i INTERSECT w bazie danych KURS.
      Lista wszystkich uczestników i wykładowców - operator UNION.
      SELECT imie, nazwisko, 'U' FROM kurs2.uczestnik
UNION
SELECT imie, nazwisko, 'W' FROM kurs2.wykladowca 
ORDER BY 3,2 ;
      Lista wykładowców, którzy nie prowadzą kursów - operator EXCEPT.
      SELECT imie, nazwisko FROM kurs2.wykladowca
EXCEPT 
SELECT imie, nazwisko 
FROM kurs2.wykladowca w JOIN kurs2.wykl_kurs wk ON w.id_wykladowca = wk.id_wykl
ORDER BY 2;
      Lista wykładowców, którzy nie prowadzą kursów. Realizacja z wykorzystaniem złączenia LEFT OUTER JOIN
      SELECT imie, nazwisko 
FROM kurs2.wykladowca w LEFT JOIN kurs2.wykl_kurs wk ON w.id_wykladowca = wk.id_wykl 
WHERE wk.id_wykl is null ;
      Lista uczestników, którzy uczestniczyli na 1 i 2 stopniu kursu języka angielskiego, operator INTERSECT (rys.4)
      SELECT imie, nazwisko 
FROM kurs2.uczestnik u JOIN kurs2.uczest_kurs uk ON u.id_uczestnik = uk.id_uczest
where id_kurs in (1,2 )
INTERSECT
SELECT imie, nazwisko 
FROM kurs2.uczestnik u JOIN kurs2.uczest_kurs uk ON u.id_uczestnik = uk.id_uczest
where id_kurs = 3
ORDER BY 2 ;
      Lab03_query03
      Rys.4 Realizacja zapytania w DBeaver

  3. Instrukcja porównania - CASE

    1. Instrukcja CASE jest instrukcją warunkową. Na początek przedstawimy modyfikację wartości atrybutów. Jej funkcjonalność można porównać do konstrukcji IF .. THEN ... ELSE ... END w językach programistycznych. Konstrukcja polecenia CASE występuje w dwóch postaciach. W pierwszej porównujemy wartość lub zawartość kolumny z wartością po słowie THEN, w drugim przypadku wyznaczamy wartość logiczną wyrażenia. Poniżej obydwie formy instrucji CASE.
      • Porównanie z wartością lub zawartością danej kolumny. 
        SELECT a1, a2, 
    CASE wartość_lub_kolumna
       WHEN wartosc_1 THEN wynik_1
       WHEN wartosc_2 THEN wynik_2
       WHEN wartosc_3 THEN wynik_3
       [ ELSE wynik_gdy_brak_na_liscie ]
     END
  FROM relacja
      • Porównanie z wyznaczoną wartością logiczną. 
        SELECT a1, a2, 
     CASE 
       WHEN wyrazenie_logiczne_1 THEN wynik_1
       WHEN wyrazenie_logiczne_2 THEN wynik_2
       WHEN wyrazenie_logiczne_3 THEN wynik_3
       [ ELSE wynik_gdy_brak_na_liscie ]
     END
  FROM relacja
    2. Instrukcja CASE może wystąpić również w innych fragmentach zapytania SQL. Można ją użyć w ramach kluzuli WHERE i HAVING w warunku ale również w klauzuli GROUP BY i ORDER BY.
    3. Ciekawą formą zapytania jest kwerenda krzyżowa. Kwerenda krzyżowa umożliwia otrzymanie wyników w dwuwymiarowej przestrzeni. Na osi X umieszczamy jeden parametr, na osi Y drugi, wynik odczytujemy na przecięciu osi. Najczęściej jest to wynik zaregowany. Przykład kwerendy krzyżowej przedstawia poniższa tabela np. na przecięciu wartości (x,y) odczytujemy agregat np. dla wartości styczeń 2011 lub luty 2012, itd.
      123456789101112
      2010a11a21
      2011a12a22
      2012a13a23
    4. Przykłady wykorzystania instrukcji CASE w bazie danych KURS.
      Zastąpienie ocen liczbowych oceną słowną dla uczestników określonego kursu.
      SELECT imie, nazwisko,
   CASE ocena
     WHEN 3 THEN 'dostateczny'
     WHEN 4 THEN 'dobry'
     WHEN 5 THEN 'bardzo dobry'
     ELSE 'brak oceny'
   END
FROM kurs2.uczestnik u JOIN kurs2.uczest_kurs uk ON u.id_uczestnik=uk.id_uczest
WHERE id_kurs=1
ORDER BY 2,1 ;
      Lista ocen na poszczególnych kursach (kwerenda krzyżowa).
      SELECT ko.nazwa,
   SUM(CASE WHEN uk.ocena = 3 THEN 1 ELSE 0 END ) as ocena_3,
   SUM(CASE WHEN uk.ocena = 4 THEN 1 ELSE 0 END ) as ocena_4,
   SUM(CASE WHEN uk.ocena = 5 THEN 1 ELSE 0 END ) as ocena_5,
   SUM(CASE WHEN uk.ocena is null THEN 1 ELSE 0 END ) as ocena_null
FROM kurs2.kurs k JOIN kurs2.uczest_kurs uk USING (id_kurs)
            JOIN kurs2.kurs_nazwa ko on k.id_nazwa = ko.id_nazwa 
GROUP BY ko.nazwa, k.id_kurs	
ORDER BY k.id_kurs;
      Lista osób, które uzyskały ocenę 5 na kursie j.angielskiego, ocenę co najmniej 4 na kursie j.niemieckiego i ocenę co najmniej 3 na kursie j.hiszpanskiego (rys.5).
      SELECT imie, nazwisko, ko.nazwa, uk.ocena
FROM kurs2.uczestnik u JOIN kurs2.uczest_kurs uk ON u.id_uczestnik=uk.id_uczest  
                 JOIN kurs2.kurs k USING ( id_kurs)
                 JOIN kurs2.kurs_nazwa ko ON k.id_nazwa=ko.id_nazwa
WHERE uk.ocena >=
CASE 
   WHEN k.id_kurs IN (1, 2, 3, 4, 5) THEN 5
   WHEN k.id_kurs IN (6, 7, 8, 9) THEN 4
   WHEN k.id_kurs IN (10, 11) THEN 3
END 
ORDER BY 4 DESC ;
      Lab03_query04
      Rys.5 Realizacja zapytania w DBeaver

  4. Wyrażenie CTE (Common Table Expressions)

    1. Wyrażenia CTE ( wspólne wyrażenia tablicowe ) upraszczają i zwiększają przejrzystość kodu polecenia SQL. Raz zdefiniowane struktury w ramach polecenia WITH można wykorzystać wiele razy. Wyrażenia CTE można wykorzystać również w ramach widoku, funcji czy procedury składowanej. Realizacja wyrażeń CTE zaczyna się od słowa kluczowego WITH po którym następuje deklaracja tabel CTE. Widoczność zdefiniowanych tabel CTE jest w ramach wyrażenia WITH oraz zapytania związanego z wyrażeniem. Wyrażenia CTE są szczególnie przydatne w przypadku rozbudowanych zapytań, łączących wiele tabel, które chcemy użyć w kolejnym kroku, wykonując na nich dodatkowe operacje. Poniżej struktura wyrażenia CTE. 
      WITH table1_CTE [( atrybuty )] AS ( definicja zapytania )   -- definicja tabeli CTE  
     [ , table2_CTE, ... ]                                  -- definicje kolejnych tabel CTE
SELECT [ atrybuty ] FROM tables_CTE ;                       -- zapytanie do tabel CTE
    2. Przykłady wykorzystania wyrażeń CTE w bazie danych KURS.
      Prezentacja funkcjonalności polecenia WITH.
      WITH statementCTE AS ( select * from kurs2.uczestnik )
SELECT * from statementCTE;
      Kursy, które mają więcej niż 7 osób.
      WITH statementCTE AS ( select id_kurs, count(*) as num 
   from kurs2.uczest_kurs group by id_kurs )
SELECT ko.nazwa from statementCTE JOIN kurs2.kurs k USING ( id_kurs ) 
   JOIN kurs2.kurs_nazwa ko ON k.id_nazwa=ko.id_nazwa
   WHERE num > 7;
      Procentowy udział osób w poszczególnych kursach.
      WITH totalCTE as ( select count(*)::float as tot from kurs2.uczest_kurs ),
   kursCTE as ( select id_kurs, count(*)::float as num 
   from kurs2.uczest_kurs group by id_kurs ) 
SELECT ko.nazwa, ROUND((kCTE.num/(select totalCTE.tot from totalCTE))::numeric,3) as proc 
   FROM kursCTE kCTE JOIN kurs2.kurs k ON k.id_kurs = kCTE.id_kurs
                     JOIN kurs2.kurs_nazwa ko ON k.id_nazwa = ko.id_nazwa;
      Informacja o kursach prowadzonych przez wykładowców.

      Wiersze zawierają kolejnych wykładowców, kolumny kursy dostępne w bazie danych, na przecięciu posiadamy informację o prowadzeniu lub nie prowadzeniu danego kursu (rys.6).

      WITH cte AS (
SELECT id_wykl,
 (case when SUM(CASE when wk.id_kurs = 1 then 1 else  0 END) = 1 then 'x' else '' end) AS k1,
 (case when SUM(CASE when wk.id_kurs = 2 then 1 else  0 END) = 1 then 'x' else '' end) AS k2,
 (case when SUM(CASE when wk.id_kurs = 3 then 1 else  0 END) = 1 then 'x' else '' end) AS k3,
 (case when SUM(CASE when wk.id_kurs = 4 then 1 else  0 END) = 1 then 'x' else '' end) AS k4,
 (case when SUM(CASE when wk.id_kurs = 5 then 1 else  0 END) = 1 then 'x' else '' end) AS k5,
 (case when SUM(CASE when wk.id_kurs = 6 then 1 else  0 END) = 1 then 'x' else '' end) AS k6,
 (case when SUM(CASE when wk.id_kurs = 7 then 1 else  0 END) = 1 then 'x' else '' end) AS k7,
 (case when SUM(CASE when wk.id_kurs = 8 then 1 else  0 END) = 1 then 'x' else '' end) AS k8,
 (case when SUM(CASE when wk.id_kurs = 9 then 1 else  0 END) = 1 then 'x' else '' end) AS k9,
 (case when SUM(CASE when wk.id_kurs = 10 then 1 else 0 END) = 1 then 'x' else '' end) AS k10,
 (case when SUM(CASE when wk.id_kurs = 11 then 1 else 0 END) = 1 then 'x' else '' end) AS k11,
 count(wk.id_kurs) AS count
FROM  kurs2.wykl_kurs wk 
GROUP BY id_wykl )
SELECT w.nazwisko, w.imie, c.k1, c.k2, c.k3,c.k4, c.k5, c.k6, c.k7, c.k8, c.k9, c.k10, c.k11, c.count
FROM kurs2.wykladowca w JOIN cte c ON  w.id_wykladowca = c.id_wykl 
ORDER BY w.nazwisko;
      Lab03_query05
      Rys.6 Realizacja zapytania w DBeaver

  5. Widok (perspektywa, kwerenda)

    1. Widok jest wynikiem realizacji jednej lub wielu operacji na tabelach w bazie danych tworzących nową dynamiczną tabelę wynikową. Widok jest tabelą wirtualną, która nie musi fizycznie istnieć w bazie danych (w przeciwieństwie do widoków zmaterializowanych). Jest wyliczana na żądanie użytkownika. Dla użytkownika widok wygląda jak zwykła tabela. Widok tworzymy w następujących sytuacjach:
      • pozwalają uprościć złożone zapytania - brak konieczności pisania długich poleceń SELECT;
      • umożliwiają wielokrotne użycia.zdefiniowanego zapytania;
      • umożliwiają zmianę formatowania danych, np. wykorzystanie funkcji CAST;
      • ukrywanie efektów normalizacji - łączenie tabel z wykorzystaniem JOIN;
      • możemy tworzyć kolumny obliczeniowe;
      • ograniczenie dostępu do danych - usuwanie poprzez selekcję i projekcję chronionych danych;
      • tworzenie warstwy abstrakcji - przykrywanie orginalnej struktury bazy danych, zmiana nazw.
    2. Widok tworzymy poleceniem CREATE VIEW. 
      CREATE VIEW 
	<nazwa perspektywy> [<nazwa kolumny>,...] 
	AS
	<zapytanie SELECT definiujące perspektywę> 
	[ WITH [CASCADED|LOCAL]
	  CHECK OPTION]
    3. Baza danych - BIBL - serwer Pascal - utworzenie struktury tabel.

      Skrypt bibl_ddl.sql ( [listing dokumentu] [link do dokumentu] )

      CREATE SCHEMA bibl ;
--
-- Tabela  DZIAL
CREATE TABLE bibl.dzial(
  id_dzial int, 
  nazwa varchar(40),
  CONSTRAINT PK_dzial PRIMARY KEY ( id_dzial )
 ) ;
--
-- Tabela KSIAZKA
CREATE TABLE bibl.ksiazka(
    id_ksiazka int,
    tytul varchar(300) NOT NULL,
    autor varchar(300),
    isbn varchar(50),
    rok_wydania varchar(10),
    wydanie varchar(10),
  id_dzial int NOT NULL,  
  CONSTRAINT PK_ksiazka PRIMARY KEY ( id_ksiazka ),
-- Referencja tabela KSIAZKA tabela DZIAL
  CONSTRAINT FK_ksiazka_dzial FOREIGN KEY(id_dzial)
    REFERENCES bibl.dzial (id_dzial)  
) ; 
--
-- Tabela EGZEMPLARZ
CREATE TABLE bibl.egzemplarz(
   id_egzemplarz int NOT NULL,
   id_ksiazka int,
   status int,
   CONSTRAINT PK_egzemplarz PRIMARY KEY ( id_egzemplarz ),
-- Referencja tabela EGZEMPLARZ tabela KSIAZKA
   CONSTRAINT FK_egzemplarz_ksiazka FOREIGN KEY(id_ksiazka)
     REFERENCES bibl.ksiazka (id_ksiazka)   
) ;
--
-- Tabela CZYTELNIK 
CREATE TABLE bibl.czytelnik(
    id_czytelnik  int,
    imie varchar(50) NOT NULL,
    nazwisko varchar(50) NOT NULL,
    kod_pocztowy varchar(50),
    miasto varchar(50),
    adres varchar(100),
    email varchar(100),
  CONSTRAINT  PK_czytelnik  PRIMARY KEY ( id_czytelnik )      
) ;
--
-- Tabela WYPOZYCZENIE
CREATE TABLE bibl.wypozyczenie(
  id_wypozyczenie SERIAL,
    id_czytelnik int NOT NULL,
    id_egzemplarz int NOT NULL,
    data_pozyczenie date,
  data_oddania date,
  status int,  
  CONSTRAINT PK_wypozyczenie PRIMARY KEY ( id_wypozyczenie ),
-- Referencja tabela WYPOZYCZENIE tabela CZYTELNIK
  CONSTRAINT FK_wypozyczenie_czytelnik FOREIGN KEY(id_czytelnik)
    REFERENCES bibl.czytelnik (id_czytelnik), 
-- Referencja tabela WYPOZYCZENIE tabela EGZEMPLARZ
  CONSTRAINT FK_wypozyczenie_egzemplarz FOREIGN KEY(id_egzemplarz)
    REFERENCES bibl.egzemplarz (id_egzemplarz)       
)  ;
    4. Wprowadzenie danych do tabel: bibl.czytelnik, bibl.dzial i bibl.ksiazka.

      Skrypt bibl_dml1.sql ( [listing dokumentu] [link do dokumentu] )

      insert into bibl.czytelnik (id_czytelnik, imie, nazwisko,email) VALUES
 ( 1, 'Adam','Abacki','abacki@onet.pl'),
 ( 2, 'Ewa','Babacka','babacka@onet.pl'),
 ( 3, 'Edward','Cabacki','cabacki@onet.pl'),
 ( 4, 'Krystyna','Dadacka','dadacka@wp.pl'),
 ( 5, 'Miroslaw','Ebacki','ebacki@gmail.com'),
 ( 6, 'Dariusz','Kabacki','kabacki@onet.pl'),
 ( 7, 'Alicja','Lalacka','lalacka@wp.pl'),
 ( 8, 'Krzysztof','Mamacki','mamacki@gmail.com'),
 ( 9, 'Hanna','Adamczyk','adamczyk@onet.pl'),
 (10, 'Ilona','Cudna','cudna@wp.pl'),
 (11,'Joanna','Debska','debska@wp.pl'),
 (12,'Waldemar','Drygiel','drygiel@onet.pl'),
 (13,'Adam','Fronc','fronc@gmail.com'),
 (14,'Marian','Glowka','glowka@o2.pl'),
 (15,'Helena','Rokicka','rokicka@o2.pl'),
 (16,'Maria','Sabak','sabak@wp.pl'),
 (17,'Wladyslaw','Stanczyk','stanczyk@wp.pl'),
 (18,'Adam','Szczypiorek','szczypiorek@gmail.com'),
 (19,'Adam','Kowalczyk','kowalczyk@o2.pl'),
 (20,'Adam','Zawadzki','zawadzki@onet.pl'),
 (21,'Adam','Kedra','kedra@wp.pl'),
 (22,'Adam','Sobczuk','sobczuk@o2.pl'),
 (23,'Klaudia','Wiacek','wiacek@gmail.com'),
 (24,'Malwina','Wierzbicka','wierzbicka@onet.pl');
--
-- Wprowadzamy dane do tabeli BIBL.DZIAL
insert into bibl.dzial (id_dzial, nazwa) VALUES (1, 'literatura') ;
insert into bibl.dzial (id_dzial, nazwa) VALUES (2, 'informatyka') ;
--
-- Wprowadzamy dane do tabeli BIBL.KSIAZKA ( dzial literatura )
insert into bibl.ksiazka (id_ksiazka, tytul,autor,id_dzial) VALUES
 ( 1,'Duma i uprzedzenie','Jane Austen',1),
 ( 2,'Wladca Pierscieni','JRR Tolkien',1),
 ( 3,'Jane Eyre','Charlotte Bronte',1),
 ( 4,'Seria o Harrym Potterze','JK Rowling',1),
 ( 5,'Wichrowe Wzgorza','Emily Bronte',1),
 ( 6,'Rok 1984','George Orwell',1),
 ( 7,'Mroczne materie','Philip Pullman',1),
 ( 8,'Wielkie nadzieje','Charles Dickens',1),
 ( 9,'Male kobietki','Louisa M Alcott',1),
 (10,'Tessa Urberville','Thomas Hardy',1),
 (11,'Paragraf 22','Joseph Heller',1),
 (12,'Rebeka','Daphne Du Maurier',1),
 (13,'Hobbit','JRR Tolkien',1),
 (14,'Birdsong','Sebastian Faulks',1),
 (15,'Buszujacy w zbozu','JD Salinger',1),
 (16,'Zona podroznika w czasie','Audrey Niffenegger',1),
 (17,'Miasteczko Middlemarch','George Eliot',1),
 (18,'Przeminelo z wiatrem','Margaret Mitchell',1),
 (19,'Wielki Gatsby','F Scott Fitzgerald',1),
 (20,'Samotnia','Charles Dickens',1),
 (21,'Wojna i pokoj','Leo Tolstoy',1),
 (22,'Autostopem przez Galaktyke','Douglas Adams',1),
 (23,'Znowu w Brideshead','Evelyn Waugh',1),
 (24,'Zbrodnia i kara','Fyodor Dostoyevsky',1),
 (25,'Grona gniewu','John Steinbeck',1),
 (26,'Alicja w Krainie Czarow','Lewis Carroll',1),
 (27,'O czym szumia wierzby','Kenneth Grahame',1);
--
-- Wprowadzamy dane do tabeli BIBL.KSIAZKA ( dzial informatyka )
insert into bibl.ksiazka (id_ksiazka,autor,tytul,rok_wydania,id_dzial) VALUES
 (28,'Jeffrey D. Ullman, Jennifer Widom','Podstawowy wyklad z systemow baz danych','2000',2),
 (29,'C.J.Date','Wprowadzenie do systemow baz danych','2000',2),
 (30,'Jeffrey D. Ullman','Systemy baz danych','1982',2),
 (31,'Paul Beynon- Davies','Systemy baz danych','1998',2),
 (32,'Georg Lausen, Gottfried Vossen','Obiektowe bazy danych','2000',2),
 (33,'Rafe Coburn','SQL dla kazdego','2001',2),
 (34,'Martin Gruber','SQL','2000',2),
 (35,'Jerzy Roszkowski','Analiza i projektowanie strukturalne','2002',2),
 (36,'Steven Roman','Access Baza danych, Projektowanie i programowanie','2001',2),
 (37,'Rick Dobson','Programowanie Microsoft Access 2000','2000',2),
 (38,'Stephen Forte','Access 2000, Ksiega eksperta','2001',2),
 (39,'John Viescas','Podrecznik Microsoft Access 2000','2000',2),
 (40,'Richard Stones','Neil Matthew, Od podstaw bazy danych i PostgreSQL','2001',2),
 (41,'John C. Worsley, Joshua D. Drake','PosgreSQL, Praktyczny przewodnik','2002',2),
 (42,'Michal Grochala','Java aplikacje bazodanowe','2001',2),
 (43,'Luke Welling, Laura Thomson','PHP i MySQL, Tworzenie stron WWW','2002',2),
 (44,'Jim Buyens','Bazy danych w Internecie krok po kroku','2000',2),
 (45,'Mark Whitehorn, Bill Marklyn','Relacyjne bazy danych','2003',2),
 (46,'Lech Banachowski i inni','Bazy danych, Wyklady i cwiczenia','2003',2),
 (47,'Thomas Connolly, Carolyn Begg','Systemy baz danych','2004',2);
    5. Wprowadzenie danych do tabeli bibl.egzemplarz.

      Skrypt bibl_dml2.sql ( [listing dokumentu] [link do dokumentu] )

      insert into bibl.egzemplarz (id_egzemplarz,id_ksiazka,status)  VALUES
 (1,1,0),
 (2,2,0),
 (3,3,0),
 (4,4,0),
 (5,5,0),
 (6,6,0),
 (7,7,0),
 (8,8,0),
 (9,9,0),
 (10,10,0),
 (11,11,0),
 (12,12,0),
 (13,13,0),
 (14,14,0),
 (15,15,0),
 (16,16,0),
 (17,17,0),
 (18,18,0),
 (19,19,0),
 (20,20,0),
 (21,21,0),
 (22,22,0),
 (23,23,0),
 (24,24,0),
 (25,25,0),
 (26,26,0),
 (27,27,0),
 (28,28,0),
 (29,29,0),
 (30,30,0),
 (31,31,0),
 (32,32,0),
 (33,33,0),
 (34,34,0),
 (35,35,0),
 (36,36,0),
 (37,37,0),
 (38,38,0),
 (39,39,0),
 (40,40,0),
 (41,41,0),
 (42,42,0),
 (43,43,0),
 (44,44,0),
 (45,45,0),
 (46,46,0),
 (47,47,0);
    6. Wprowadzenie danych do tabeli bibl.wypozyczenie i modyfikacja tabeli bibl.egzemplarz.

      Skrypt bibl_dml3.sql ( [listing dokumentu] [link do dokumentu] )

      insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 1,  1,'2012-10-10',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=1;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 1,  3,'2012-09-10',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=3;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 1,  4,'2013-01-04',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=4;                                                   
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 2,  2,'2012-10-07',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=2;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 2,  5,'2012-08-05',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=5;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 3,  6,'2012-07-11',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=6;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 3,  9,'2012-09-15',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=9;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 5, 10,'2012-10-13',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=10;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 5, 11,'2012-11-19',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=11;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 5, 12,'2012-12-08',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=12;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 8,  7,'2013-01-18',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=7;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 8, 14,'2012-07-10',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=14;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 8, 15,'2012-09-20',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=15;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (11, 22,'2012-11-11',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=22;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (11, 24,'2012-10-01',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=24;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (11, 30,'2012-09-25',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=30;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (12, 32,'2012-10-14',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=32;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (18, 41,'2012-08-16',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=41;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (21, 28,'2012-10-16',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=28;
-- oddanie ksiazki ( wypozyczenie ponowne )
update bibl.wypozyczenie set (data_oddania,status) = ('2013-02-04',2) where id_egzemplarz=41 and id_czytelnik=18 and status=1;
update bibl.egzemplarz set status=0 where id_egzemplarz=41;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (19, 41,'2013-02-05',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=41;
-- oddanie ksiazki ( wypozyczenie ponowne )
update bibl.wypozyczenie set (data_oddania,status) = ('2012-10-04',2) where id_egzemplarz=30 and id_czytelnik=11 and status=1;
update bibl.egzemplarz set status=0 where id_egzemplarz=30;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (21, 30,'2012-10-05',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=30;
-- oddanie ksiazki ( wypozyczenie ponowne )
update bibl.wypozyczenie set (data_oddania,status) = ('2012-11-04',2) where id_egzemplarz=30 and id_czytelnik=21 and status=1;
update bibl.egzemplarz set status=0 where id_egzemplarz=30;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (1, 30,'2012-11-05',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=30;
    7. Utworzenie widoków do realizacji konkretnych funkcjonalności.

      Skrypt bibl_views.sql ( [listing dokumentu] [link do dokumentu] )

      -- czytelnicy posiadajacy wypozyczone ksiazki
create view bibl.v_czytelnik_wypozyczenia as 
 select c.nazwisko as "Nazwisko", c.imie as "Imie", count(*) as "Liczba ksiazek" 
 from bibl.czytelnik c join bibl.wypozyczenie w on c.id_czytelnik = w.id_czytelnik where w.status=1
 group by c.id_czytelnik
 order by c.nazwisko;
                                        
                                        
-- czytelnicy nie posiadajacy wypozyczonych ksiazek
create view bibl.v_czytelnik_brak_wypozyczen  as  
  select c.nazwisko as "Nazwisko", c.imie as "Imie" 
  from bibl.czytelnik c where c.id_czytelnik not in 
  ( select w.id_czytelnik from bibl.wypozyczenie w where w.status=1 )
  order by c.nazwisko;                                      
 
-- czytelnicy ktorzy nigdy nie pozyczyli ksiazek
create view bibl.v_czytelnik_nie_pozyczal   as
  select nazwisko as "Nazwisko",imie as "Imie" 
from bibl.czytelnik 
where id_czytelnik not in ( select distinct id_czytelnik from bibl.wypozyczenie ) order by nazwisko ;


-- ksiazki wypozyczone
create view bibl.v_ksiazki_pozyczone  as
  select k.tytul as "Tytul", k.autor as "Autor" 
  from bibl.egzemplarz e join bibl.ksiazka k on e.id_ksiazka=k.id_ksiazka where e.status=1
  order by k.tytul ;
 
-- ksiazki w bibliotece
create view bibl.v_ksiazki_w_bibliotece  as
  select k.tytul as "Tytul", k.autor as "Autor" 
  from bibl.egzemplarz e join bibl.ksiazka k on e.id_ksiazka=k.id_ksiazka where e.status=0
  order by k.tytul ;
    
-- liczba ksiazek w bibliotece i pozyczonych
create view bibl.v6 (opis , liczba_ksiazek ) as
 select 'Ksiazki w bibliotece', count(*) from bibl.egzemplarz where status=0 
 union
 select 'Ksiazki pozyczone', count(*) from bibl.egzemplarz where status=1 
 order by 1; 
 
-- liczba ksiazek w poszczegolnych kategoriach
create view bibl.v7 as
  select d.nazwa as "Nazwa", count(*) as "Liczba ksiazek" 
 from bibl.dzial d join bibl.ksiazka k on d.id_dzial = k.id_dzial group by d.nazwa;
  
-- liczba ksiazek w poszczegolnych kategoriach (pozyczone, w bibliotece)   
create view bibl.v8 (Kategoria, Opis ,Liczba_pozycji ) as
  select d.nazwa,
         case when e.status=0 then 'W bibliotece' 
              when e.status=1 then 'Pozyczone' end,
         count(*)
  from bibl.dzial d join bibl.ksiazka k on d.id_dzial = k.id_dzial
                    join bibl.egzemplarz e on k.id_ksiazka = e.id_ksiazka 
  group by d.nazwa,e.status order by d.nazwa,e.status;            

  
-- ksiazki najczesciej wypozyczane
create view bibl.v9 as
  select k.tytul as "Tytul", k.autor as "Autor", count(*) as "Liczba wypozyczen"
        from bibl.ksiazka k join bibl.egzemplarz e on k.id_ksiazka = e.id_ksiazka 
                            join bibl.wypozyczenie w on e.id_egzemplarz = w.id_egzemplarz 
                                    group by k.id_ksiazka 
                                    having count(*) > 1
                                    order by "Liczba wypozyczen" desc;    
                                    
-- ksiazki nigdy nie pozyczone
create view bibl.v10 as
  select k.tytul as "Tytul", k.autor as "Autor" from bibl.ksiazka k 
  where k.id_ksiazka not in 
  ( select e.id_ksiazka from bibl.wypozyczenie w join bibl.egzemplarz e on w.id_egzemplarz = e.id_egzemplarz  ) ;   
  
--

Zadania do realizacji

  1. Zadanie 1 - zrealizować przykłady przedstawione na zajęciach.
  2. Zadanie 2 - przygotować zapytania do bazy "Kurs v.2.1".
    • Statystyka kursu - udział uczestnika w kolejnych edycjach kursu.
      • Imie;
      • Nazwisko;
      • k1, ... , k11 - numer kursu;
      • Dodatkowo w ostatniej kolumnie zawarta jest liczbę kursów, w których dana osoba uczestniczyła;
      • Lista posortowana po nazwisku uczestnika;
      • Wiersze zawierają informację o uczestniku, kolumny informację o kursie. Na przecięciu otrzymujemy informację o obecności uczestnika na kursie.
    • Realizacja zadania przedstawiona została na rys.7.
      Lab3_Zadanie
      Rys.7. Realizacja zapytania przdstawionego w punkcie 2.