Kolokwium z Baz Danych, 2007.11.06

Struktura bazy danych: kol.sql.

create table czesci (
    id int primary key,
    nazwa varchar(16),
    rodzaj varchar(16), /* np. cpu, karta graficzna, */
    cena number(10,2), /* koszt jednej części */
    magazyn int /* liczba elementów w magazynie, może być NULL */
);

create table zestawy (
    id int primary key,
    nazwa varchar(16),
    cena number(10,2) /* cena którą klient musi zapłacić za cały zestaw */
);

create table elementy_zestawow (
    zestaw int references zestawy,
    czesc int references czesci,
    liczba int,
    constraint c_liczba check (liczba>0 AND liczba <10),
    unique(zestaw,czesc)
);

Baza przedstawia system magazynowy pewnej firmy sprzedającej zestawy komputerowe.

Napisz następujące zapytania SQL:

1. Wypisz dla każdego rodzaju części minimalną i maksymalną cenę części tego rodzaju.
2. Wypisz części które występują w tabeli czesci, ale brak ich w magazynie.
3. Wypisz id, nazwę, cenę oraz koszt zestawów (przez koszt rozumiemy sumę kosztów elementów zestawu).
4. Wypisz id i nazwę zestawów, które mogą być wykonane w części znajdujących się w magazynie (pamięta o sprawdzeniu czy w magazynie jest odpowiednia liczba części).
5. Wypisz pary zestawów, które składają się z dokładnie takiego samego zbioru części (pomijamy liczbę części).
6. Wypisz części, które nie są wykorzystywane w żadnym zestawie ale znajdują się w magazynie.
7. Wypisz id i nazwę części oraz liczbę zestawów w których jest ona wykorzystywana.
8. Wypisz id i nazwę zestawu, oraz liczbę różnych części, które należy dokupić do magazynu, aby było możliwe wykonanie zestawu (np. jeśli brakuje 5 części o id 100 i 6 części o id 101, to odpowiedzią jest 2).
9. Wypisz id i nazwę zestawu, oraz liczbę różnych rodzajów części wchodzących w jego skład.
10. Wypisz id i nazwy zestawów, które można wykonać przy założeniu, że możemy wymienić brakujące części przez inne (ale tego samego rodzaju i w tej samej ilości). Pamiętaj, że w skład zestawu może wchodzić więcej niż jedna część jednego rodzaju.

Zasady

Rozwiązania należy przesłać na adres: walen (małpa) mimuw.edu.pl, w nieprzekraczalnym terminie 2007.11.06 23:59. 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 */