Esercizio 2

• Cognome, nome e classe degli studenti che hanno ottenuto voti inferiori a 5 in una certa materia di cui viene fornita la descrizione, facendo comparire ciascun studente una sola volta nell’elenco.
  SELECT DISTINCT CognomeStudente, NomeStudente, Classe
  FROM (Studente INNER JOIN Voti ON Studente.CodiceStudente=Voti.CodiceStudente) INNER JOIN Materia ON Materia.CodiceMateria=Voti.CodiceMateria
  WHERE Voto < 5 AND Materia = [quale materia?];
• Media dei voti di inglese di uno studente di cui si conosce il nome.
  SELECT AVG(Voto) AS MediaVoti
  FROM (Voti INNER JOIN Studente ON Voti.CodiceStudente=Studente.CodiceStudente) INNER JOIN Materia ON Voti.CodiceMateria=Materia.CodiceMateria
  WHERE Materia = 'Inglese' AND CognomeStudente = [Cognome];
• Miglior voto di matematica nella classe terza.
  SELECT MAX(Voto) AS MigliorVoto
  FROM (Voti INNER JOIN Studente ON Voti.CodiceStudente=Studente.CodiceStudente) INNER JOIN Materia ON Voti.CodiceMateria=Materia.CodiceMateria
  WHERE Materia.Materia = “Matematica” AND Studente.Classe = 3;
• Miglior voto e peggior voto di uno studente di cui si conosce il codice.
  SELECT MAX(Voto) AS MigliorVoto, MIN(Voto) AS PeggiorVoto
  FROM Voti INNER JOIN Studente ON Voti.CodiceStudente= Studente.CodiceStudente
  WHERE Studente.CodiceStudente = [Codice dello studente];
• Elenco alfabetico di tutti gli studenti con cognome, nome e numero delle prove sufficienti in una determinata materia.
  SELECT CognomeStudente, NomeStudente, Count(*) AS VotiSufficienti
  FROM Studente INNER JOIN Voti ON Studente.CodiceStudente=Voti.CodiceStudente
  WHERE Voti.Voto >= 6 AND CodiceMateria = [codice della materia]
  GROUP BY CognomeStudente, NomeStudente;
• Per ciascuna materia la descrizione e il numero delle prove svolte nel periodo tra 1 febbraio e il 30 marzo.
  SELECT Materia.Materia, Count(*) AS NumeroProve
  FROM Voti INNER JOIN Materia ON Voti.CodiceMateria=Materia.CodiceMateria
  WHERE DataCompito Between #1/02/2008# AND #30/03/2008#
  GROUP BY Materia;
• Per ciascuna materia il numero di compiti in classe effettuati.
  SELECT Materia, Count(Prove) AS NrProve
  FROM (
  SELECT DataCompito, Materia, Count(*) AS Prove
  FROM Voti INNER JOIN Materia ON Voti.CodiceMateria=Materia.CodiceMateria
  GROUP BY DataCompito, Materia)
  GROUP BY Materia;
• Numero delle prove che un determinato studente, di cui si conosce il cognome e nome, ha sostenuto in ciascuna materia con la descrizione della materia.
  SELECT Materia.Materia, Count(*) AS TotCompiti
  FROM (Voti INNER JOIN Studente ON Voti.CodiceStudente=Studente.CodiceStudente) INNER JOIN Materia ON Voti.CodiceMateria=Materia.CodiceMateria
  WHERE CognomeStudente = [Cogn]
  GROUP BY Materia.Materia;
• Elenco con cognome e nome degli studenti di una determinata classe che hanno il voto minimo inferiore a 5.
  SELECT CognomeStudente, NomeStudente
  FROM Voti INNER JOIN Studente ON Voti.CodiceStudente=Studente.CodiceStudente
  WHERE Classe = 5
  GROUP BY CognomeStudente, NomeStudente
  HAVING MIN(Voto) < 5;
• Cognome,nome degli studenti di una classe che hanno la media dei voti di tutte le prove inferiore alla media dei voti di tutte le prove dell’intera classe.
  SELECT CognomeStudente, NomeStudente, AVG(Voto)
  FROM Studente INNER JOIN Voti ON Studente.CodiceStudente=Voti.CodiceStudente
  WHERE Classe = 5
  GROUP BY CognomeStudente, NomeStudente
  HAVING AVG(Voto) < (SELECT AVG(Voto) FROM Voti INNER JOIN Studente ON Voti.CodiceStudente=Studente.CodiceStudente WHERE Classe = 5);
• Cognome e nome degli studenti della classe terza che sono nati prima di uno qualsiasi tra gli studenti della classe quarta.
  SELECT CognomeStudente, NomeStudente
  FROM Studente
  WHERE Classe = 3 AND DataNascita < ANY (SELECT DataNascita FROM Studente WHERE Classe = 4);
• Cognome, nome degli studenti della classe terza che sono nati prima di tutti gli studenti della classe quarta.
  SELECT CognomeStudente, NomeStudente
  FROM Studente
  WHERE Classe = 3 AND DataNascita < ALL (SELECT DataNascita FROM Studente WHERE Classe = 4);
• Cognome, nome e classe degli studenti che hanno ottenuto nelle prove di una materia un voto uguale al massimo dei voti in quella materia di tutte le classi.
  SELECT CognomeStudente, NomeStudente
  FROM (Studente INNER JOIN Voti ON Studente.CodiceStudente=Voti.CodiceStudente) INNER JOIN Materia ON Materia.CodiceMateria=Voti.CodiceMateria
  WHERE Materia = [quale materia] AND Voto = (SELECT MAX(Voto) FROM Voti INNER JOIN Materia ON Voti.CodiceMateria=Materia.CodiceMateria WHERE Materia = [quale materia]);
• Sigla delle classi che hanno la media dei voti di tutti gli studenti in una materia (di cui viene fornito il codice) inferiore alla media di tutti i voti in quella materia di tutta la scuola.
  SELECT Classe
  FROM Studente INNER JOIN Voti ON Studente.CodiceStudente=Voti.CodiceStudente
  WHERE CodiceMateria = 2
  GROUP BY Classe
  HAVING AVG(Voto) < (SELECT AVG(Voto) FROM Voti WHERE CodiceMateria = 2);
• Cognome, nome e matricola degli studenti che non hanno insufficienze.
  SELECT CognomeStudente, NomeStudente, Studente.CodiceStudente
  FROM Studente INNER JOIN Voti ON Studente.CodiceStudente=Voti.CodiceStudente
  WHERE Studente.CodiceStudente NOT IN (SELECT Distinct(CodiceStudente)
  FROM Voti
  WHERE Voto <6)
  GROUP BY CognomeStudente, NomeStudente, Studente.CodiceStudente;