--Vista su 4 tabelle
CREATE VIEW CLASSE(studente, insegnante, materia, voto, data) AS
SELECT st_cognome || ' ' || st_nome, in_cognome || ' ' || in_nome, mt_descr, vr_voto, vr_data
FROM studenti JOIN verifiche ON vr_studente = st_matricola
JOIN materie ON mt_sigla = vr_materia
JOIN insegnanti ON mt_docente = in_codice

-- Le ultime 10 query le codifico partendo dalla vista CLASSE

--11 Aggiungere al punto precedente anche la media dei voti conseguiti

SELECT studente, materia, COUNT(*), AVG(voto)
FROM CLASSE
GROUP BY studente, materia
ORDER BY studente, materia

-- SELECT * FROM CLASSE
--12 Media e numero di verifiche fatte da ogni singolo studente suddiviso per materia.

SELECT studente, materia, ROUND(AVG(voto), 2) AS media, COUNT(*) AS num_verifiche
FROM CLASSE
GROUP BY studente, materia
ORDER BY studente, materia

--13 stampare il registro degli insegnanti: Nome insegnante e materia,
--elenco degli studenti con numero di verifiche fatte e media dei voti.


SELECT insegnante, materia, studente, COUNT(*) AS num_verifiche, ROUND(AVG(voto),2) AS media 
FROM classe
GROUP BY insegnante, materia, studente
ORDER BY insegnante, materia, studente


--14 Elenco degli studenti con la media generale insufficiente.

SELECT   studente, ROUND(AVG(voto),2) AS media_generale
FROM classe
GROUP BY  studente
HAVING AVG(voto) < 6
ORDER BY  studente


--15. Elenco degli studenti insufficienti in una determinata materia.
-- Query fatta senza vista


SELECT st_cognome, st_nome, ROUND(AVG(vr_voto),2) AS media_generale
FROM studenti, verifiche
WHERE vr_materia = 'ITA' AND vr_studente = st_matricola
GROUP BY st_cognome, st_nome
HAVING AVG(vr_voto) < 6
ORDER BY st_cognome, st_nome

--Oppure unendo la vista con la tabella materia su un campo in comune
SELECT studente, ROUND(AVG(voto),2)
FROM materie, classe
WHERE materia = mt_descr AND mt_sigla='ITA' -- prova a togliere materia=mt_descr e ....
GROUP BY studente
HAVING AVG(voto) < 6
ORDER BY studente


-- 16. Elenco degli studenti con il numero di insufficienze conseguite in ogni materia.
SELECT studente, COUNT(*) AS num_insuff
    FROM classe
    WHERE voto < 6
    GROUP BY studente
    ORDER BY studente;
	

-- 17. Elenco degli insegnanti con il numero di insufficienze assegnate.
SELECT COUNT(*) as num_insuff_assegnate, insegnante, materia
    FROM classe
    WHERE voto < 6
    GROUP BY insegnante, materia
    ORDER BY num_insuff_assegnate DESC, insegnante;

-- 18. Elenco degli studenti con il voto massimo e minimo conseguito in ogni materia.
SELECT materia, studente, MAX(voto), MIN(voto)
    FROM classe
    GROUP BY studente, materia
    ORDER BY materia, studente;

-- 19. Elenco degli insegnanti con il voto massimo e minimo assegnati.
SELECT insegnante, MAX(voto) AS voto_max, MIN(voto) AS voto_min
FROM classe
GROUP BY insegnante
ORDER BY insegnante

-- 19. Elenco degli insegnanti con il voto massimo e minimo assegnati. (Con anche la materia)
SELECT insegnante, materia, MAX(voto) AS voto_max, MIN(voto) AS voto_min
FROM classe
GROUP BY insegnante, materia
ORDER BY insegnante

--20. Elenco degli studenti che non hanno ancora svolto verifiche in una determinata materia.
--Produce solo il codice studente

SELECT DISTINCT vr_studente
FROM verifiche
where vr_studente NOT IN (
SELECT vr_studente
FROM verifiche
LEFT JOIN studenti ON st_matricola = vr_studente
WHERE vr_materia = 'ITA'
ORDER BY st_cognome
);

-- 20 ricavata da sopra, incrociando la tabella studenti per prelevare nome e cognome
SELECT st_nome, st_cognome
FROM studenti
WHERE st_matricola IN 
(
	SELECT DISTINCT vr_studente
	FROM verifiche
	WHERE vr_studente NOT IN 
	(
		SELECT vr_studente
		FROM verifiche
		LEFT JOIN studenti ON st_matricola = vr_studente
		WHERE vr_materia = 'ITA'
		ORDER BY st_cognome
	)
);