# Filmografie

Si consideri il seguente schema:

- Regista(**Nome**, DataNascita, Nazionalità)
- Attore(**Nome**, DataNascita, Nazionalità)
- Interpreta(**Attore**, **Film**, **Personaggio**)
- Film(**Titolo**, NomeRegista, Anno)
- Proiezione(**NomeCin**, **CittaCin**, **TitoloFilm**)
- Cinema(**Citta**, **NomeCinema**, Sale, Posti)

---

**Domanda 1.** Selezionare le Nazionalità dei registi che hanno diretto qualche film nel 1992, ma non hanno diretto nessun film nel 1993.

-- Soluzione 1
select distinct Nazionalità
from Regista
where Nome in (
	select NomeRegista
	from Film
	where Anno = '1992')
	and
	Nome not in (
	select NomeRegista
	from Film
	where Anno = '1993');
	
-- Soluzione 2
select distinct Nazionalità
from Regista R join Film F
	on R.Nome = F.NomeRegista
where F.anno = '1992'
	and 
	(select count(*)
	from Film
	where Anno = '1993'
		and NomeRegista = R.Nome) = 0;
	
-- Soluzione 3
select distinct Nazionalità
from Regista, Film
where Nome = NomeRegista and Anno = '1992'
	and Nome not in (
		select NomeRegista
		from Film
		where Anno = '1993'
	);

Versione con **ERRORE**!

-- Soluzione SBAGLIATA
SELECT Nazionalità
FROM Regista JOIN Film
	ON Nome = NomeRegista
WHERE Anno = 1992 AND Anno <> 1993

La clausola `where` agisce a livello di tupla.

---

**Domanda 2.** Nomi dei registi che nel 1993 hanno diretto più film che nel 1992.

-- Soluzione 1
select Nome
from Regista R
where (
	select count(*) 
	from Film 
	where NomeRegista = R.Nome and Anno = '1993')
	> (
	select count(*) 
	from Film 
	where NomeRegista = R.Nome and Anno = '1992');
	
-- Soluzione 2
select distinct NomeRegista 
from Film F1
where Anno = '1993'
group by NomeRegista
having count(*) > (
	select count(*)
	from Film F2
	where Anno = '1992' 
		and F1.NomeRegista = F2.NomeRegista);

-- Soluzione 3 (con vista)
create view NumPerAnno(Nom, Ann, Num) as
select NomeRegista, Anno, count(*)
from Film
group by NomeRegista, Anno

select Nom as NomeRegistaCercato
from NumPerAnno N1
where Ann = '1993' and
	Nom not in (
		select Nom
		from NumPerAnno N2
		where N2.Ann = '1992' and N1.Num <= N2.Num);

Versione con **ERRORE**!

-- Soluzione 2 SBAGLIATA 
-- Si perdono quelli che non hanno fatto 
-- film nel 1992
select distinct NomeRegista 
from Film F1
where Anno = '1992'
group by NomeRegista
having count(*) < (
	select count(*)
	from Film F2
	where Anno = '1993' 
		and F1.NomeRegista = F2.NomeRegista);

---

**Domanda 3.** Date di nascita dei registi che hanno diretto film in proiezione sia a Torino che a Milano.

-- Soluzione 1
select distinct NomeRegista, DataNascita
from Regista join Film on Nome = NomeRegista
where Titolo in (
	select TitoloFilm 
	from Proiezione
	where CittaCin = 'Torino')
	and Titolo in (
	select TitoloFilm 
	from Proiezione
	where CittaCin = 'Milano');
	
-- Soluzione 2
select distinct NomeRegista, DataNascita
from Regista R join Film F on R.Nome = F.NomeRegista
	join Proiezione P on F.Titolo = P.TitoloFilm
where CittaCin = 'Milano'
	and Titolo in (
		select TitoloFilm
		from Proiezione
		where CittaCin = 'Torino');

-- Soluzione 3
select distinct Nome, DataNascita
from Regista R join Film F on R.Nome = F.NomeRegista
	join Proiezione P1 on P1.TitoloFilm = F.titolo
	join Proiezione P2 on P2.TitoloFilm = F.titolo
where P1.Citta = 'Torino' and P2.Citta = 'Roma';

Versioni con **ERRORI**!

-- Soluzione SBAGLIATA 1 
-- non può mai esistere una tupla con entrambi
-- i valori
select distinct NomeRegista, dataNascita
from Regista join Film on Nome = NomeRegista
where Titolo in (
	select TitoloFilm 
	from Proiezione
	where CittaCin = 'Milano' 
		and CittaCin = 'Torino');
		
-- Soluzione SBAGLIATA 2 
-- si considerano i film che soddisfano 
-- OR logico, quindi non va bene
select distinct NomeRegista, dataNascita
from Regista join Film on Nome = NomeRegista
where Titolo in (
	select TitoloFilm 
	from Proiezione
	where CittaCin = 'Milano' 
		or CittaCin = 'Torino');

---

**Domanda 4.** Film proiettato nel maggior numero di cinema a Milano.

