Exercice Corrigé Acess SQL base de donnees Access - SQL -

Objectifs : - Créer une base de données.
                - Relier les tables.
                - 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 ?

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

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

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

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

----------------------------------------------------------------------------
Correction
----------------------------------------------------------------------------

 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) ;