database sql -web

1. ESERCITAZIONI: Algebra relazionale e QUERY
E’ dato il seguente diagramma E/R di esempio:
Cognome
Nome
Classe
Sezione
Specializzazione
CodClasse
1
Classe
HaInsegnante
Cap
Frequenta
Citta
N
E’Frequentata
Frequenta
Studente
1
Riceve
Riceve
Insegna
Insegna
N
E’Ricevuta
CodProf
CodVal
Professore
Cap
Indirizzo
Matricola
N
NumOre
DataNascita
N
Valutazione
ClasseConcorso
Citta
Nome
Indirizzo
Cognome
DataNascita
Materia
DataVal
Voto
Si ricava il seguente SCHEMA RELAZIONALE (MAPPING DEL DIAGRAMMA ER)
Classe (CodClasse, Classe, Sezione, Specializzazione)
Studente (Matricola, Cognome, Nome, DataNascita, Indirizzo, Cap, Citta, Email, CodClasse1)
Con l’attributo “CodClasse1” FK sulll’attributo “CodClasse” della relazione “Classe”
VRCodClasse1 (Studente)  VRCodClasse (Classe) deriva dalla TOTALITA’ della associazione diretta “Frequenta”
VRCodClasse (Classe)  VRCodClasse1 (Studente) deriva dalla TOTALITA’ della associazione inversa “E’Frequentata”
Valutazione (CodVal, DataVal, Voto, Materia, Matricola1)
Con l’attributo “Matricola1” FK sull’attributo “Matricola” della relazione “Studente”
VRMatricola1 (Valutazione)  VRMatricola (Studente) deriva dalla TOTALITA’ della associazione inversa “E’ricevuta”
Professore (CodProf, Cognome, Nome, Data Nascita, Indirizzo, Cap, Citta, ClasseConcorso)
Insegna (CodProf2, CodClasse2, NumOre)
Con l’attributo “CodProf2” FK sull’attributo “CodProf” della relazione “Professore”
Con l’attributo “CodClasse2” FK sull’attributo “CodClasse” della relazione “Classe”
VRCodProf2 (Insegna)  VRCodProf (Professore) deriva dal mapping dell’associazione di molt. N:N “Insegna”
VRCodClasse2 (Insegna)  VRCodClasse (Classe) deriva dal mapping dell’associazione di molt. N:N “Insegna”
VRCodProf (Professore)  VRCodProf2 (Insegna) deriva dalla TOTALITA’ della associazione diretta “Insegna”
VRCodClasse (Classe)  VRCodClasse2 (Insegna) deriva dalla TOTALITA’ della associazione inversa “E’Insegnata”
Email
TESTO DELLE QUERY
Risolvere le seguenti interrogazioni utilizzando, se possibile, l’algebra relazionale e tradurle in SQL:
GLI OPERATORI ALGEBRICI IN SQL
Q1. Elencare tutte le valutazioni con voto sufficiente
Q2. Elencare tutte le valutazioni in “Italiano” con voto sufficiente
Q3. Elencare tutte le valutazioni in una materia fornita in ingresso dall’utente
Q4. Elencare tutte le classi, con la relativa specializzazione, presenti in istituto
Q5. Elencare tutte le specializzazioni presenti in istituto
Q6. Elencare gli studenti dell’istituto con le rispettive valutazioni
Q7. Elencare gli studenti del’istituto con le rispettive classi
Q8. Elencare i nominativi degli studenti dell’istituto con le rispettive classi
Q9. Elencare i nominativi degli studenti dell’istituto che hanno valutazioni in “Informatica”
Q10. Elencare i professori dell’istituto con le rispettive classi in cui insegnano
Q11. Salvare in una nuova tabella ProfessoriInsegnanoClassi i nominativi dei professori
dell’istituto con le rispettive classi in cui insegnano
Q12. Utilizzando la nuova tabella creata al punto precedente, elencare i nominativi dei
professori che insegnano nelle classi di una specializzazione fornita in ingresso dall’utente
Q13. Elencare i nominativi degli studenti dell’istituto, con le rispettive classi, che frequentano
una specializzazione fornita in ingresso
Q14. Elencare le coppie di studenti con matricola diversa che vengono dalla stessa città
Q15. Elencare le valutazioni degli studenti dell’istituto
Q16. Elencare i professori dell’istituto della specializzazione “INFORMATICA” con le rispettive
classi
Q17. Elencare gli studenti dell’istituto con le rispettive valutazioni, considerando anche quelli
al momento senza alcun voto
OPERATORI INSIEMISTICI IN SQL
Q18. Elencare i nomi di tutti i docenti che insegnano nelle terze o nelle quarte
Q19. Elencare i nomi di tutti i docenti che insegnano sia nelle terze sia nelle quarte
Q20. Elencare i nominativi dei docenti che non provengono da Napoli
Q21. Elencare le matricole, cognome
“ELETTRONICA” sia in “INFORMATICA”
e
nome
degli
studenti
con
valutazioni
Q22. Elencare le matricole degli studenti che non hanno al momento valutazioni
sia
in
OPERATORI DI AGGIORNAMENTO IN SQL
INSERT
Q23. Inserire il nuovo professore con i seguenti
“01/01/1970”, “Via Rodi, 5”, “80100”, “Napoli”, “A033”
dati
“P-05”,”Magnino”,
“Paolo”,
Q24. Inserire il nuovo professore del quali si conoscono solo i seguenti dati “P-05”,”Magnino”,
“Paolo”, “01/01/1970” (evidentemente tutti gli altri sono attributi OPZIONALI)
Q25. Accodare alla tabella Professore la tabella Supplente dallo stesso formato contenente
l’elenco dei supplenti dell’istituto
UPDATE
Q26. Aggiornare la tabella Classe modificando in “MECCATRONICA” la specializzazione
precedentemente indicata “MECCANICA”
Q27. Aggiornare la tabella Valutazione modificando il nome della materia “TECNOLOGIA” in
“TECNICA”
Q28. Aggiornare la tabella delle Valutazioni incrementando di una unità i voti insufficienti nelle
valutazioni in “INFORMATICA”
DELETE
Q29. Cancellare dalla tabella Valutazioni le valutazioni insufficienti
Q30. Cancellare tutti i professori presenti in istituto
ALTRE CARATTERISTICHE DELLA SELECT
Q31. Visualizzare le valutazioni riguardanti le materie “INFORMATICA” ed “ELETTRONICA”
Q32. Visualizzare le valutazioni
specializzazione “INFORMATICA”
insufficienti
nelle
materie
non
di
indirizzo
della
Q33. Visualizzare le materie alle cui valutazioni corrispondono voti tra il 6 ed 8 (estremi
compresi)
Q34. Visualizzare le valutazioni del mese di dicembre 2009
Q35. Visualizzare le valutazioni nelle materie di laboratorio
Q36. Visualizzare gli studenti il cui cognome inizia per “V”
Q37. Visualizzare gli studenti la cui matricola inizia con “M” ed il cui terzo carattere è un
numero (totale 4 caratteri) e la cui città inizia con “RO”
Q38. Visualizzare i nominativi e gli indirizzi degli studenti che hanno un indirizzo email
ORDINARE I DATI
Q39. Ordinare alfabeticamente le righe della tabella professori in base agli attributi Cognome e
Nome
Q40. Elencare le valutazioni in “INFORMATICA” in ordine di voto decrescente
LE FUNZIONI DI AGGREGAZIONE
FUNZIONE SUM
Q41. Calcolare il numero totale di ore di insegnamento del professore con codice “P-01”
FUNZIONE AVG
Q42. Calcolare la media dei voti dello studente con matricola “M-01”
FUNZIONE MAX E MIN
Q43. Ricercare il voto minimo ed il voto massimo assegnato durante una valutazione di
“INFORMATICA”
Q44. Ricercare il primo cognome tra gli studenti
FUNZIONE COUNT
Q45. Determinare il numero complessivo di professori dell’istituto
Q46. Determinare il numero di valutazioni sufficienti
Q47. Determinare
“INFORMATICA”
il
numero
di
classi
dell’istituto
appartenenti
alla
specializzazione
Q48. Determinare il numero di specializzazioni presenti in istituto
RAGGRUPPAMENTI
Q49. Per ogni diversa materia determinare il numero di valutazioni effettuate, visualizzando
l’elenco delle materie in ordine alfabetico crescente con il corrispondente valore calcolato
Q50. Per ogni diversa materia calcolare la media dei voti delle valutazioni effettuate,
visualizzando in ordine decrescente di media, l’elenco delle materie con il corrispondente
valore della media calcolato
Q51. Calcolare per ogni professore il numero totale di ore di insegnamento visualizzando poi
l’elenco dei nominativi dei professori con i corrispondenti valori calcolati in ordine decrescente
di nominativo
Q52. Calcolare per ogni studente la media dei voti riportati nelle valutazioni di
“INFORMATICA” visualizzando in ordine decrescente di media l’elenco dei nominativi degli
studenti ed i corrispettivi valori calcolati
Q53. Per ogni diversa materia determinare il numero di valutazioni effettuate visualizzando
l’elenco delle materie alle quali corrisponde più di una valutazione
Q54. Per ogni diversa materia determinare la media dei voti delle valutazioni effettuate,
visualizzando in ordine decrescente di media, l’elenco delle materie in cui il valore della media
restituito è compreso tra 5 e 6 (estremi compresi)
Q55. Visualizzare, in ordine alfabetico, i nominativi degli studenti con la media dei voti in
“INFORMATICA” che non superi il 6
QUERY NIDIFICATE
Q56. Visualizzare i nominativi degli studenti che hanno conseguito in “INFORMATICA” il voto
massimo assegnato in questa materia
Q57. Visualizzare, in ordine alfabetico, i nominativi dei professori che hanno un numero totale
di insegnamento superiore a quelle del professore con codice “P-03”
Q58. Visualizzare i nominativi degli studenti che frequentano classi con più di 1 allievo
Q59. Visualizzare, in ordine alfabetico, i nominativi dei professori che non insegnano nella
“3H” “INFORMATICA”
Q60. Visualizzare i codici dei professori che insegnano nella “3h” e nella “4H” “INFORMATICA”
Q61. Visualizzare i nominativi degli studenti della “3H” “INFORMATICA” che hanno preso voti
in “INFORMATICA” superiori ad almeno uno dei voti di “INFORMATICA” degli studenti della
“4H” “INFORMATICA”
Q62. Visualizzare i nominativi della “3H” “INFORMATICA” che hanno preso voti di
“INFORMATICA” superiori a tutti i voti di “INFORMATICA” degli studenti della “4H”
“INFORMATICA”
Q63. Visualizzare la media degli studenti di “INFORMATICA” solo se esistono studenti iscritti a
questa specializzazione
SOLUZIONI
Q1. Elencare tutte le valutazioni con voto sufficiente
SQL
SELECT *
FROM Valutazione
WHERE (Voto >= 6);
Algebra relazionale

