Basi di dati - il linguaggio di interrogazione SQL - esercitazione

SCHEMA RELAZIONALE:

AUTO (Targa, Marca, Cilindrata, Potenza, CodF*, CodAss*)

PROPRIETARI (CodF, Nome, Residenza)

ASSICURAZIONI (CodAss, Nome, Sede)

SINISTRO (CodS, Località, Data)

AUTOCOINVOLTE (CodS*, Targa*, ImportoDelDanno)

Scrivere le interrogazioni SQL che restituiscono le seguenti informazioni:

1- Targa e Marca delle Auto di cilindrata superiore a 2000 cc o di potenza superiore a 120 CV

2- Nome del proprietario e Targa delle Auto di cilindrata superiore a 2000 cc oppure di potenza

superiore a 120 CV

3- Targa e Nome del proprietario delle Auto di cilindrata superiore a 2000 cc oppure di potenza

superiore a 120 CV, assicurate presso la “SARA”

4- Targa e Nome del proprietario delle Auto assicurate presso la “SARA” e coinvolte in sinistri il

20/01/02

5- Per ciascuna Assicurazione, il nome, la sede ed il numero di auto assicurate

6- Per ciascuna auto “Fiat”, la targa dell’auto ed il numero di sinistri in cui è stata coinvolta

7- Per ciascuna auto coinvolta in più di un sinistro, la targa dell’auto, il nome dell’ Assicurazione

ed il totale dei danni riportati

8- CodF e Nome di coloro che possiedono più di un’auto

9- La targa delle auto che non sono state coinvolte in sinistri dopo il 20/01/01

10- Il codice dei sinistri in cui non sono state coinvolte auto con cilindrata inferiore a 2000 cc

R.Gori – G.Leoni 5

Esercizi di SQL

ESERCIZIO 4

SCHEMA RELAZIONALE:

ROMANZI(CodiceR, Titolo, NomeAut*, Anno)

PERSONAGGI(NomeP, CodiceR*, sesso, ruolo)

AUTORI(NomeAut, AnnoN, AnnoM:optional, Nazione)

FILM(CodiceF, Titolo, Regista, Produttore, Anno, CodiceR*)

Scrivere le interrogazioni SQL che restituiscono le seguenti informazioni:

1- Il titolo dei romanzi del 19° secolo

2- Il titolo e l’anno di pubblicazione dei romanzi di autori russi, ordinati per autore e, per lo stesso

autore, ordinati per anno di pubblicazione

3- I personaggi principali (ruolo =”P”) dei romanzi di autori viventi.

4. I romanzi dai quali è stato tratto un film con lo stesso titolo del romanzo

5- Il titolo, il regista e l’anno dei film tratti dal romanzo “Robin Hood”

6- Per ogni autore italiano, l’anno del primo e dell’ultimo romanzo.

7- I nomi dei personaggi che compaiono in più di un romanzo, ed il numero dei romanzi nei quali

compaiono

8- I romanzi di autori italiani dai quali è stato tratto più di un film

9- Il titolo dei romanzi dai quali non è stato tratto un film

10- Il titolo dei romanzi i cui personaggi principali son tutti femminili.

R.Gori – G.Leoni 6

Esercizi di SQL

ESERCIZIO 5

SCHEMA RELAZIONALE:

STUDENTI (Matricola, NomeS, CorsoLaurea*, AnnoN)

CORSIDILAUREA (CorsoLaurea, TipoLaurea, Facoltà)

FREQUENTA (Matricola*, CodCorso*)

CORSI (CodCorso, NomeCorso, CodDocente*)

DOCENTI (CodDocente, NomeD, Dipartimento)

Scrivere le interrogazioni SQL che restituiscono le seguenti informazioni:

1- Il nome e l’anno di nascita degli studenti iscritti a SBC, in ordine rispetto al nome

2- Il nome ed il Dipartimento dei docenti di “Basi di Dati e Sistemi Informativi” o di

“Informatica Generale”

3- Matricola e nome degli studenti di un corso di laurea triennale (tipoLaurea = '

L'

) che

seguono un corso di un docente di nome Felice.

4- Per ogni tipo di laurea, il tipoLaurea e l’età media degli studenti

5- Di ogni corso di un docente di nome Leoni, il CodCorso e il numero degli studenti che lo

frequentano

6- Il codice dei corsi frequentati da più di 5 studenti e tenuti da docenti del Dipartimento

di Informatica

