Esercizio 1

Si consideri il seguente schema di base di dati:

QUIZ(CodQuiz, Argomento, Punteggio) STUDENTE(Matricola, Nome, Indirizzo, Città) RISULTATOTEST(Matricola, CodQuiz, Esito, Data)

L’attributo Esito può assumere come valore superato/non superato ed indica l’esito del quiz.

Scrivere i comandi SQL per:

1. Creare la tabella RISULTATOTEST 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 RISULTATOTEST(
   	Matricola NUMERIC(7) REFERENCES STUDENTE(Matricola)
   		ON UPDATE CASCADE
   		ON DELETE CASCADE,
   	CodQuiz VARCHAR(4) REFERENCES QUIZ(CodQuiz)
   		ON UPDATE CASCADE
   		ON DELETE RESTRICT,
   	Esito VARCHAR(12) NOT NULL CHECK IN('Superato','Non superato'),
   	Data DATE NOT NULL,
   	PRIMARY KEY (Matricola, CodQuiz)
   );
   

   Se viene modificata la matricola di uno studente o il codice di un quiz nelle tabelle QUIZ/STUDENTE le modifiche vengono propagate alla tabella RISULTATOTEST. Se si cancella uno studente, vengono cancellate anche le informazioni sui test a cui ha preso parte. Non viene consentita la cancellazione di un quiz se ci sono ancora informazioni a lui relative nella tabella RISULTATOTEST.

2. Cancellare gli studenti che non hanno sostenuto test dopo l’1/1/2010

   DELETE FROM STUDENTE
   WHERE Matricola NOT IN(
   	SELECT Matricola 
   	FROM STUDENTE NATURAL JOIN RISULTATOTEST
   	WHERE Data > '1/1/2010' DATE
   );
   

3. Aumentare di due unità il punteggio dei quiz di Informatica

   UPDATE QUIZ SET Punteggio = Punteggio + 2
   WHERE Argomento = 'Informatica';
   

4. Aggiungere alla tabella STUDENTE l’attributo obbligatorio email

   ALTER TABLE STUDENTE ADD COLUMN email VARCHAR(30) NOT NULL
   DEFAULT '[email protected]';
   

Esercizio 2

1. Il nome e la matricola degli studenti di Como che nel Gennaio del 2017 hanno risposto correttamente ad almeno un quiz di Informatica

   SELECT Nome, Matricola FROM STUDENTI
   WHERE Città = 'Como' AND Matricola IN(
   	SELECT Matricola 
   	FROM RISULTATOTEST NATURAL JOIN QUIZ
   	WHERE Argomento = 'Informatica' AND Esito = 'Superato' 
   		AND Data > '1/1/2017' DATE 
   		AND Data < '31/1/2017' DATE
   );
   

2. Il nome degli studenti di Varese che hanno svolto tutti i quiz di matematica

   
   SELECT Nome FROM STUDENTE
   WHERE Città = 'Varese' AND Matricola IN (
   	SELECT Matricola 
   	FROM RISULTATOTEST NATURAL JOIN QUIZ
   	WHERE Argomento = 'Matematica'
   	GROUP BY Matricola
   	HAVING COUNT(*) = (
   		SELECT COUNT(*) FROM QUIZ 
   		WHERE Argomento = 'Matematica'
   	)
   );
   

3. I quiz a cui hanno partecipato almeno 20 studenti, ciascuno dei quali abbia superato almeno 10 quiz di biologia

   SELECT CodQuiz FROM RISULTATOTEST
   WHERE Matricola IN(
   	SELECT Matricola 
   	FROM RISULTATOTEST NATURAL JOIN QUIZ
   	WHERE Esito = 'Superato' AND Argomento = 'Biologia'
   	GROUP BY Matricola
   	HAVING COUNT(*) >= 10
   )
   GROUP BY CodQuiz
   HAVING COUNT(*) >= 20;
   

4. Il nome degli studenti che hanno superato con successo sia quiz di matematica che di informatica ma mai quelli di fisica

   SELECT Nome 
   FROM STUDENTI NATURAL JOIN RISULTATOTEST NATURAL JOIN QUIZ
   WHERE Argomento = 'Matematica' AND Esito = 'Superato'
   INTERSECT 
   SELECT Nome 
   FROM STUDENTI NATURAL JOIN RISULTATOTEST NATURAL JOIN QUIZ
   WHERE Argomento = 'Informatica' AND Esito = 'Superato'
   EXCEPT
   SELECT Nome 
   FROM STUDENTI NATURAL JOIN RISULTATOTEST NATURAL JOIN QUIZ
   WHERE Argomento = 'Fisica' AND Esito = 'Superato'