Si consideri il seguente schema di base di dati relativo ad una porzione di un ipotetico sistema di gestione del calendario dei Mondiali di Calcio 2018

STADIO(Nome, Città, Capienza) SQUADRA(Nazione, Data_qualifica, Capitano, Mondiali_vinti) PARTITA(Stadio, Data, Squadra1,Squadra2, Goal1, Goal2, Spettatori) GIOCATORE(Codice, Nome, Cognome, Squadra, Età, Ruolo)

Goal1 e Goal2 indicano i goal segnati durante una partita dalle due squadre che si sfidano (squadra1 e squadra2)

1. Identificare le chiavi esterne presenti nello schema indicando, per ognuna di esse, relazione referente e riferita

   SQUADRA(Nazione, Data_qualifica, Capitano(GIOCATORE), Mondiali_vinti) PARTITA(Stadio(STADIO), Data, Squadra1(SQUADRA), Squadra2(SQUADRA), Goal1, Goal2, Spettatori) GIOCATORE(Codice, Nome, Cognome, Squadra(SQUADRA), Età, Ruolo)

2. Scrivere i comandi SQL per:

   1. Creare una vista che contenga il nome delle squadre il cui capitano ha meno di 20 anni

      CREATE VIEW vista1 AS
      SELECT Nazione
      FROM SQUADRA JOIN GIOCATORE ON (Capitano = Codice)
      WHERE Età < 20
      

   2. Aggiungere un vincolo che stabilisca che l’età di un giocatore non può essere inferiore a 17 anni

      ALTER TABLE GIOCATORE ADD CONSTRAINT vincoloEtà
      CHECK(Età >= 17)
      

   3. Aumentare di una unità il numero di mondiali vinti dal Belgio

      UPDATE SQUADRA
      SET Mondiali_vinti = Mondiali_vinti + 1
      WHERE Nazione = 'Belgio'
      

Esercizio 2

1. Il nome delle squadre che non hanno mai disputato una gara finita 0 a 0

   SELECT Nazione FROM SQUADRA WHERE Nazione NOT IN(
   	SELECT Squadra1 FROM PARTITA WHERE Goal1 = 0 AND Goal2 = 0
   )AND Nazione NOT IN (
   	SELECT Squadra2 FROM PARTITA WHERE Goal1 = 0 AND Goal2 = 0
   )
   

2. Le nazionali che hanno vinto almeno 3 mondiali e con almeno 5 giocatori con ruolo attaccante di età compresa tra 20 e 25 anni.

   SELECT Nazione FROM Squadra WHERE Mondiali_vinti >= 3 AND
   	NAZIONE IN(
   	SELECT Squadra FROM GIOCATORE WHERE Ruolo = 'Attaccante'
   	AND Età BETWEEN 20 AND 25 
   	GROUP BY Squadra
   	HAVING COUNT(*) >= 5
   )
   

3. Lo stadio che ha ospitato partite della nazionale dell’Uruguay e della Francia ma mai della Germania

   SELECT Nome 
   FROM STADIO JOIN PARTITA ON (Nome = Stadio)
   WHERE Squadra1 = 'Uruguay' OR Squadra2 = 'Uruguay'
   INTERSECT
   SELECT Nome
   FROM STADIO JOIN PARTITA ON (Nome = Stadio)
   WHERE Squadra1 = 'Francia' OR Squadra2 = 'Francia'
   EXCEPT
   SELECT Nome
   FROM STADIO JOIN PARTITA ON (Nome = Stadio)
   WHERE Squadra = 'Germania' OR Squadra2 = 'Germania'
   

4. Il nome e la città degli stadi dove si sono giocate le partite con più goal

   SELECT Nome, Città 
   FROM STADIO JOIN PARTITA ON (Nome = Stadio)
   WHERE (Goal1 + Goal2) >= ALL(
   	SELECT Goal1 + Goal2 FROM PARTITA
   );