Esercizio n 57 pag 286 (da Informatica: Le basi di dati ed il

Esercizio n 57 pag 286
(da Informatica: Le basi di dati ed il linguaggio SQL, Access, MySql, Database in rete – A.Lorenzi – E.Cavalli – Editrice ATLAS)
Creare un database per rappresentare gli esami sostenuti dagli studenti di una università. II database è formato da quattro tabelle
con i dati degli studenti iscritti, dei corsi offerti, con l'indicazione della facoltà che li ha attivati, delle facoltà presenti nell'università e
degli esami sostenuti dagli studenti:
Facoltà (CoclFacoltà, NomeFacoltà, Indirizzo, Citta)
Studenti (Matricola, Cognome, Nome, Indirizzo, Citta, Telefono, CodFacoltà)
Corsi
(CodCorso, NomeCorso, Crediti, CodFacoltà)
Esami (ID, Matricola, CodCorso, Data, Voto)
La tabella Studenti, con chiave Matricola, raccoglie i dati anagrafici degli studenti dell'università e, tramite la chiave esterna
CodFacoltà, permette di sapere a quale facoltà lo studente è iscritto.
La tabella Corsi, con chiave CodCorso, raccoglie le informazioni sui corsi offerti. Ogni corso è caratterizzato da un certo numero di
crediti ed è attivato da una facoltà, identificata mediante la chiave esterna CodFacoltà. Uno studente per laurearsi deve conseguire
un certo numero di crediti: 180 per la laurea di primo livello, conseguita la quale si può iscrivere alle lauree specialistiche. I corsi
delle lauree specialistiche richiedono altri 120 crediti. II valore del numero di crediti è diversa per ogni corso.
La tabella Facoltà con chiave CodFacoltà contiene la descrizione e l'indirizzo della facoltà. La tabella Esami, con chiave ID, serve a
registrare i voti degli studenti. ID è un progressivo assegnato automaticamente per ogni esame registrato. Nella tabella Esami sono
registrati: lo studente che ha sostenuto l'esame, identificato con la chiave esterna Matricola, il corso oggetto della prova,
riconosciuto dalla chiave esterna CodCorso, oltre alla data dell'esame e al voto conseguito. Il voto può assumere qualsiasi valore tra
1 e 30. Un esame si intende superato se il voto è maggiore o uguale a 18.
Creare il database e le tabelle e inserire alcuni dati di prova. Sviluppare in SQL e VB6 le operazioni per:
• aggiornare le quattro tabelle mediante maschere;
• costruire le interrogazioni (specificando la sequenza di operazioni relazionali utilizzate), in grado di fornire le seguenti
informazioni:
1. Elenco degli studenti che hanno superato un certo esame
SELECT Studenti.*
FROM Esami INNER JOIN Studenti ON Esami.matricola = Studenti.matricola
WHERE Esami.id=[Scegli codice esame] AND Esami.voto>18;
2. Elenco degli studenti che hanno sostenuto un certo esame
SELECT Esami.voto, Esami.voto
FROM Esami INNER JOIN Studenti ON Esami.matricola = Studenti.matricola
WHERE Esami.id=[Scegli codice esame];
3. Elenco degli studenti iscritti a facoltà collocate in città diverse da quella di residenza, precisando città di residenza, facoltà e
città sede della facoltà.
SELECT Studenti.*
FROM Studenti INNER JOIN Facolta ON Studenti.codfacolta = Facolta.codafacolta
WHERE Studenti.citta<>Facolta.citta;
4. Elenco degli esami superati in un certo periodo, suddivisi per corso. L’elenco deve contenere matricola, nome e cognome dello
studente, descrizione del corso, data e voto.
SELECT Corsi.nomecorso,Studenti.matricola, Studenti.cognome, Studenti.nome, Esami.data, Esami.voto
FROM (Esami INNER JOIN Corsi ON Esami.codcorso = Corsi.codcorso) INNER JOIN Studenti
Esami.matricola = Studenti.matricola
WHERE Esami.voto>18 and Esami.data between [data inizio] and [data fine]
Order by Corsi.nomecorso;
5. Media dei voti degli esami superati da un dato studente.
SELECT Studenti.matricola, Studenti.cognome, Studenti.nome, Avg(voto) AS MediaVoti
FROM Esami INNER JOIN Studenti ON Esami.matricola = Studenti.matricola
WHERE Studenti.matricola=[Scegli matricola] AND Esami.voto>18
Group by Studenti.matricola, Studenti.cognome, Studenti.nome;
6. Cognome e nome degli studenti con media superiore a un voto prefissato.
SELECT Studenti.cognome, Studenti.nome, Avg(voto) AS MediaVoti
FROM Esami INNER JOIN Studenti ON Esami.matricola=Studenti.matricola
GROUP BY Studenti.cognome, Studenti.nome
Having Avg(voto) > [Scegli la Media] ;
7. Descrizione dei corsi per i quali la media degli esami superati è maggiore di 24.
SELECT Corsi.nomecorso, Avg(Esami.voto) AS MediaVoti
FROM Esami INNER JOIN Corsi ON Esami.codcorso = Corsi.codcorso
WHERE Esami.voto>18
GROUP BY Corsi.nomecorso
HAVING Avg(Esami.voto)>24;
ON