2. ESERCITAZIONI: Algebra relazionale e QUERY
E’ dato il seguente diagramma E/R di esempio:
TESTO DELLE QUERY
Risolvere le seguenti interrogazioni utilizzando, se possibile, l’algebra relazionale e tradurle in SQL:
Q1: Ottenere solo il nome ed il cognome degli studenti
Q2: Ottenere tutti i dati relativi agli studenti che hanno più di 16 anni
Q3: Ottenere il nome ed il cognome di tutti gli studenti della 5H che hanno almeno 17 anni
Q4: Ottenere tutti gli studenti della 5H che non si chiamano “Gennaro”
Q5: Ottenere il nome ed il cognome degli studenti che hanno svolto almeno una prova di
‘INFORMATICA’ (senza ripetizioni)
Q6: Ottenere il nome, il cognome dei docenti ed il numero di prove da essi effettuate
Q7: Ottenere il nome ed il cognome degli studenti che hanno riportato in almeno una prova la
sufficienza.
Q8: Elencare tutte le classi cui appartengono gli studenti
Q9: Elencare tutte le classi cui appartengono gli studenti in ordine alfabetico (crescente è il
default)
Q10: Elencare tutte le classi cui appartengono gli studenti in ordine alfabetico (decrescente
per classe e per sezione)
Q11: Elencare il nome dei docenti che hanno svolto prove in “INFORMATICA”
Q12: Elencare la materia delle prove ed il nome dei docenti che hanno assegnato un voto alla
studentessa “MARIA” “LATELLA”
Q13: Elencare i docenti che hanno il cognome che inizia con la lettera ‘M’
Q14: Ottenere tutti i dati relativi agli studenti che hanno un età compresa tra 17 e 19 anni
ordinati per classe di appartenenza decrescente
Q15: Dire quanti sono gli studenti che hanno un età compresa tra 17 e 19 anni
Q16: Dire quanti sono gli studenti per ciascuna classe di appartenenza
Q17: Dire quanti sono gli studenti per ciascuna classe di appartenenza mettendo le classi in
ordine decrescente
Q18: Dire qual è la media tra le età di tutti gli studenti
Q19: Dire qual è l’età media di ciascuna classe/sezione
Q20: Dire qual è la somma delle età di tutti i docenti
SOLUZIONI
Q1: Ottenere solo il nome ed il cognome degli studenti
SQL
SELECT NomeS, CognomeS
FROM Studente;
Algebra Relazionale
NomeS, CognomeS (Studente)
Q2: Ottenere tutti i dati relativi agli studenti che hanno più di 16 anni
SQL
SELECT *
FROM Studente
WHERE AnniS >= 16;
Algebra Relazionale
AnniS >= 16
(Studente)
Q3: Ottenere il nome ed il cognome di tutti gli studenti della 5H che hanno almeno 17 anni
SQL
SELECT NomeS, CognomeS
FROM Studente
WHERE AnniS >= 17
AND ClasseS = 5
AND SezioneS = 'H';
Algebra Relazionale
NomeS, CognomeS
(
AnniS >= 17 AND ClasseS = 5 AND SezioneS = ‘H’
(Studente)
)
Q4: Ottenere tutti gli studenti della 5H che non si chiamano “Gennaro”
SQL
SELECT *
FROM Studente
WHERE NomeS <> “Gennaro”
AND ClasseS = 5
AND SezioneS = 'H';
Algebra Relazionale
NomeS <>”Gennaro” AND ClasseS = 5 AND SezioneS = ‘H’
(Studente)
Q5: Ottenere il nome ed il cognome degli studenti che hanno svolto almeno una prova di
‘INFORMATICA’ (senza ripetizioni)
SQL
SELECT DISTINCT Studente.NomeS, Studente.CognomeS
FROM Studente, Svolge, Prova
WHERE Studente.IdS = Svolge.IdS1
AND Svolge.IdP1 = Prova.IdP
AND Prova.MateriaP = 'INFORMATICA';
ma anche
SELECT DISTINCT Studente.NomeS, Studente.CognomeS
FROM Studente INNER JOIN (Svolge INNER JOIN Prova ON Svolge.IdP1 = Prova.IdP)
ON Studente.IdS = Svolge.IdS1
WHERE Prova.MateriaP = 'INFORMATICA';
Algebra Relazionale
NomeS, CognomeS
(P (Prova (Studente Svolge)) )
IdP = IdP1
IdS = IdS1
con P = MateriaP = ‘INFORMATICA’
ma anche
NomeS, CognomeS, Voto
(P (Prova) (Studente Svolge)) )
IdP = IdP1
IdS = IdS1
con P = MateriaP = ‘INFORMATICA’
Q6: Ottenere il nome, il cognome dei docenti ed il numero di prove da essi effettuate
SQL
SELECT CognomeD, NomeD, Count(*) As Numero
FROM Docente, Prova
WHERE Docente.IdD = Prova.IdD1
GROUP BY CognomeD, NomeD;
Q7: Ottenere il nome ed il cognome degli studenti che hanno riportato in almeno una prova
la sufficienza.
SQL
SELECT Studente.NomeS, Studente.CognomeS, Svolge.Voto
FROM Studente, Svolge, Prova
WHERE Studente.IdS = Svolge.IdS1
AND Svolge.IdP1 = Prova.IdP
AND Svolge.Voto >= 6;
ma anche
SELECT Studente.NomeS, Studente.CognomeS, Svolge.Voto
FROM Studente INNER JOIN (Svolge INNER JOIN Prova ON Svolge.IdP1 = Prova.IdP)
ON Studente.IdS = Svolge.IdS1
WHERE Svolge.Voto >= 6;
Algebra Relazionale
NomeS, CognomeS, Voto
(P (Prova (Studente Svolge)) )
IdP = IdP1
IdS = IdS1
con P = Voto >= 6
ma anche
NomeS, CognomeS, Voto
(Prova (Studente P (Svolge)) )
IdP = IdP1
con P = Voto >= 6
IdS = IdS1
Q8: Elencare tutte le classi cui appartengono gli studenti
SQL
SELECT DISTINCT Studente.ClasseS, Studente.SezioneS
FROM Studente;
Algebra Relazionale
ClasseS, SezioneS (Studente)
Q9: Elencare tutte le classi cui appartengono gli studenti in ordine alfabetico (crescente è il
default)
SQL
SELECT DISTINCT Studente.ClasseS, Studente.SezioneS
FROM Studente
ORDER BY ClasseS;
Algebra Relazionale
Come sopra
Q10: Elencare tutte le classi cui appartengono gli studenti in ordine alfabetico (decrescente
per classe e per sezione)
SQL
SELECT DISTINCT Studente.ClasseS, Studente.SezioneS
FROM Studente
ORDER BY ClasseS DESC, SezioneS DESC;
Algebra Relazionale
Come sopra
Q11: Elencare il nome dei docenti che hanno svolto prove in “INFORMATICA”
SQL
SELECT Docente.NomeD, Docente.CognomeD
FROM Docente, Prova
WHERE IdD = IdD1
AND Prova.MateriaP = “INFORMATICA”;
ma anche
SELECT Docente.NomeD, Docente.CognomeD
FROM Docente INNER JOIN Prova ON Docente.IdD = Prova.IdD1
WHERE MateriaP = “INFORMATICA”;
Algebra Relazionale
NomeD, CognomeD
(P (Docente Prova) )
IdD = IdD1
con P = MateriaP = “INFORMATICA”
ma anche
NomeS, CognomeS, Voto
(Docente P (Prova) )
IdD = IdD1
con P = MateriaP = “INFORMATICA”
Q12: Elencare la materia delle prove ed il nome dei docenti che hanno assegnato un voto
alla studentessa “MARIA” “LATELLA”
SQL
SELECT Docente.NomeD, Docente.CognomeD, Prova.MateriaP
FROM Docente, Prova, Svolge, Studente
WHERE Docente.IdD = Prova.IdD1
AND Prova.IdP = Svolge.IdP1
AND Svolge.IdS1 = Studente.IdS
AND Studente.NomeS = “MARIA” AND Studente.CognomeS = “LATELLA”;
ma anche
SELECT Docente.NomeD, Docente.CognomeD, Prova.MateriaP
FROM Studente INNER JOIN (Svolge INNER JOIN (Docente INNER JOIN Prova
ON Docente.IdD = Prova.IdD1)
ON Prova.IdP = Svolge.IdP1)
ON Studente.IdS = Svolge.IdS1
WHERE Studente.NomeS = “MARIA” AND Studente.CognomeS = “LATELLA”;
Algebra Relazionale
NomeS, CognomeS, MateriaP
(P ( Studente (Svolge (Docente Prova)) ))
IdS = IdS1
IdP1 = IdP
IdD = IdD1
con P = NomeS = ‘MARIA’ AND CognomeS = “LATELLA”
ma anche
NomeS, CognomeS, MateriaP
(P ( Studente ) (Svolge (Docente Prova)) ))
IdS = IdS1
IdP1 = IdP
IdD = IdD1
con P = NomeS = “MARIA” AND CognomeS = ”LATELLA”
Q13: Elencare i docenti che hanno il cognome che inizia con la lettera ‘M’
SQL
SELECT *
FROM Docente
WHERE NomeD LIKE “M*”;
Algebra Relazionale
NomeD LIKE “M*”
(Docente)
Q14: Ottenere tutti i dati relativi agli studenti che hanno un età compresa tra 17 e 19 anni
ordinati per classe di appartenenza decrescente
SQL
SELECT *
FROM Studente
WHERE AnniS BETWEEN 17 AND 19
ORDER BY ClasseS DESC;
Algebra Relazionale
(AnniS >= 17) AND (AnniS <= 19)
(Studente)
Indirizzo
Q15: Dire quanti sono gli studenti che hanno un età compresa tra 17 e 19 anni
SQL
SELECT COUNT(*) As Numero
FROM Studente
WHERE AnniS BETWEEN 17 AND 19;
Q16: Dire quanti sono gli studenti per ciascuna classe di appartenenza
SQL
SELECT ClasseS, SezioneS, COUNT(*) As Numero
FROM Studente
GROUP BY ClasseS, SezioneS;
Q17: Dire quanti sono gli studenti per ciascuna classe di appartenenza mettendo le classi
in ordine decrescente
SQL
SELECT ClasseS, SezioneS, COUNT(*) As Numero
FROM Studente
GROUP BY ClasseS, SezioneS
ORDER BY ClasseS DESC, SezioneS DESC;
Q18: Dire qual è la media tra le età di tutti gli studenti
SQL
SELECT AVG (AnniS) AS Media
FROM Studente;
Q19: Dire qual è l’età media di ciascuna classe/sezione
SQL
SELECT ClasseS, SezioneS, AVG (AnniS) AS Media
FROM Studente
GROUP BY ClasseS, SezioneS;
Q20: Dire qual è la somma delle età di tutti i docenti
SQL
SELECT SUM (AnniD) AS Somma
FROM Docente;