-- Soluzione 1
select TitoloFilm
from Proiezione
where CittaCin = 'Milano'
group by TitoloFilm
having count(distinct NomeCinema) >= all (
	select count(*)
	from Proiezione
	where CittaCin = 'Milano'
	group by TitoloFilm);
	
-- Soluzione 2
create view ProiezMilano(Titolo, Num) as
select TitoloFilm, count(*)
from Proiezione
where CittaCin = 'Milano'
group by TitoloFilm

select Titolo
from ProiezMilano
where Num = (
	select max(Num)
	from ProiezMilano);

---

**Domanda 5.** Attori che hanno interpretato più di un ruolo nello stesso film.

-- Soluzione 1.1
select distinct Attore
from Interpreta I1 join Interpreta I2
	on I1.Attore = I2.Attore and I1.Film = I2.Film
where I1.Personaggio <> I2.Personaggio;

-- Soluzione 1.2
select distinct I1.Attore
from Interpreta I1, Interpreta I2
where I1.Attore = I2.Attore
	and I1.Film = I2.Film
	and I1.Personaggio <> I2.Personaggio;

-- Soluzione 2.1
-- funziona per com'è fatta la chiave
-- e per via del fatto che raggruppiamo per 
-- attore e personaggio
select distinct Attore
from Interpreta
group by Attore, Film
having count(Personaggio) > 1;

-- Soluzione 2.2
select distinct Attore
from Interpreta
group by Attore, Film
having count(distinct Personaggio) > 1;

-- Soluzione 2.3
-- funziona per com'è fatta la chiave
-- e per via del fatto che raggruppiamo per 
-- attore e personaggio
select distinct Attore
from Interpreta
group by Attore, Film
having count(*) > 1;

---

**Domanda 6.** Selezionare i film in cui recita un solo attore che però interpreta più personaggi.

-- Soluzione 1.1
select Film
from Interpreta
group by Film
having count(distinct Attore) = 1
	and count(distinct Personaggio) > 1;
	
-- Soluzione 1.2
-- sempre per il fatto della chiave
select Film
from Interpreta
group by Film
having count(distinct Attore) = 1
	and count(*) > 1;

---

**Domanda 7.** Trovare gli attori italiani che non hanno mai recitato con altri attori italiani.

-- Soluzione 1
-- La subquery identifica gli attori (in generale, anche
-- non italiani) che hanno recitato con altri attori italiani
SELECT A1.Nome
FROM Attore A1
WHERE A1.Nazionalità = 'Italiana'
  AND A1.Nome NOT IN (
      SELECT DISTINCT I1.Attore
      FROM Interpreta I1
	      JOIN Interpreta I2 ON I1.Titolo = I2.Titolo
	      JOIN Attore A2 ON I2.Attore = A2.Nome
      WHERE A2.Nazionalità = 'Italiana'
        AND I1.Attore <> A2.Nome
  );

-- Soluzione 2
-- Subquery funziona per ogni riga, quindi restituisce l'attore stesso 
-- se ha collaborato con altri italiani, oppure nulla
SELECT Nome
FROM Attore A1
WHERE Nazionalità = 'Italiana'
  AND A1.Nome NOT IN (
      SELECT I1.Attore
      FROM Interpreta I1, Interpreta I2, Attore A2
      WHERE I1.Titolo = I2.Titolo
        AND I2.Attore = A2.Nome
        AND A1.Nome = I1.Attore  -- Correlazione esplicita
        AND A2.Nome <> A1.Nome
        AND A2.Nazionalità = 'Italiana'
  );

-- Soluzione 3
create view IntIta as
select Film, Attore
from Interpreta
where Attore in (
	select Nome 
	from Attore
	where Nazionalità = 'Italiana');
	
select Attore
from IntIta
where Attore not in (
	select X.Attore
	from IntIta X, IntIta Y
	where X.Film = Y.Film
		and X.Attore <> Y.Attore);

---

**Domanda 8.** Trovare i film di registi italiani dove non recita nessun italiano.

select Titolo
from Regista join Film on Nome = NomeRegista
where Nazionalità = 'Italiana'
	and Titolo not in (
	select Film
	from Interpreta join Attore on Attore = Nome
	where Nazionalità = 'Italiana'
);

---

**Domanda 9.** Registi che hanno recitato in almeno un loro film.

SELECT DISTINCT NomeRegista
FROM Film JOIN Interpreta 
	ON Film.Titolo = Interpreta.Film
WHERE Interpreta.Attore = Film.NomeRegista;

---

**Domanda 10.** I registi che hanno recitato in almeno 4 loro film interpretandovi un totale di almeno 5 personaggi diversi (non si consideri il caso in cui un regista/attore interpreta personaggi diversi che però hanno lo stesso nome, in film diversi)

select NomeRegista
from Film join Interpreta on Titolo = Film
where NomeRegista = Attore
group by NomeRegista
having count(distinct Titolo) >= 4
	and count(distinct Personaggio) >= 5