Esercizio 1

Si consideri il seguente schema di base di dati:

ATTORI(CodAttore, Nome, AnnoNascita, Nazionalità) RECITA(CodAttore(ATTORI), CodFilm(FILM)) FILM(CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere)

Scrivere i comandi SQL per:

1. Creare la tabella RECITA specificando gli opportuni vincoli di integrità e le opzioni per gestire l’integrità referenzia (motivando le scelte effettuate per la gestione dell’integrità referenziale)

   CREATE TABLE RECITA(
   	codAttore NUMERIC(5),
   	codFilm NUMERIC(5),
   	PRIMARY KEY (codAttore, codFilm),
   	FOREIGN KEY codAttore REFERENCES ATTORI(codAttore)
   		ON UPDATE CASCADE
   		ON DELETE CASCADE,
   	FOREIGN KEY codFilm REFERENCES FILM(codFilm)
   		ON UPDATE CASCADE
   		ON DELETE CASCADE
   );
   

2. Aggiungere alla tabella FILM un vincolo che specifichi che l’attributo anno di produzione deve essere compreso tra il 1950 e il 2018

   ALTER TABLE FILM ADD CONSTRAINT vincolo1 
   CHECK(annoProduzione BETWEEN 1950 AND 2018);
   

3. Creare una vista che contenga solo i film di Genere comico prodotti nel 2010

   CREATE VIEW vista1 AS(
   	SELECT * FROM FILM
   	WHERE genere = 'comico' AND annoProduzione = 2010
   )
   

4. Aggiungere la colonna telefono alla tabella ATTORI

   ALTER TABLE ATTORI
   ADD COLUMN telefono VARCHAR(25)
   

Esercizio 2

Formulare in SQL le interrogazioni per restituire:

1. Il codice degli attori che hanno recitato in tutti i film del regista Francis Ford Coppola

   SELECT codAttore 
   FROM ATTORI NATURAL JOIN RECITA NATURAL JOIN FILM
   WHERE regista = 'Francis Ford Coppola'
   GROUP BY codAttore
   HAVING COUNT(*) = (
   	SELECT COUNT(*)
   	FROM FILM
   	WHERE regista = 'Francis Ford Coppola'
   )
   

2. Il numero di attori che hanno recitato in ogni film, per i film del 2010 in cui hanno recitato almeno due attori

   -- Q1: codici dei film del 2010 in cui hanno recitato 
   -- almeno due attori
   SELECT CodFilm
   FROM RECITA NATURAL JOIN FILM
   WHERE AnnoProduzione = 2010
   GROUP BY CodFilm
   HAVING COUNT(CodAttore) >= 2;
   
   SELECT COUNT(codAttore)
   FROM RECITA
   WHERE codAttore IN(
   	SELECT codAttore FROM RECITA 
   	WHERE codFilm IN Q1
   	GROUP BY codAttore 
   	HAVING COUNT(*)=(
   		SELECT COUNT(*)
   		FROM FILM
   		WHERE codFilm IN Q1
   	)
   )
   

3. Il codice ed il nome degli attori che hanno recitato in film della loro stessa nazionalità

   SELECT codAttore, nome
   FROM ATTORI NATURAL JOIN RECITA NATURAL JOIN FILM
   WHERE ATTORI.nazionalità = FILM.nazionalità
   GROUP BY codAttore, nome
   

4. Il titolo dei film senza attori

   -- troviamo il numero di attori per ogni film
   -- vogliamo solo i film dove il numero di attori è 0
   SELECT titolo
   FROM FROM ATTORI NATURAL JOIN RECITA NATURAL JOIN FILM
   GROUP BY codFilm, titolo
   WHERE COUNT(codAttore) = 0
   
   -- SELECT CodFilm FROM FILM MINUS SELECT CodFilm FROM RECITA;