7- Per ogni studente della Facoltà di Lettere e Filisofia, la matricola ed il numero di corsi seguiti

8- Matricola e nome degli studenti che non frequentano nessun corso

9- Il Codice ed il Nome dei docenti dei Corsi che non sono frequentati da nessuno studente

10- Matricola e nome degli studenti che seguono solo corsi di docenti del dipartimento di Storia

11- Il CodCorso dei corsi seguiti solo da studenti che appartengono al Corso di Laurea Triennale

in SBC

12- Nome e CodDocente dei docenti che insegnano qualche corso seguito da più di 5 studenti

13- Codice dei corsi che sono frequentati da tutti gli studenti del CorsoLaurea SBC

R.Gori – G.Leoni 7

Esercizi di SQL

ESERCIZIO 1 - Soluzioni

SCHEMA RELAZIONALE:

ATTORI (CodAttore, Nome, AnnoNascita, Nazionalità);

RECITA (CodAttore*, CodFilm*)

FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere)

PROIEZIONI (CodProiezione, CodFilm*, CodSala*, Incasso, DataProiezione)

SALE (CodSala, Posti, Nome, Città)

1- Il nome di tutte le sale di Pisa

SELECT s.Nome

FROM Sale s

WHERE s.Città = '

Pisa'

2- Il titolo dei film di F. Fellini prodotti dopo il 1960.

SELECT f.Titolo

FROM Film f

WHERE f.Regista = “Fellini” AND f.AnnoProduzione > 1960

3- Il titolo e la durata dei film di fantascienza giapponesi o francesi prodotti dopo il 1990

SELECT f.Titolo, f.Durata

FROM Film f

