/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ęć)

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);

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

select KOD, NAZWA from BUDYNKI order by KOD;

2. Podać ID, NUMER i LICZBA_MIEJSC dla wszystkich sal dydaktycznych (TYP='D') w budynku o danym kodzie, według malejącej liczby miejsc

select ID, NUMER, LICZBA_MIEJSC from SALE where TYP='D' and BUD_KOD=?
order by LICZBA_MIEJSC desc;

3. Podać KOD i NAZWA przedmiotów, dla których odbywają się jakieś zajęcia w danym cyklu (np. '2011Z')

select KOD, NAZWA from PRZEDMIOTY where exists (
        select 1 from ZAJECIA_CYKLI where PRZ_KOD=p.KOD and CDYD_KOD=?);

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

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=?;

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)

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;

Integracja z Pythonem

1. Napisać procedurę, która formatuje zbiór wynikowy w postaci czytelnie się prezentującej na terminalu
2. 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