Kolokwium z Baz Danych, 2008.10.30

Struktura bazy danych:

create table aktorzy (
  pesel    varchar(11) primary key,
  imie     varchar(16) not null,
  nazwisko varchar(32) not null,
  data_ur  date not null
);

create table seriale (
  id       integer primary key,
  nazwa    varchar(32) not null
);

create table odcinki (
  serial   integer references seriale not null,
  numer    integer not null,
  nazwa    varchar(32) not null,
  data     date not null, /* data emisji */
  primary key (serial,numer)
);

create table obsada (
  serial        integer not null,
  numer_odcinka integer not null,
  aktor    varchar(11) references aktorzy not null,
  rola     varchar(16) not null,
  foreign key (serial,numer_odcinka) 
    references odcinki (serial,numer),
  primary key (serial,numer_odcinka,aktor,rola)
);

Baza zawiera informacje o aktorach, serialach i ich odcinkach oraz obsadzie.

Napisz następujące zapytania SQL:

1. lista odcinków (w formacie: nazwa serialu, numer odcinka, nazwa odcinka, data emisji w postaci yyyy-mm-dd), posortowana rosnąco wg nazwy serialu, numeru odcinka,
2. lista seriali, oraz liczba odcinków, data pierwszego odcinka, data ostatniego odcinka (w formacie: nazwa serialu, liczba odcinków, data pierwszego odcinka w postaci dd-mm-yyyy, data ostatniego odcinka w postaci dd-mm-yyyy), posortowana malejąco wg daty ostatniego odcinka,
3. lista peseli aktorów, którzy występowali w jednym odcinku (dowolnego serialu) w więcej niż jednej roli, posortowana malejąco,
4. lista aktorów wraz z informacją o liczbie seriali w których występują (w formacie imię, nazwisko, liczba seriali), posortowana rosnąco wg nazwiska, imienia.
5. dla każdego serialu wypisz najmłodszego aktora (lub aktorów), którzy w nim występują (w formacie: nazwa serialu, pesel aktora), uporządkowanie: rosnąco wg nazwy serialu,
6. dla każdego serialu wypisz aktora (lub aktorów), którzy występowali w największej liczbie odcinków (w formacie: nazwa serialu, pesel aktora, liczba odcinków), uporządkowanie: rosnąco wg nazwy serialu,
7. dla każdego aktora wypisz jak długo występował w (dowolnych) serialach (okres od pierwszego do ostatniego wystąpienia w serialu, jeśli aktor nie występował w żadnym serialu to NULL), (w formacie: imię, nazwisko, czas występowania w postaci y-m-d) uporządkowanie: rosnąco wg nazwiska, imienia.
8. dla każdego serialu wypisz pesel aktorów, którzy występowali w każdym odcinku (co najmniej jednym), (w formacie: nazwa serialu, pesel), uporządkowanie: rosnąco wg. nazwy serialu, pesela.
9. dla każdego aktora wypisz maksymalną liczbę seriali w których występował jednocześnie (przyjmujemy, że występowanie w serialu oznacza okres od pierwszego występu w serialu do ostatniego), (w formacie: pesel aktora, liczba seriali) uporządkowanie: wg. malejącej liczby seriali, rosnąco wg. pesela.
10. dla każdego serialu i występujących w nim aktorów, wypisz maksymalną liczbę kolejnych odcinków w których aktor występował (w formacie: nazwa serialu, imię aktora, nazwisko aktora, liczba odcinków) uporządkowanie: wg. nazwy serialu, nazwiska aktora, imienia aktora.

Dodatkowe założenia:

• serial może nie mieć żadnych odcinków,
• aktor może nie grac w żadnym serialu,
• numeracja odcinków może nie być spójna (np. 3, 5, 20).

Zasady

Rozwiązania należy przesłać na adres: walen (małpa) mimuw.edu.pl, w nieprzekraczalnym terminie 2007.10.30 15:45. Rozwiązanie powinno składać się z jednego pliku tekstowego przesłanego jako treść listu (nie załącznik) w formacie:

/* Autor:  Imię Nazwisko */
/* Indeks: nr */

/* zadania 1 */
SELECT ... {rozwiązanie zadania 1}

/* zadanie 2 */
SELECT ... {rozwiązanie zadania 2}

/* zadanie 3 */
SELECT ... | /* BRAK */

...itd...

Rozwiązania

rozw.sql

Wyniki

wyniki