Exercice création des requêtes SQL

Objectifs :

- Créer des requêtes SQL.

Soit le modèle relationnel suivant relatif à la gestion des notes annuelles d’une promotion d’étudiants :
 

ETUDIANT (N°Etudiant, Nom, Prénom)

MATIERE (CodeMat, LibelléMat, CoeffMat)

EVALUER (N°Etudiant#, CodeMat#, Date_Evaluation, Note)

Remarques : Les clés primaires sont soulignées et les clés étrangères sont marquées par #


Questions :


Créer les requêtes SQL suivantes :


1.    Quel est le nombre total d’étudiants ?

2.    Quelle est, parmi l’ensemble des notes, la note la plus haute et la note la plus basse ?

3.    Quelles sont les notes de chaque étudiant dans chacune des matières ?

4.    Quelles sont les moyennes par  matière ?

5.    Quelle est la moyenne générale de chaque étudiant ?

6.    Quelle est la moyenne générale de la promotion ?

On utilisera la requête de la question 5 comme table source

7.    Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion ?
On utilisera la requête de la question 5 comme table source

Les requêtes SQL

1 - Quel est le nombre total d'étudiants ?

SELECT COUNT(*) FROM ETUDIANT ; 

2 - Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la 
plus basse ?

SELECT MIN(Note), MAX(Note) FROM EVALUER ; 

3 - Quelles sont les moyennes de chaque étudiant dans chacune des matières?

SELECT ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat, 
AVG(Note) AS MoyEtuMat 
FROM EVALUER, MATIERE, ETUDIANT 
WHERE EVALUER.CodeMat = MATIERE.CodeMat 
AND EVALUER.N°Etudiant = ETUDIANT.N°Etudiant 
GROUP BY ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat; 

4 - Quelles sont les moyennes par matière ?

SELECT LibelléMat, AVG(MoyEtuMat) 
FROM MOYETUMAT 
GROUP BY LibelléMat ; 

5 - Quelle est la moyenne générale de chaque étudiant ?

SELECT N°Etudiant, Nom, Prénom, 
SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu 
FROM MOYETUMAT 
GROUP BY N°Etudiant, Nom, Prénom ; 

6 - Quelle est la moyenne générale de la promotion ? 
Avec la vue MGETU de la question 5 :

SELECT AVG(MgEtu) 
FROM MGETU ; 

7 - Quels sont les étudiants qui ont une moyenne générale supérieure ou
égale à la moyenne générale de la promotion ?
Avec la vue MGETU de la question 5 :

SELECT N°Etudiant, Nom, Prénom, MgEtu 
FROM MGETU 
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU) ;