Voto >= 6
(Valutazione)
Q2. Elencare tutte le valutazioni in “Informatica” con voto sufficiente
SQL
SELECT *
FROM Valutazione
WHERE (Materia = “Informatica”) AND (Voto >= 6);
Algebra relazionale

(Materia = “Informatica”) AND (Voto >= 6)
(Valutazione)
Q3. Elencare tutte le valutazioni in una materia fornita in ingresso dall’utente
SQL
SELECT *
FROM Valutazione
WHERE Materia = [MateriaX];
Algebra relazionale

Materia = [MateriaX]
(Valutazione)
Q4. Elencare tutte le classi, con la relativa specializzazione, presenti in istituto
SQL
SELECT Classe, Sezione, Specializzazione
FROM Classe;
Algebra relazionale
 Classe, Sezione, Specializzazione (Classe)
Q5. Elencare tutte le specializzazioni presenti in istituto
SQL
SELECT DISTINCT Specializzazione
FROM Classe;
Algebra relazionale
 Specializzazione (Classe)
Q6. Elencare gli studenti dell’istituto con le rispettive valutazioni
SQL
SELECT *
FROM Studente, Valutazione
WHERE Studente.Matricola=Valutazione.Matricola1;
Algebra relazionale
(Studente  Valutazione)
Matricola=Matricola1
Q7. Elencare gli studenti del’istituto con le rispettive classi
SQL
SELECT *
FROM Studente, Classe
WHERE Studente.CodClasse1 = Classe.CodClasse;
Algebra relazionale
(Studente  Classe)
CodClasse1 = CodClasse
Q8. Elencare i nominativi degli studenti dell’istituto con le rispettive classi
SQL
SELECT Cognome, Nome, Classe, Sezione, Specializzazione
FROM Studente, Classe
WHERE Studente.CodClasse1 = Classe.CodClasse;
Algebra relazionale
 Cognome,Nome, Classe, Sezione, Specializzazione (Studente  Classe)
