Tekst źródłowy strony /Cw11

= [[TI:WTBD]]/Ćwiczenia 11 =

Na tych ćwiczeniach omawialiśmy zadania ze Sprawdzianu 1, i kontynuowaliśmy trenowanie SQL na danych o rozkładzie zajęć -- przykłady zapytań z wielokrotnymi złączeniami.

== Definicja danych bazy ''plan'' (plan zajęć) ==
<source lang="sql">
create table PRZEDMIOTY (KOD primary key, 
                         NAZWA, 
                         OPIS);
create table ZAJECIA_CYKLI (ID int primary key, 
                            PRZ_KOD references PRZEDMIOTY, 
                            CDYD_KOD, 
                            TZAJ_KOD, 
                            LICZBA_GODZIN int);
create table TERMINY (ID int primary key, 
                      DZIEN_TYGODNIA, 
                      GODZINA_POCZATKU int, 
                      MINUTA_POCZATKU int, 
                      GODZINA_KONCA int, 
                      MINUTA_KONCA int);
create table BUDYNKI (KOD primary key, 
                      NAZWA, 
                      DLUGOSC_GEO real, 
                      SZEROKOSC_GEO real);
create table SALE (ID int primary key, 
                   LICZBA_MIEJSC int, 
                   BUD_KOD references BUDYNKI, 
                   NUMER, 
                   TYP);
create table TERMINY_GRUP (ID int primary key, 
                           CZESTOTLIWOSC, 
                           GR_NR, 
                           TRM_ID int references TERMINY, 
                           SL_ID int references SALE, 
                           ZAJ_CYK_ID int references ZAJECIA_CYKLI);
</source>

== Przykłady zapytań ==

Mamy na ogół do czynienia z przykładami zapytań ''sparametryzowanych'', tzn. o wyniku zależnym od wartości jednego lub więcej parametrów, które należy podać (jako wartości literalne). Np. w przykładzie 4 parametrami są kod przedmiotu i kod cyklu dydaktycznego. Poniżej w miejsce konkretnych wartości tych parametrów stawiamy znaki zapytania; gdy użyjemy tekstu takiego zapytania jako argumentu wywołania np. metody ''cursor.execute()'', przekazując jako drugi argument -- sekwencję składającą się z konkretnych wartości parametrów, to w miejsce znaków zapytania zostaną podstawione kolejne elementy tej sekwencji, z zachowaniem reguł składni SQL (np. apostrofy wokół stałych napisowych).

1. Podać kod i nazwę wszystkich budynków uwzględnionych w bazie
<source lang="sql">
select KOD, NAZWA from BUDYNKI order by KOD;
</source>
2. Podać ID, NUMER i LICZBA_MIEJSC dla wszystkich sal dydaktycznych (TYP='D') w budynku o danym kodzie, według malejącej liczby miejsc
<source lang="sql">
select ID, NUMER, LICZBA_MIEJSC from SALE where TYP='D' and BUD_KOD=?
order by LICZBA_MIEJSC desc;
</source>
3. Podać KOD i NAZWA przedmiotów, dla których odbywają się jakieś zajęcia w danym cyklu (np. '2011Z')
<source lang="sql">
select KOD, NAZWA from PRZEDMIOTY where exists (
        select 1 from ZAJECIA_CYKLI where PRZ_KOD=p.KOD and CDYD_KOD=?);
</source>
4. Podać terminy (dzień tygodnia, czas początku i końca) oraz miejsca (numer sali) dla wszystkich zajęć przedmiotu o danym kodzie odbywających się w danym cyklu
<source lang="sql">
select z.TZAJ_KOD, tg.GR_NR,
    t.GODZINA_POCZATKU, t.MINUTA_POCZATKU,
    t.GODZINA_KONCA, t.MINUTA_KONCA, 
    s.NUMER, b.NAZWA
from TERMINY_GRUP tg 
    join TERMINY t on t.ID=tg.TRM_ID
    join ZAJECIA_CYKLI z on z.ID=tg.ZAJ_CYK_ID
    join SALE s on s.ID=tg.SL_ID
    join BUDYNKI b on b.KOD=s.BUD_KOD
where z.PRZ_KOD=?
    and z.CDYD_KOD=?;
</source>
5. Podać rozkład zajęć (czas początku i końca zajęć, typ zajęć i przedmiot) odbywających się w danej sali, w danym dniu tygodnia i cyklu dydaktycznym
   Ćwiczenie dla czytelnika :)
6. Podać liczbę godzin zajęć, jakie tygodniowo odbywają się w danej sali (w danym cyklu dydaktycznym)
<source lang="sql">
select s.ID, s.NUMER, 
    sum((t.GODZINA_KONCA - t.GODZINA_POCZATKU)*60
        + t.MINUTA_KONCA - t.MINUTA_POCZATKU) / 60. as GODZINY_ZAJEC
from TERMINY_GRUP tg 
    join TERMINY t on t.ID=tg.TRM_ID
    join ZAJECIA_CYKLI z on z.ID=tg.ZAJ_CYK_ID
    join SALE s on s.ID=tg.SL_ID
where s.ID=?
    and z.CDYD_KOD=?
group by s.ID, s.NUMER;
</source>

== Integracja z Pythonem ==
# Napisać procedurę, która formatuje zbiór wynikowy w postaci czytelnie się prezentującej na terminalu
# Napisać program, który obsługuje kilka spośród powyższych zapytań (sparametryzowanych), biorąc jako argumenty -- hasło (umowne określenie zapytania) i jego parametry (np. kod przedmiotu, którego rozkład chcemy poznać itp.), a zbiór wynikowy prezentuje z wykorzystaniem powyższej procedury