-- Schema della Base di dati
 
CREATE TABLE Studente (
       matricola VARCHAR(20) PRIMARY KEY, 
       anno INT
)
;

CREATE TABLE Corso (
       nome VARCHAR(20) PRIMARY KEY, 
       anno INT
)
;

CREATE TABLE Prenota (
       studente VARCHAR(20), 
       corso VARCHAR(20), 
       PRIMARY KEY (studente,corso),
       FOREIGN KEY studente REFERENCES Studente(matricola),
       FOREIGN KEY corso REFERENCES Corso(nome)
)
;



--Query 1
SELECT p.studente
FROM Studente s, Prenota p, Corso c
WHERE s.matricola = p.studente AND p.corso=c.nome AND
           s.anno = c.anno
;


--Query 2
SELECT s.matricola
FROM Studente s
EXCEPT
SELECT p.studente
FROM Studente s, Prenota p, Corso c
WHERE s.matricola = p.studente AND p.corso=c.nome AND
            s.anno = c.anno
;

--Query 3

CREATE VIEW NumCorsiAnno AS (
SELECT anno, count(*) AS numcorsierogati
FROM Corso 
GROUP BY anno
)
;

CREATE VIEW StudenteAnnoNumCorsi AS (
SELECT p.studente AS studente, c.anno AS anno, count(*) AS numcorsiprenotati 
FROM  Prenota p, Corso c
WHERE p.corso = c.nome
GROUP BY p.studente, c.anno
)
;

SELECT v1.studente, v1.anno, v1.numcorsiprenotati, v2.numcorsierogati
FROM StudenteAnnoNumCorsi v1, NumCorsiAnno v2
WHERE v1.anno = v2.anno
;