CodClasse1 = CodClasse
Q9. Elencare i nominativi degli studenti dell’istituto che hanno valutazioni in “Informatica”
SQL
SELECT DISTINCT Studente.Cognome, Studente.Nome
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1) AND (Materia = “INFORMATICA”);
Algebra relazionale
 Cognome,Nome (Materia = “INFORMATICA” (Studente  Valutazione) )
Matricola=Matricola1
SELECT DISTINCT Studente.Cognome, Studente.Nome
FROM Studente, (SELECT * FROM Valutazione WHERE (Materia = “INFORMATICA”) AS t1)
WHERE (Studente.Matricola=t1.Matricola1);
Algebra relazionale
 Cognome,Nome ( (Studente  Materia = “INFORMATICA” (Valutazione) )
Matricola=Matricola1
Q10. Elencare i professori dell’istituto con le rispettive classi in cui insegnano
SQL
SELECT Cognome, Nome, Classe, Sezione, Specializzazione
FROM Professore, Insegna, Classe
WHERE (Professore. CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2=Classe.CodClasse);
Algebra relazionale
A =  Cognome, Nome, Classe, Sezione, Specializzazione 
 A (Professore

(Insegna  Classe) )
CodProf=CodProf2 CodClasse2= CodClasse)
Q11. Salvare in una nuova tabella ProfessoriInsegnanoClassi i nominativi dei professori
dell’istituto con le rispettive classi in cui insegnano
SQL
SELECT Cognome, Nome, Classe, sezione, Specializzazione INTO ProfessoriInsegnanoClassi
FROM Professore, Insegna, Classe
WHERE (Professore. CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2=Classe.CodClasse);
Algebra relazionale
Vedi sopra
Q12. utilizzando la nuova tabella creata al punto precedente, elencare i nominativi dei
professori che insegnano nelle classi di una specializzazione fornita in ingresso dall’utente
SQL
SELECT DISTINCT Cognome, Nome
FROM ProfessoriInsegnanoClassi
WHERE Specializzazione = [SpecializzazioneX];
Algebra relazionale
 Cognome, Nome (Specializzazione = [SpecializzazioneX] (ProfessoriInsegnanoClassi) )
