Esercizi su SQL

Esercizi su SQL Dato il seguente schema di una base di dati: Romanzo(ID, Autore, Titolo, AnnoPubblicazione, Prezzo, Scheda) Autore(ID, Cognome, Nome, Nazionalità, DataNascita, LuogoNascita, DataMorte, LuogoMorte) Personaggio(ID, Nome, Cognome, Descrizione) PersonaggioRomanzo(Personaggio, Romanzo, Descrizione) Scrivere le interrogazioni SQL che soddisfano le seguenti richieste: 1. Romanzi pubblicati nel 2009 2. Romanzi pubblicati a partire dal 1990 3. Romanzi del ventesimo secolo 4. Autore (cioè Nome e Cognome) e Titolo di tutti i romanzi 5. Autore (cioè Nome e Cognome) e Titolo di tutti i romanzi, ordinati per cognome e nome dell’autore 6. Autore (cioè Nome e Cognome)e Titolo dei romanzi di autori russi, ordinati per cognome e nome dell’autore 7. Titolo dei romanzi di autori nati nel ventesimo secolo, ordinati per cognome e nome dell’autore e – a parità di autore – di data di pubblicazione 8. Titolo e anno di pubblicazione di romanzi di autori viventi 9. Romanzi di scrittori viventi, oppure morti ma non a Torino 10. Titolo e anno di pubblicazione dei romanzi di scrittori nati a Roma 11. Romanzi con titolo che inizia per “Tokyo” 12. Romanzi con la parola “Sposi” nel titolo 13. Romanzi con penultima lettera “i” nel titolo 14. Romanzi il cui titolo termina per “blues” 15. Romanzi pubblicati del ventesimo secolo e con la parola “Zeno” nel titolo 16. Romanzi pubblicati prima del 1900 e con la parola “Sposi” nel titolo, scritta l’iniziale maiuscola o minuscola 17. Romanzi di Alessandro Manzoni 18. Titoli dei romanzi scritti da Don DeLillo, escludendo eventuali duplicati 19. Romanzi con il personaggio di Benjamin Malaussène 20. Prezzo totale dei romanzi con il personaggio di Harry Potter 21. Numero dei romanzi pubblicati nel 2009 22. Numero dei romanzi pubblicati in ogni anno 23. Numero dei romanzi pubblicati per ogni anno negli anni ‘90 24. L’anno di pubblicazione del primo e dell’ultimo romanzo di Dave Eggers 25. Per ogni autore americano, l’anno di pubblicazione del primo e dell’ultimo romanzo 26. *Per ogni personaggio che compare in più di un romanzo, il nome e cognome del personaggio e il numero di romanzi in cui compare 27. *Titoli dei romanzi in cui compaiono al massimo 5 personaggi 28. *Titolo e prezzo del romanzo (o dei romanzi) che ha (hanno) il prezzo più alto 29. I cognomi degli autori nati nel 1950 e i titoli dei romanzi pubblicati nello stesso anno, in un’unica tabella 30. Autori (cioè Nome e Cognome) che sono anche personaggi di un romanzo 31. Autori (cioè Nome e Cognome) che non sono personaggi di un romanzo 32. Romanzi il cui autore non è presente nel database © Luca Anselma 2009 1 1. Romanzi pubblicati nel 2009 select *
from Romanzo
where AnnoPubblicazione=2009
2. Romanzi pubblicati a partire dal 1990 select *
from Romanzo
where Anno Pubblicazione>=1990
3. Romanzi del ventesimo secolo select *
from Romanzo
where AnnoPubblicazione>=1901 and AnnoPubblicazione<=2000
4. Autore (cioè Nome e Cognome) e Titolo di tutti i romanzi select Nome, Cognome, Titolo
from Romanzo join Autore A on (Autore=A.ID)
5. Autore (cioè Nome e Cognome) e Titolo di tutti i romanzi, ordinati per cognome e nome dell’autore select Nome, Cognome, Titolo
from Romanzo join Autore A on (Autore=A.ID)
order by Cognome, Nome
6. Autore (cioè Nome e Cognome) e Titolo dei romanzi di autori russi, ordinati per cognome e nome dell’autore select Nome, Cognome, Titolo
from Romanzo join Autore A on (Autore=A.ID)
where Nazionalità=’Russa’
order by Cognome, Nome
7. Titolo dei romanzi di autori nati nel ventesimo secolo, ordinati per cognome e nome dell’autore e – a parità di autore – di data di pubblicazione select Titolo
from Romanzo join Autore A on (Autore=A.ID)
where DataNascita>=1/1/1901 and DataNascita<=31/12/2000
order by Cognome, Nome, AnnoPubblicazione
8. Titolo e anno di pubblicazione di romanzi di autori viventi select Titolo, AnnoPubblicazione
from Romanzo join Autore A on (Autore=A.ID)
where DataMorte is null
© Luca Anselma 2009 2 9. Romanzi di scrittori viventi, oppure morti ma non a Torino select R.*
from Romanzo R join Autore A on (Autore=A.ID)
where DataMorte is null or LuogoMorte<>’Torino’
10. Titolo e anno di pubblicazione dei romanzi di scrittori nati a Roma select Titolo, AnnoPubblicazione
from Romanzo join Autore A on (Autore=A.ID)
where LuogoNascita=’Roma’
11. Romanzi con titolo che inizia per “Tokyo” select *
from Romanzo
where Titolo like ‘Tokyo%’
12. Romanzi con la parola “Sposi” nel titolo select *
from Romanzo
where Titolo like ‘%Sposi%’
13. Romanzi con penultima lettera “i” nel titolo select *
from Romanzo
where Titolo like ‘%i_’
14. Romanzi il cui titolo termina per “blues” select *
from Romanzo
where Titolo like ‘%blues’
15. Romanzi pubblicati del ventesimo secolo e con la parola “Zeno” nel titolo select *
from Romanzo
where AnnoPubblicazione>=1901 and AnnoPubblicazione<=2000 and
Titolo like ‘%Zeno%’
16. Romanzi pubblicati prima del 1900 e con la parola “Sposi” nel titolo, scritta l’iniziale maiuscola o minuscola select *
from Romanzo
where AnnoPubblicazione<1900 and (Titolo like ‘%Sposi%’ or
Titolo like ‘%sposi%’)
© Luca Anselma 2009 3 17. Romanzi di Alessandro Manzoni select R.*
from Romanzo join Autore A on (Autore=A.ID)
where Nome=’Alessandro’ and Cognome=’Manzoni’
18. Titoli dei romanzi scritti da Don DeLillo, escludendo eventuali duplicati select distinct Titolo
from Romanzo join Autore A on (Autore=A.ID)
where Nome=’Don’ and Cognome=’DeLillo’
19. Romanzi con il personaggio di Benjamin Malaussène select R.*
from (Romanzo R join PersonaggioRomanzo PR on
(PR.Romanzo=R.ID)) join Personaggio P on
(PR.Personaggio=P.ID)
where Nome=’Benjamin’ and Cognome=’Malaussène’
20. Prezzo totale dei romanzi con il personaggio di Harry Potter select sum(Prezzo)
from (Romanzo R join PersonaggioRomanzo PR on
(PR.Romanzo=R.ID)) join Personaggio P on
(PR.Personaggio=P.ID)
where Nome=’Harry’ and Cognome=’Potter’
21. Numero dei romanzi pubblicati nel 2009 select count(*)
from Romanzo
where AnnoPubblicazione=2009
22. Numero dei romanzi pubblicati in ogni anno select AnnoPubblicazione, count(*)
from Romanzo
group by AnnoPubblicazione
23. Numero dei romanzi pubblicati per ogni anno negli anni ‘90 select AnnoPubblicazione, count(*)
from Romanzo
where AnnoPubblicazione>=1990 and AnnoPubblicazione<=1999
group by AnnoPubblicazione
24. L’anno di pubblicazione del primo e dell’ultimo romanzo di Dave Eggers select min(AnnoPubblicazione), max(AnnoPubblicazione)
© Luca Anselma 2009 4 from Romanzo join Autore A on (Autore=A.ID)
where Nome=’Dave’ and Cognome=’Eggers’
25. Per ogni autore americano, l’anno di pubblicazione del primo e dell’ultimo romanzo select Cognome, Nome, min(AnnoPubblicazione),
max(AnnoPubblicazione)
from Romanzo join Autore A on (Autore=A.ID)
where Nazionalità=”Americana”
group by Cognome, Nome
26. Per ogni personaggio che compare in più di un romanzo, il nome e cognome del personaggio e il numero di romanzi in cui compare select Nome, Cognome, count(*)
from (Romanzo R join PersonaggioRomanzo PR on
(PR.Romanzo=R.ID)) join Personaggio P on
(PR.Personaggio=P.ID)
group by Cognome, Nome
having count(*)>1
27. Titoli dei romanzi in cui compaiono al massimo 5 personaggi select Titolo
from (Romanzo R join PersonaggioRomanzo PR on
(PR.Romanzo=R.ID))
group by R.ID, Titolo
having count(*)<=5
28. *Titolo e prezzo del romanzo (o dei romanzi) che ha (hanno) il prezzo più alto select Titolo, Prezzo
from Romanzo
where Prezzo=( select max(Prezzo)
from Romanzo)
29. I cognomi degli autori nati nel 1950 e i titoli dei romanzi pubblicati nello stesso anno, in un’unica tabella select Titolo
from Romanzo
where AnnoPubblicazione=1950
union
select Cognome
from Autore
where DataNascita>=1/1/1950 and DataNascita<=31/12/1950
30. Autori (cioè Nome e Cognome) che sono anche personaggi di un romanzo select Nome, Cognome
© Luca Anselma 2009 5 from Autore
intersect
select Nome, Cognome
from Personaggio
31. Autori (cioè Nome e Cognome) che non sono personaggi di un romanzo select Nome, Cognome
from Autore
except
select Nome, Cognome
from Personaggio
32. Romanzi il cui autore non è presente nel database select R.*
from Romanzo left join Autore A on (Autore=A.ID)
where A.ID is null
© Luca Anselma 2009 6