WHERE f.Genere=”Fantascienza” and ((f.Nazionalità=”Giapponese” or f.Nazionalità=”Francese”) and

f.AnnoProduzione >1990

4- Il titolo dei film di fantascienza giapponesi prodotti dopo il 1990 oppure francesi

SELECT f.Titolo

FROM Film f

WHERE f.Genere=”Fantascienza” and ((f.Nazionalità=”Giapponese” and f.Anno>1990) or

f.Nazionalità=”Francese”)

5- I titolo dei film dello stesso regista di “Casablanca”

SELECT f.Titolo

FROM Film f

WHERE f.Regista = (SELECT f.Regista

FROM Film f

WHERE f.Titolo = “Casablanca”)

6- Il titolo ed il genere dei film proiettati il giorno di Natale 2004

SELECT DISTINCT f.Titolo, f.Genere

FROM Film f, Proiezioni p

WHERE p DataProiezione =25/12/04 and f.CodFilm=p.CodFilm

7- Il titolo ed il genere dei film proiettati a Napoli il giorno di Natale 2004

SELECT DISTINCT f.Titolo, f.Genere

FROM Film f, Proiezioni p, Sale s

WHERE p.DataProiezione =25/12/04 and s.Città=”Napoli” and f.CodFilm=p.CodFilm and

p.CodSala=s.CodSala

R.Gori – G.Leoni 8

Esercizi di SQL

8- I nomi delle sale di Napoli in cui il giorno di Natale 2004 è stato proiettato un film con R.Williams

SELECT DISTINCT s.Nome

FROM Attori a, Recita r, Film f, Proiezioni p, Sale s

WHERE p.DataProiezione =25/12/04 and s.Città=”Napoli” and a.Nome = “R.Williams”

and a.CodAttore = r.CodAttore and r.CodFilm = f.CodFilm and f.CodFilm=p.CodFilm

and p.CodSala=s.CodSala

9- Il titolo dei film in cui recita M. Mastroianni oppure S.Loren

SELECT DISTINCT f.Titolo

FROM Film f, Recita r, Attore a

WHERE (a.Nome = “M.Mastrianni” OR a.Nome = ”S.Loren”)

AND f.CodFilm = r.CodFilm

AND r.CodAttore = a.CodAttore

10- Il titolo dei film in cui recitano M. Mastroianni e S.Loren

SELECT f.Titolo

FROM Film f,

WHERE “M.Mastrianni” IN (SELECT a.Nome

FROM Attori A, Recita R

WHERE f.CodFilm = r.CodFilm AND r.CodAttore = a.CodAttore)

AND ”S.Loren” IN (SELECT a.Nome

FROM Attori A, Recita R

WHERE f.CodFilm = r.CodFilm AND r.CodAttore = a.CodAttore)

11- Per ogni film in cui recita un attore francese, il titolo del film e il nome dell’attore

SELECT DISTINCT f.Titolo, a.Nome

FROM Attori a, Recita r, Film f

WHERE a.CodAttore = r.CodAttore and r.CodFilm = f.CodFilm

and a.Nazionalità = “Francese”

12- Per ogni film che è stato proiettato a Pisa nel gennaio 2005, il titolo del film e il nome della sala.

SELECT DISTINCT f.Titolo, s.Nome

FROM Film f, Proiezioni p, Sale s

WHERE f.CodFilm = s.CodFilm and p.CodSala=s.CodSala

and s.Città = ‘Pisa’ and p.DataProiezione between 01/01/05 and 31/01/05

13- Il numero di sale di Pisa con più di 60 posti

SELECT count(*)

FROM Sale s

WHERE s.Città = “Pisa” and s.Posti > 60

14- Il numero totale di posti nelle sale di Pisa

SELECT sum(s.Posti)

FROM Sale s

WHERE s.Città = “Pisa”

15- Il numero di sale di ogni città

SELECT s.Città, count(*)

FROM Sale s

GROUP BY s.Città

R.Gori – G.Leoni 9

Esercizi di SQL

16- Il numero di sale con più di 60 posti di ogni città

SELECT s.Città, count(*)

FROM Sale s

WHERE s.Posti > 60

GROUP BY s.Città

17- Per ogni regista, il numero di film diretti dopo il 1990

SELECT f.Regista, count(*)

FROM Film f

WHERE f.AnnoProduzione > '

1990'

GROUP BY f.Regista

18- Per ogni regista, l’incasso totale di tutte le proiezioni dei suoi film

SELECT f.Regista, sum(p.Incasso) as IncassoTotale

FROM Film f, Proiezioni p

WHERE f.CodFilm = p.CodFilm

GROUP BY f.Regista

19- Per ogni film di S.Spielberg, il titolo del film, il numero totale di proiezioni a Pisa e l’incasso totale

(sempre a Pisa)

SELECT f.Titolo, count(*) as NumeroProiezioni, sum(p.Incasso) as IncassoTotale

FROM Film f, Proiezioni p, Sale s

WHERE f.CodFilm = p.CodFilm and p.CodSala=s.CodSala

and f.Regista = ‘S.Spielberg’ and s.Città = ‘Pisa’

GROUP BY f.CodFilm, f.Titolo

20- Per ogni regista e per ogni attore, il numero di film del regista con l’attore

SELECT f.Regista, a.Nome, count(*) as NumeroFilm

FROM Attori a, Recita r, Film f

WHERE a.CodAttore=r.CodAttore and rCodFilm = f.CodFilm

GROUP BY f.Regista, a.CodAttore, a.Nome

21 - Il regista ed il titolo dei film in cui recitano meno di 6 attori

SELECT f.Regista, f.Titolo

FROM Film f, Recita r

WHERE f.CodFilm = r.CodFilm

GROUP BY f.CodFilm, f.Titolo, f.Regista

HAVING count(*) < 6

(osserviamo che questa interrogazione non restituisce I film in cui non recita alcun attore)

oppure

SELECT f.Regista, f.Titolo

FROM Film f

WHERE 6 > (SELECT count (*)

FROM Recita r

WHERE f.CodFilm = r.CodFilm)

22- Per ogni film prodotto dopo il 2000, il codice, il titolo e l’incasso totale di tutte le sue proiezioni

SELECT f.CodFilm, f.Titolo, sum (f.Incasso) as IncasssoTotale

FROM Film f, Proiezioni p

WHERE f.AnnoProduzione > 2000 and f.CodFilm = p.CodFilm

GROUP BY f.CodFilm, f.Titolo

R.Gori – G.Leoni 10

Esercizi di SQL

23 – Il numero di attori dei film in cui appaiono solo attori nati prima del 1970

SELECT f.Titolo, count(*) as NumeroAttori

FROM Attori a, Recita r, Film f

WHERE a.CodAttore=r.CodAttore and r.CodFilm = f.CodFilm

GROUP BY f.CodFilm, f.Titolo

HAVING max(a.AnnoNascita) < 1970

24- Per ogni film di fantascienza, il titolo e l’incasso totale di tutte le sue proiezioni

SELECT f.Titolo, sum(p.Incasso) as IncassoTotale

FROM Film f, Proiezioni p

WHERE f