Zadania ćwiczebne z SQL - odpowiedzi

Dla przypomnienia schemat bazy danych:

• Ksiazki(nrk,tytul,autor,wydawca,rok_wyd,cena)
• Czytelnicy(nrcz,nazwisko,imie,zawod)
• Wypozyczenia(nrk,nrcz,data_wyp,data_zwr)
  Klucz główny jest trójelementowy. Klucze obce:
  • nrk: do Ksiazki
  • nrcz: do Czytelnicy.
  Pole data_zwr może być puste (NULL) -- oznacza to, że książki jeszcze nie zwrócono.

1. Którzy czytelnicy wypożyczyli w tym roku najwięcej książek?

   CREATE VIEW Ten_Rok AS
   SELECT nrcz,COUNT(*) AS ile
   FROM Wypozyczenia
   WHERE EXTRACT(year FROM data_wyp) = EXTRACT(year FROM current_date) 
   GROUP BY nrcz;
   
   SELECT Czytelnicy.nrcz,imie,nazwisko
   FROM Ten_Rok JOIN Czytelnicy ON Ten_Rok.nrcz = Czytelnicy.nrcz
   WHERE ile = (SELECT MAX(ile) FROM Ten_Rok); 
   

   Zakładamy, że jeśli czytelnik wypożyczył tę samą książkę kilka razy, to liczy się ona kilkakrotnie. W przeciwnym razie trzeba zamiast COUNT(*) dać COUNT(DISTINCT nrk).

2. Która obecnie wypożyczona książka jest najdłużej trzymana i przez kogo (może być kilka takich książek --- należy podać wszystkie)?

   SELECT autor,tytul,imie,nazwisko
   FROM Wypozyczenia JOIN Ksiazki ON Wypozyczenia.nrk = Ksiazki.nrk
        JOIN Czytelnicy ON Wypozyczenia.nrcz = Czytelnicy.nrcz
   WHERE data_zwr IS NULL
     AND data_wyp = (SELECT MIN(data_wyp)
                     FROM Wypozyczenia
                     WHERE data_zwr IS NULL);
   

3. Jaki jest procent przebywania poszczególnych książek poza biblioteką?

   Niestety nie znamy daty zakupu książki (kiedyś była kolumna data_zakupu w tabeli Ksiazki, ale jakoś zginęła). Przyjmiemy więc, że dla każdej książki mierzymy czas od daty pierwszego wypożyczenia.

   SELECT Ksiazki.nrk,tytul,
          100 * (SUM(COALESCE(data_zwr - data_wyp, CURRENT_DATE - data_wyp))) / 
          (CURRENT_DATE - MIN(data_wyp)) || '%' AS "Poza"
   FROM Ksiazki JOIN Wypozyczenia ON Ksiazki.nrk = Wypozyczenia.nrk
   GROUP BY Ksiazki.nrk,tytul;
   

4. Podaj numery katalogowe i tytuły pięciu (lub więcej, jeśli jest ,,remis'') książek o największej liczbie wypożyczeń.

   CREATE VIEW Ile_Wypozyczen AS
   SELECT nrk,COUNT(*) AS ile
   FROM Wypozyczenia
   GROUP BY nrk;
   
   SELECT tytul,Ksiazki.nrk
   FROM Ile_Wypozyczen JOIN Ksiazki ON Ile_Wypozyczen.nrk = Ksiazki.nrk
   WHERE ile in (SELECT ile
                 FROM Ile_Wypozyczen
                 ORDER BY ile DESC LIMIT 5); 
   

5. Kto czytał najdroższą książkę wydaną przed 1989 rokiem (może być kilka takich książek -- podaj czytelników dla wszystkich)?

   CREATE VIEW Stare_ksiazki AS
   SELECT * FROM Ksiazki  
   WHERE rok_wyd < 1989;
   
   SELECT imie,nazwisko
   FROM Wypozyczenia NATURAL JOIN Stare_ksiazki NATURAL JOIN Czytelnicy
   WHERE cena = (SELECT MAX(cena)
                        FROM Stare_ksiazki);