Q13. Elencare i nominativi degli studenti dell’istituto, con le
frequentano una specializzazione fornita in ingresso
rispettive classi, che
SQL
SELECT Cognome, Nome, Classe, Sezione
FROM Studente, Classe
WHERE (Studente.CodClasse1 = Classe.CodClasse) AND (Specializzazione = [SpecializzazioneX]);
Algebra relazionale
 Cognome,Nome, Classe, Sezione(Specializzazione = [SpecializzazioneX] (Studente  Classe)
CodClasse1 = CodClasse
SELECT Cognome, Nome, Classe, Sezione
FROM Studente, (SELECT * FROM Classe WHERE (Specializzazione = [SpecializzazioneX])) AS t1
WHERE (Studente.CodClasse1 = t1.CodClasse);
Algebra relazionale
 Cognome,Nome, Classe, Sezione (Studente  Specializzazione = [SpecializzazioneX] (Classe) )
CodClasse1 = CodClasse
Q14. Elencare le coppie di studenti con matricola diversa che vengono dalla stessa città
SQL
SELECT t1.Cognome, t1.Nome, t2.Cognome, t2.Nome
FROM Studente AS t1, Studente AS t2
WHERE (t1.Matricola<>t2.Matricola) And (t1.Citta=t2.Citta);
Algebra relazionale
 t1.Cognome, t1.Nome, t2.Cognome, t2.Nome (t1.Matricola<>t2.Matricola (t1  t2)
t1.Citta=t2.Citta
Q15. Elencare le valutazioni degli studenti dell’istituto
SQL
SELECT Cognome, Nome, Materia, Voto
FROM Studente, Valutazione
WHERE (Studente.Matricola = Valutazione.Matricola1);
SELECT Cognome, Nome, Materia, Voto
FROM Studente INNER JOIN Valutazione ON Studente.Matricola = Valutazione.Matricola1;
Algebra relazionale
 Cognome,Nome, Materia, Voto (Studente  Valutazione)
Matricola=Matricola1
Q16. Elencare i professori dell’istituto della specializzazione “INFORMATICA” con le
rispettive classi
SQL
SELECT Cognome, Nome, Classe, Sezione
FROM Professore, Insegna, Classe
WHERE (Professore. CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2=Classe.CodClasse)
AND (Specializzazione = “INFORMATICA”);
Algebra relazionale
A =  Cognome, Nome, Classe, Sezione 
P =  Specializzazione = “INFORMATICA” 
 A (P (Professore

(Insegna  Classe) ) )
CodProf=CodProf2
CodClasse2= CodClasse)
SELECT Cognome, Nome, Classe, Sezione
FROM Professore, Insegna, (SELECT * FROM Classe WHERE (Specializzazione = “INFORMATICA”)) AS t1
WHERE (Professore. CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2=t1.CodClasse);
Algebra relazionale
 A (Professore

(Insegna 
CodProf=CodProf2

P
(Classe) ) )
CodClasse2= CodClasse)
Q17. Elencare gli studenti dell’istituto con le rispettive valutazioni, considerando anche quelli
al momento senza alcun voto
SQL
SELECT Cognome, Nome, Materia, Voto
FROM Studente LEFT JOIN Valutazione ON (Studente.Matricola = Valutazione.Matricola1);
Algebra relazionale
SX
 Materia, Voto, Cognome, Nome (Studente  Valutazione)
Matricola=Matricola1
SELECT Materia, Voto, Cognome, Nome
FROM Valutazione RIGHT JOIN Studente ON (Valutazione.Matricola1 = Studente.Matricola);
Algebra relazionale
DX
 Materia, Voto, Cognome, Nome (Valutazione  Studente)
