Esercizio 1

SQUADRA(codiceS, nome, dataFondazione) GIOCATORE(codiceG, nome, cognome, dataNascita, nazionalità) GIOCA(codiceSquadra, codGiocatore, partiteGiocate, golSegnati)

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

   GIOCA(codiceSquadra(SQUADRA), codGiocatore(GIOCATORE), partiteGiocate, golSegnati)

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

   CREATE TABLE GIOCA(
   	codSquadra DECIMAL(8) REFERENCES SQUADRA 
   		ON UPDATE CASCADE,
   	codGiocatore DECIMAL(8) REFERENCES GIOCATORE 
   		ON UPDATE CASCADE,
   		ON DELETE CASCADE,
   	partiteGiocate INTEGER,
   	golSegnati INTEGER,
   	PRIMARY KEY (codSquadra, codGiocatore)
   );
   

3. Cancellare i giocatori che hanno segnato zero goal

   DELETE FROM GIOCATORI WHERE codiceG NOT IN(
   	SELECT codGiocatore FROM GIOCA WHERE golSegnati <> 0
   );
   

4. Aggiungere alla tabella SQUADRA un vincolo che specifichi che l’anno di fondazione non può essere inferiore al 1900

   ALTER TABLE SQUADRA ADD CONSTRAINT anno 
   CHECK(EXTRACT(YEAR FROM dataFondazione) )>= 1990);
   

Esercizio 2

1. Il nome e cognome dei giocatori stranieri che giocano nella Juventus ed hanno segnato almeno 4 goal

   SELECT nome, cognome FROM GIOCATORE
   WHERE codG IN (
   	SELECT codGiocatore
   	FROM SQUADRA JOIN GIOCA ON (codiceS = codiceSquadra)
   	WHERE nome = 'Juventus' AND nazione <> 'Italia'
   	GROUP BY codGiocatore
   	HAVING SUM(golSegnati) >= 4;
   )
   

2. Il codice dei giocatori che non hanno mai giocato in una squadra fondata nel 2000

   SELECT codiceG FROM GIOCATORE
   EXCEPT
   SELECT codGiocatore 
   FROM GIOCA JOIN SQUADRA ON (codiceS = codiceSquadra)
   WHERE EXTRACT(YEAR FROM dataFondazione) = 2000;
   

3. Per i giocatori che hanno segnato almeno 3 goal, il loro id ed il numero di squadra in cui hanno militato

   SELECT codGiocatore, COUNT(codiceSquadra)
   FROM GIOCA
   GROUP BY codGiocatore
   HAVING SUM(golSegnati) >= 3;
   

4. Il nome delle squadre fondate dopo il 1980 in cui sono presenti sia giocatori tedeschi che francesi ma non austriaci

   SELECT nome FROM SQUADRA, GIOCA, GIOCATORE
   WHERE codceS = codiceSquadra AND codG = codiceGiocatore
   AND EXTRACT(YEAR FROM dataFondazione) > 1980 
   AND nazionalità = 'tedesca' 
   INTERSECT
   SELECT nome FROM SQUADRA, GIOCA, GIOCATORE
   WHERE codiceS = codiceSquadra AND codG = codiceGiocatore 
   AND EXTRACT(YEAR FROM dataFondazione) > 1980 
   AND nazionalità = 'francese'
   MINUS
   SELECT nome FROM SQUADRA, GIOCA, GIOCATORE
   WHERE codiceS = codiceSquadra AND codG = codiceGiocatore 
   AND EXTRACT(YEAR FROM dataFondazione) > 1980 
   AND nazionalità = 'austriaca'
   

5. Il codice delle squadre in cui hanno militato giocatori di almeno 3 diverse nazionalità

   SELECT codiceSquadra
   FROM GIOCA JOIN GIOCATORE ON (codiceGiocatore = codiceG)
   GROUP BY codiceSquadra
   HAVING COUNT(DISTINCT(nazionalità) >= 3