#
#  Query compito in classe di Aprile - V Alt
#

#   a)	elencare i DVD presenti in totale nella mediateca
SELECT *
FROM Media INNER JOIN Tipo ON Media.Id_Tipo = tipo.Id_Tipo
WHERE tipo.Descrizione = 'DVD'

#  b)	elencare i CD in prestito
SELECT Media.Titolo
FROM (Media INNER JOIN Tipo ON Media.Id_Tipo = Tipo.Id_Tipo)
INNER JOIN Prestiti ON Media.Id_Media = Prestiti.Id_Media
WHERE  tipo.descrizione = 'CD' AND Datatermine IS NULL

#   c) determinare il numero di DVD in prestito
SELECT COUNT(*) As DVD_In_Prestito
FROM (Media INNER JOIN Tipo ON Media.Id_Tipo = Tipo.Id_Tipo)
INNER JOIN Prestiti ON Media.Id_Media = Prestiti.Id_Media
WHERE tipo.Descrizione = 'DVD' AND Prestiti.Datatermine IS NULL

#   d) determinare il numero di studenti che hanno preso in prestito
#      un DVD di cui e' dato il codice
SELECT COUNT(DISTINCT Studenti.Matricola) AS Numero_Studenti
FROM ((media INNER JOIN prestiti ON media.Id_Media = prestiti.Id_Media)
INNER JOIN studenti ON studenti.Matricola = prestiti.Matricola)
INNER JOIN tipo ON media.Id_Tipo = tipo.Id_Tipo
WHERE Tipo.Descrizione = 'DVD' AND Media.Id_Media = 1

#  e) determinare il numero di CD presenti in generale nella mediateca
SELECT SUM(Media.Copie) AS NumeroCD 
FROM Media INNER JOIN Tipo ON Media.Id_Tipo = Tipo.Id_Tipo
WHERE Tipo.Descrizione = 'CD'

#  f)	identificare il libro pił prestato del corrente anno solare   ("best-seller")

# individua i libri che hanno come numero di prestiti un numero
# equivalente a quello massimo trovato in precedenza...
SELECT Media.*
FROM (Media INNER JOIN Tipo ON Media.Id_Tipo = Tipo.Id_Tipo)
INNER JOIN Prestiti ON Media.Id_Media = Prestiti.Id_Media
WHERE  tipo.descrizione = 'Libro'
GROUP BY Media.Titolo
HAVING COUNT(*) =
# individua il numero massimo di prestiti precedentemente trovati
(SELECT MAX(T1.NPrestiti)
FROM
( # individua il numero di prestiti dei vari libri  (solo conteggio prestiti)
SELECT COUNT(*) AS NPrestiti
FROM (Media INNER JOIN Tipo ON Media.Id_Tipo = Tipo.Id_Tipo)
INNER JOIN Prestiti ON Media.Id_Media = Prestiti.Id_Media
WHERE  tipo.descrizione = 'Libro'
GROUP BY Media.Titolo) AS T1 )

#   g) indicare il periodo di prestito pił lungo di un certo media  

# deriva i dati del media (id = 7), rilevando di nuovo tutte le lunghezze
# dei prestiti, ma prendendo solo quella pari al massimo di questo valore
SELECT Media.Titolo, DATEDIFF(Prestiti.DataTermine, Prestiti.DataInizio) AS LunghezzaPrestito
FROM (Media INNER JOIN Tipo ON Media.Id_Tipo = Tipo.Id_Tipo)
INNER JOIN Prestiti ON Media.Id_Media = Prestiti.Id_Media
WHERE  Media.Id_Media = 7 AND DATEDIFF(Prestiti.DataTermine, Prestiti.DataInizio) =

# trova la massima lunghezza di prestito tra quelle registrate per il media (id = 7)
(SELECT MAX(T1.TempoPrest) AS TempoMaxPrestito
FROM
# trova tutte le lunghezze dei prestiti per un certo media (in questo caso di Id pair a 7)
(SELECT DATEDIFF(Prestiti.DataTermine, Prestiti.DataInizio) AS TempoPrest
FROM (Media INNER JOIN Tipo ON Media.Id_Tipo = Tipo.Id_Tipo)
INNER JOIN Prestiti ON Media.Id_Media = Prestiti.Id_Media
WHERE  Media.Id_Media = 7) AS T1)