Matricola1=Matricola
Q18. Elencare i nomi di tutti i docenti che insegnano nelle terze o nelle quarte
SQL
(SELECT Cognome, Nome
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2=Classe.CodClasse)
AND (Classe=”3”) )
UNION
(SELECT Cognome, Nome
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2=Classe.CodClasse)
AND (Classe=”4”) );
Algebra relazionale
A =  Cognome, Nome 
P =  Classe=”3”  P1 =  Classe=”3” 
( A (Professore  (Insegna  
P
CodProf=CodProf2
(Classe)))
CodClasse2= CodClasse)
)  ( A (Professore  (Insegna  
CodProf=CodProf2
P1
(Classe)))
)
CodClasse2= CodClasse)
Q19. Elencare i nomi di tutti i docenti che insegnano sia nelle terze sia nelle quarte
SQL
(SELECT Cognome, Nome
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2=Classe.CodClasse)
AND (Classe=”3”) )
INTERSECT
(SELECT Cognome, Nome
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2=Classe.CodClasse)
AND (Classe=”4”) );
Algebra relazionale
A =  Cognome, Nome 
P =  Classe=”3”  P1 =  Classe=”4” 
( A (Professore  (Insegna  
P
CodProf=CodProf2
(Classe)))
CodClasse2= CodClasse)
)  ( A (Professore  (Insegna  
CodProf=CodProf2
P1
(Classe)))
)
CodClasse2= CodClasse)
SELECT CodProf, Cognome, Nome, Classe
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) And (Insegna.CodClasse2=Classe.CodClasse)
(Classe='3')
and CodProf = ANY (oppure SOME)
(
SELECT CodProf
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) And
(Insegna.CodClasse2=Classe.CodClasse) And (Classe='4')
);
SELECT CodProf, Cognome, Nome, Classe
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) And (Insegna.CodClasse2=Classe.CodClasse)
(Classe='3')
and CodProf IN
(
SELECT CodProf
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) And
(Insegna.CodClasse2=Classe.CodClasse) And (Classe='4')
);
And
And
Q20. Elencare i nominativi dei docenti che non provengono da Napoli
SQL
(SELECT Cognome, Nome
FROM Professore)
MINUS
(SELECT Cognome, Nome
FROM Professore
WHERE (Citta=”NAPOLI”) );
Algebra relazionale
A =  Cognome, Nome 
P =  Citta=”NAPOLI” 
(
A
(Professore) )
- (
A
(

P
(Professore )) )
SELECT Cognome, Nome
FROM Professore
WHERE CodProf <> ALL
(SELECT CodProf
FROM Professore
WHERE (Citta=”NAPOLI”));
SELECT Cognome, Nome
FROM Professore
WHERE CodProf NOT IN
(SELECT CodProf
FROM Professore
WHERE (Citta=”NAPOLI”));
SELECT Cognome, Nome
FROM Professore
WHERE CodProf IN
(SELECT CodProf
FROM Professore
WHERE (Citta<>”NAPOLI”));
Q21. Elencare gli studenti (matricola,
“ELETTRONICA” sia in “INFORMATICA”
cognome
e
nome)
con
valutazioni
sia
SQL
(SELECT Matricola, Cognome, Nome
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1) AND (Materia=”ELETTRONICA”) )
INTERSECT
(SELECT Matricola, Cognome, Nome
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1) AND (Materia=”INFORMATICA”) );
Algebra relazionale
A =  Matricola, Cognome, Nome 
P =  Materia=”ELETTRONICA”  P1 =  Materia=”INFORMATICA” 
(A ( Studente  P (Valutazione)) )  (A (Studente  P1(Valutazione)) )
Matricola=Matricola1
N.B. Vedi Query 19 per versioni alternative
Matricola=Matricola1
in
Q22. Elencare le matricole degli studenti che non hanno al momento valutazioni
SQL
(SELECT Matricola
FROM Studente )
MINUS
(SELECT Matricola
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1) );
Algebra relazionale
(
Matricola
(Studente) )
- (
Matricola
(Studente  Valutazione) )
Matricola=Matricola1
SELECT Matricola, Cognome, Nome
FROM Studente
WHERE Matricola <> ALL
(SELECT Matricola
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1));
SELECT Matricola, Cognome, Nome
FROM Studente
WHERE Matricola NOT IN
(SELECT Matricola
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1));
Q23. Inserire il nuovo professore con i seguenti
“01/01/1970”, “Via Rodi, 5”, “80100”, “Napoli”, “A033”
dati
“P-05”,”Magnino”,
“Paolo”,
SQL
INSERT INTO Professore(CodProf, Cognome, Nome, DataNascita, Indirizzo, Cap, Citta, ClasseConcorso )
VALUES (“P-05”, “Magnino”, “Paolo”, “01/01/1970”, “Via Rodi, 5”, “80100”, “Napoli”, “A033”);
INSERT INTO Professore
VALUES (“P-05”, “Magnino”, “Paolo”, “01/01/1970”, “Via Rodi, 5”, “80100”, “Napoli”, “A033”);
Q24. Inserire il nuovo professore del quali si conoscono solo i seguenti dati obbligatori
“P-05”,”Magnino”, “Paolo”, “01/01/1970” (con tutti gli altri attributi OPZIONALI)
SQL
INSERT INTO Professore ( CodProf, Cognome, Nome, DataNascita )
VALUES (“P-05”, “Magnino”, “Paolo”, “01/01/1970”);
Q25. Accodare alla tabella Professore la tabella Supplente dallo stesso formato contenente
l’elenco dei supplenti dell’istituto
SQL
INSERT INTO Professore
SELECT *
FROM Supplente;
Q26. Aggiornare la tabella Classe modificando in “MECCATRONICA” la specializzazione
precedentemente indicata “MECCANICA”
SQL
UPDATE Classe SET Specializzazione = “MECCATRONICA”
WHERE Specializzazione = ”MECCANICA”;
Q27. Aggiornare la tabella Valutazione modificando il nome della materia “TECNOLOGIA” in
“TECNICA”
SQL
UPDATE Valutazione SET Materia = “TECNICA”
WHERE Materia =”TECNOLOGIA”;
Q28. Aggiornare la tabella delle Valutazioni incrementando di una unità i voti insufficienti
nelle valutazioni in “INFORMATICA”
SQL
UPDATE Valutazione SET Voto = Voto + 1
WHERE (Materia = ”INFORMATICA”) AND (Voto <= 5);
Q29. Cancellare dalla tabella Valutazioni le valutazioni insufficienti
SQL
DELETE FROM Valutazione
WHERE Voto <= 5;
Q30. Cancellare tutti i dati della tabella ProfessoriInsegnanoClassi creata con la query Q11
SQL
DELETE FROM ProfessoriInsegnanoClassi;
Q31. Visualizzare le valutazioni riguardanti le materie “INFORMATICA”, “SISTEMI”, ed
“ELETTRONICA”
SQL
SELECT *
FROM Valutazione
WHERE (Materia =“INFORMATICA”) OR (Materia =”ELETTRONICA”) OR (Materia =”SISTEMI”);
EQUIVALENTE A
SELECT *
FROM Valutazione
WHERE Materia IN (“INFORMATICA”,”ELETTRONICA”,”SISTEMI”);
Q32. Visualizzare le valutazioni
specializzazione “INFORMATICA”
insufficienti
nelle
materie
non
di
indirizzo
della
SQL
SELECT DataVal, Voto, Materia
FROM Valutazione, Studente,Classe
WHERE (Valutazione.Matricola1=Studente.Matricola) AND (Studente.CodClasse1=Classe.CodClasse)
AND (Specializzazione="INFORMATICA") AND (Voto <= 5)
AND (Materia NOT IN (“INFORMATICA”,”ELETTRONICA”,”SISTEMI”);
Q33. Visualizzare le materie alle cui valutazioni corrispondono voti tra il 6 ed 8 (estremi
compresi)
SQL
SELECT DISTINCT Materia
FROM Valutazione
WHERE Voto BETWEEN 8 AND 10;
Q34. Visualizzare le valutazioni del mese di dicembre 2009
SQL
SELECT *
FROM Valutazione
WHERE DataVal BETWEEN “01/12/2009” AND “31/12/2009”; // NOTAZIONE STANDARD
SELECT *
FROM Valutazione
WHERE DataVal BETWEEN #01/12/2009# AND #31/12/2009#; // NOTAZIONE ACCESS
Q35. Visualizzare le valutazioni nelle materie di laboratorio
SQL
SELECT *
FROM Valutazione
WHERE Materia LIKE “LAB*”;
Q36. Visualizzare gli studenti il cui cognome inizia per “V”
SQL
SELECT *
FROM Studente
WHERE Cognome LIKE “V*”;
Q37. Visualizzare gli studenti la cui matricola inizia con “M” ed il cui terzo carattere è un
numero (totale 4 caratteri) e la cui città inizia con “RO”
SQL
SELECT *
FROM Studente
WHERE (matricola LIKE “M?#?”) AND (Citta LIKE “RO*”);
Q38. Visualizzare i nominativi e gli indirizzi degli studenti che hanno un indirizzo email
SQL
SELECT *
FROM Studente
WHERE Email IS NOT NULL;
N.B. Ovviamente questi sono gli studenti senza un indirizzo email
SELECT *
FROM Studente
WHERE Email IS NULL;
Q39. Ordinare alfabeticamente in senso crescente le righe della tabella professori in base
agli attributi Cognome e Nome
SQL
SELECT *
FROM Professore
ORDER BY Cognome [ASC], Nome[ASC];
N.B. Se si volesse ordinarli in senso decrescente
SELECT *
FROM Professore
ORDER BY Cognome DESC, NomeDESC;
Q40. Elencare le valutazioni in “INFORMATICA” in ordine di voto decrescente
SQL
SELECT *
FROM Valutazione
WHERE (Materia = “INFORMATICA”)
ORDER BY Voto DESC;
Q41. Calcolare il numero totale di ore di insegnamento del professore con codice “P-01”
SQL
SELECT SUM (NumOre) AS TotaleOreIns
FROM Insegna
WHERE (CodProf2 = “P-01”);
Q42. Calcolare la media dei voti dello studente con matricola “M-01”
SQL
SELECT AVG (Voto) AS MediaVoti
FROM Valutazione
WHERE (Matricola1 = “M-01”);
Q43. Ricercare il voto minimo ed il voto massimo assegnato durante una valutazione di
“INFORMATICA”
SQL
SELECT MAX (Voto) AS VotoMax, MIN (Voto) AS VotoMin
FROM Valutazione
WHERE (Materia = “INFORMATICA”);
Q44. Ricercare il primo cognome tra gli studenti
SQL
SELECT MIN (Cognome) AS PrimoCognome
FROM Studente;
N.B. Se si volesse invece l’ultimo
SELECT MAX (Cognome) AS UltimoCognome
FROM Studente;
Q45. Determinare il numero complessivo di professori dell’istituto
SQL
SELECT COUNT(*) AS TotProf
FROM Professore;
o equivalentemente
SELECT COUNT(CodProf) AS TotProf
FROM Professore;
Q46. Determinare il numero di valutazioni sufficienti
SQL
SELECT COUNT(*) AS TotSuff
FROM Valutazione
WHERE Voto >= 6;
Q47. Determinare il numero di classi dell’istituto appartenenti
“INFORMATICA”
alla specializzazione
SQL
SELECT COUNT(*) AS NumClassiInf
FROM Classe
WHERE Specializzazione = “INFORMATICA”;
o equivalentemente
SELECT COUNT(Classe) AS NumClassiInf
FROM Classe
WHERE Specializzazione = “INFORMATICA”;
Q48. Determinare il numero di specializzazioni presenti in istituto
SQL
SELECT DISTINCT COUNT(Specializzazione) AS NumSpec
FROM Classe;
o equivalentemente con la subquery
SELECT COUNT (Specializzazione) AS NumSpec
FROM
(SELECT Specializzazione
FROM Classe
GROUP BY (Specializzazione)
);
SELECT COUNT(*) AS NumSpecializzazioni
FROM (SELECT DISTINCT Specializzazione FROM Classe);
Q49. Per ogni diversa materia determinare il numero di valutazioni effettuate, visualizzando
l’elenco delle materie in ordine alfabetico crescente
SQL
SELECT Materia, COUNT(*) AS NumVal
FROM Valutazione
GROUP BY Materia
ORDER BY Materia;
Q50. Per ogni diversa materia calcolare la media dei voti delle valutazioni effettuate,
visualizzando i risultati in ordine decrescente di media
SQL
SELECT Materia, AVG(Voto) AS Media
FROM Valutazione
GROUP BY Materia
ORDER BY AVG(Voto) DESC;
Q51. Calcolare per ogni professore il numero totale di ore di insegnamento visualizzando poi
l’elenco dei nominativi dei professori con i corrispondenti valori calcolati in ordine
decrescente di nominativo
SQL
SELECT CodProf, Cognome, Nome, SUM(NumOre) AS TotOre
FROM Professore, Insegna
WHERE Professore.Codprof=Insegna.CodProf2
GROUP BY CodProf, Cognome, Nome
ORDER BY Cognome DESC;
Q52. Calcolare per ogni studente la media dei voti riportati nelle valutazioni di
“INFORMATICA” visualizzando in ordine decrescente di media l’elenco dei nominativi degli
studenti ed i corrispettivi valori calcolati
SQL
SELECT Matricola, Cognome, Nome, AVG(Voto) AS Media
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1) AND (Materia =”INFORMATICA”)
GROUP BY Matricola, Cognome, Nome
ORDER BY AVG(Voto) DESC;
Q53. Per ogni diversa materia determinare il numero di valutazioni effettuate visualizzando
l’elenco delle materie alle quali corrisponde più di una valutazione
SQL
SELECT Materia, COUNT(*) AS NumVal
FROM Valutazione
GROUP BY Materia
HAVING COUNT(*) > 1;
Q54. Per ogni diversa materia determinare la media dei voti delle valutazioni effettuate,
visualizzando in ordine decrescente di media, l’elenco delle materie in cui il valore della
media restituito è compreso tra 5 e 6 (estremi compresi)
SQL
SELECT Materia, AVG(Voto) AS NumVal
FROM Valutazione
GROUP BY Materia
HAVING AVG(Voto) BETWEEN 5 AND 6
ORDER BY AVG(Voto) DESC;
Q55. Visualizzare, in ordine alfabetico, i nominativi degli studenti con la media dei voti in
“INFORMATICA” che non superi il 6
SQL
SELECT Matricola, Cognome, Nome, AVG(Voto) AS MediaInf
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1) AND (Materia =”INFORMATICA”)
GROUP BY Matricola, Cognome, Nome
HAVING AVG(Voto) <= 6
ORDER BY Cognome, Nome;
Q56. Visualizzare i nominativi degli studenti che hanno conseguito in “INFORMATICA” il voto
massimo assegnato in questa materia
SQL
SELECT Cognome, Nome
FROM Studente, Valutazione
WHERE (Studente.Matricola=Valutazione.Matricola1) AND (Materia=”INFORMATICA”)
AND (Voto = (
SELECT MAX(Voto)
FROM Valutazione
WHERE (Materia=”INFORMATICA”)
) );
Q57. Visualizzare, in ordine alfabetico, i nominativi dei professori che hanno un numero
totale di insegnamento superiore a quelle del professore con codice “P-03”
SQL
SELECT Cognome, Nome
FROM Professore, Insegna
WHERE (Professore.CodProf=Insegna.CodProf2)
GROUP BY Cognome, Nome
HAVING SUM(NumOre) >
(
SELECT SUM ( NumOre)
FROM Insegna
WHERE (CodProf2=”P-03”)
)
ORDER BY Cognome, Nome;
Q58. Visualizzare i nominativi degli studenti che frequentano classi con più di 1 allievo
SQL
SELECT Cognome, Nome
FROM Classe
WHERE (CodClasse1 IN
(
SELECT CodiceClasse1
FROM Studente
GROUP BY CodiceClasse1
HAVING COUNT(*) >=2
) );
Q59. Visualizzare, in ordine alfabetico, i nominativi dei professori che non insegnano nella
“3H” “INFORMATICA”
SQL
SELECT Cognome, Nome
FROM Professore
WHERE (CodProf NOT IN
(
SELECT CodProf2
FROM Insegna, Classe
WHERE (Insegna.CodClasse2 = Classe.CodClasse) AND (Classe=”3”) AND
(Sezione=”H”) AND (Specilaizzazione=”INFORMATICA”)
) );
Q60. Visualizzare
“INFORMATICA”
i
codici
dei
professori
che
insegnano
nella
“3H”
e
nella
“4H”
SQL
SELECT CodProf2
FROM Insegna, Classe
WHERE (Insegna.CodClasse2 = Classe.CodClasse) AND (Classe=”3”)
(Specializzazione=”INFORMATICA”)
AND (CodProf2 IN
(
SELECT CodProf2
FROM Insegna, Classe
WHERE (Insegna.CodClasse2 = Classe.CodClasse)
(Sezione=”H”) AND (Specializzazione=”INFORMATICA”)
) );
AND
(Sezione=”H”)
AND
AND
(Classe=”4”)
AND
Variante con i nominativi dei docenti
SELECT CodProf, Cognome, Nome
FROM Professore, Insegna, Classe
WHERE (Professore.CodProf=Insegna.CodProf2) AND (Insegna.CodClasse2 = Classe.CodClasse)
AND (Classe=”3”) AND (Sezione=”H”) AND (Specializzazione=”INFORMATICA”)
AND (CodProf IN
(
SELECT CodProf2
FROM Insegna, Classe
WHERE (Insegna.CodClasse2 = Classe.CodClasse) AND (Classe=”4”) AND
(Sezione=”H”) AND (Specializzazione=”INFORMATICA”)
) );
Q61. Visualizzare i nominativi degli studenti della “3H” “INFORMATICA” che hanno preso
voti in “INFORMATICA” superiori ad almeno uno dei voti di “INFORMATICA” degli studenti
della “4H” “INFORMATICA”
SQL
SELECT DISTINCT, Cognome, Nome
FROM Valutazione, Studente, Classe
WHERE (Valutazione.Matricola1=Studente.Matricola) AND (Studente.CodClasse1 = Classe.CodClasse)
AND (Classe=”3”) AND (Sezione=”H”) AND (Specializzazione=”INFORMATICA”)
AND (Materia = “INFORMATICA”)
AND (Voto > ANY
(
SELECT Voto
FROM Valutazione, Studente, Classe
WHERE (Valutazione.Matricola1=Studente.Matricola) AND (Studente.CodClasse1 =
Classe.CodClasse)
AND
(Classe=”4”)
AND
(Sezione=”H”)
AND
(Specializzazione=”INFORMATICA”) AND (Materia = “INFORMATICA”)
) );
Q62. Visualizzare i nominativi della “3H” “INFORMATICA” che hanno preso voti di
“INFORMATICA” superiori a tutti i voti di “INFORMATICA” degli studenti della “4H”
“INFORMATICA”
SQL
SELECT DISTINCT, Cognome, Nome
FROM Valutazione, Studente, Classe
WHERE (Valutazione.Matricola1=Studente.Matricola) AND (Studente.CodClasse1 = Classe.CodClasse)
AND (Classe=”3”) AND (Sezione=”H”) AND (Specializzazione=”INFORMATICA”)
AND (Materia = “INFORMATICA”)
AND (Voto > ALL
(
SELECT Voto
FROM Valutazione, Studente, Classe
WHERE (Valutazione.Matricola1=Studente.Matricola) AND (Studente.CodClasse1 =
Classe.CodClasse)
AND
(Classe=”4”)
AND
(Sezione=”H”)
AND
(Specializzazione=”INFORMATICA”) AND (Materia = “INFORMATICA”)
) );
Q63. Visualizzare la media degli studenti di “INFORMATICA” solo se esistono studenti iscritti
a questa specializzazione
SQL
SELECT AVG(Voto) AS MediaVotiInf
FROM Valutazione, Studente, Classe
WHERE (Valutazione.Matricola1=Studente.Matricola) AND (Studente.CodClasse1 = Classe.CodClasse)
AND (Specializzazione=”INFORMATICA”)
AND EXISTS
(
SELECT *
FROM Studente, Classe
WHERE (Studente.CodClasse1 = Classe.CodClasse) AND
(Specializzazione=”INFORMATICA”)
) );
SELECT AVG(Voto) AS MediaVotiInf
FROM Valutazione, Studente, Classe
WHERE (Valutazione.Matricola1=Studente.Matricola) AND (Studente.CodClasse1 = Classe.CodClasse)
AND (Specializzazione=”LINGUISTICA”)
AND EXISTS
(
SELECT *
FROM Studente, Classe
WHERE (Studente.CodClasse1 = Classe.CodClasse) AND
(Specializzazione=”LINGUISTICA”)
) );