Laboratorium 3: Kolokwium A

Kolokwium 1

Baza zawiera dane o pewnej firmie. Są to informacje o pracownikach i ich bezpośrednich szefach (Pracownicy). Tabela ListaObcenosci zawiera dane o listach obecności, które codziennie są uzupełniane przez pracowników, którzy zapisują w nich swoje przyjście i wyjście z pracy (można założyć, że wejscie\(<\)wyjscie). Tabele Projekty i UczestnicyProjektow zawieraj¡ informacje o projektach, które są rozwijane w firmie.

CREATE TABLE Pracownicy ( 
  id number PRIMARY KEY, 
  imie varchar(10) NOT NULL, 
  nazwisko varchar(10) NOT NULL, 
  pensja number DEFAULT 0 NOT NULL, 
  szef number REFERENCES Pracownicy
);
CREATE TABLE ListaObecnosci ( 
  pracownik number REFERENCES Pracownicy NOT NULL, 
  wejscie date NOT NULL, 
  wyjscie date
);
CREATE TABLE Projekty ( 
  id number PRIMARY KEY, 
  nazwa varchar(10) NOT NULL, 
  szef number REFERENCES Pracownicy, 
  rozpoczecie date NOT NULL, 
  zakonczenie date NOT NULL
);
CREATE TABLE UczestnicyProjektow ( 
  projekt number REFERENCES Projekty NOT NULL, 
  pracownik number REFERENCES Pracownicy NOT NULL, 
  PRIMARY KEY (projekt,pracownik)
);

Napisz następujące zapytania SQL:

  1. Wypisać zestawienie: imię, nazwisko, pensję pracownika, oraz łączną liczbę projektów, w których bierze (lub brał) on udział. Proszę pamiętać, również o tych pracownikach, którzy mają tę liczbę równą 0. Przez biorącego udział w projekcie rozumiemy osobę, dla której istnieje odpowiedni wpis w tabeli UczestnicyProjektow.
  2. Wypisać imię i nazwisko pracownika (lub pracowników jeśli będzie takich osób więcej) biorącego udział w największej liczbie projektów.
  3. Wypisać id, imię i nazwisko tych osób, które nigdy nie pojawiły się w pracy.
  4. Wypisać pracowników "oszustów" (imię + nazwisko), tzn. takich, dla których istnieją co najmniej dwa różne wpisy w ListaObecnosci obejmujące okresy mające niezerową część wspólną.
  5. Wypisać dla każdego pracownika (nawet "oszusta") łączną liczbę godzin przez niego przepracowanych (ale licząc już wg sprawiedliwych zasad, czyli "wielokrotnaobecność liczy się tylko raz). Zestawienie powinno obejmować: imię, nazwisko i liczbę godzin.
  6. Wypisać pracowników (imię + nazwisko), którzy mają co najmniej dwóch podwładnych (pośrednich lub bezpośrednich).
  7. Wypisać imiona, nazwiska pracowników, łączną liczbę ich bezpośrednich i pośrednich podwładnych.
  8. Wypisać maksymalny okres (tzn. początek i koniec tego okresu), w którym nikogo nie było w pracy (na podstawie tabeli ListaObecnosci). Przyjmujemy, że zajmujemy się jedynie okresami zawartymi w 1.01.2007-31.12.2007.

Kolokwium 2

Struktura bazy danych:

DROP TABLE elementy_zestawow;
DROP TABLE czesci;
DROP TABLE zestawy;
 
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.