Transcript Gesnotes requêtes et regroupements

Support : Cas Gesnotes
Vous disposez du fichier gesnotes.mdb créé sous ACCESS. Téléchargez le à partir de la page précedente et
renommez le en intégrant votre nom (ex : marcelgesnotes.mdb). Il vous servira de support de test des requêtes s
proposées. Ce cas sera une occasion d’aborder les opérations et calculs impliquant un regroupement à l’aide des
opérateurs SUM, AVG, MAX, MIN, COUNT, GROUP BY, HAVING
Voici un aperçu de la structure du fichier de base de données gesnotes à l’aide du modèle de relations ACCESS.
Rédigez le modèle relationnel à la source des tables de gesnotes.mdb.
Il servira de support à l’écriture des requêtes.
Support : Contenu tables Gesnotes
Voici les occurrences contenues dans les différentes tables de gesnotes.mdb
(Extrait)
Questions
1°) Donnez la liste des épreuves passées par les candidats (Nom de l’épreuve et Coeff)
2°) Quelle note a obtenu le candidat PAIDISSE à l’épreuve de Français ?
3°) Calculez le total de points (Note * Coeff) obtenu par chaque candidat dans chaque épreuve.
4°) Quels candidats ont un nombre de points compris entre 50 et 60 à l’épreuve de mathématiques ?
5°) Donnez la liste des établissements de la zone Cambrai.
6°) Quelle épreuve a le plus grand coefficient ?
7°) Quelle est la moyenne simple du candidat FABER dans les 7 épreuves ?
8°) Quelle est la moyenne coefficientée du candidat FABER dans les 7 épreuves ?
9°) Quel candidat a eu la moyenne simple la plus basse ?
10°) Quelle est la moyenne générale coefficientée de tous les candidats ?
Question1 correction
Donnez la liste des épreuves passées par les canidats (Nom de l’épreuve et Coeff)
QBE Access
Résultat
Question2 correction
Quelle note a obtenu le candidat PAIDISSE à l’épreuve de Français ?
QBE Access
Résultat
Question3 correction
QBE Access
Calculez le total de points (Note * Coeff) obtenu par chaque candidat dans chaque
épreuve.
Résultat (Extrait)
Question4 correction
Quels candidats ont un nombre de points compris entre 50 et 60 à l’épreuve de
mathématiques ?
QBE Access
Résultat
Question5 correction
QBE Access
Donnez la liste des établissements de la zone Cambrai.
Résultat
Question6 correction
QBE Access
Quelle épreuve a le plus grand coefficient ?
Résultat
Question7 correction
Quelle est la moyenne simple du candidat FABER dans les 7 épreuves ?
QBE Access
Résultat
Question8 correction
Quelle est la moyenne coefficientée du candidat FABER dans les 7 épreuves ?
QBE Access
Résultat
Question9 correction
Quel candidat a eu la moyenne simple la plus basse ?
QBE Access
Requête91
Requête92
Résultat
Résultat
Résultat
Requête93 (finale)
Question10 correction
Quelle est la moyenne générale coefficientée de tous les candidats ?
QBE Access
Requête101
Résultat
Requête102
Résultat
SQL Standard
1°) SELECT DesignEpreuve, Coeff FROM EPREUVE
2°) SELECT Note FROM NOTATION, EPREUVE, CANDIDAT
WHERE NomCandidat LIKE “Paidisse”
AND DesignEpreuve LIKE “Français”
AND CANDIDAT.NumCandidat = NOTATION.NumCandidat
AND NOTATION.Codeépreuve = EPREUVE. Codeépreuve
3°) SELECT NomCandidat, DesignEpreuve ,
(Note * Coeff) AS Total Points
FROM NOTATION, EPREUVE, CANDIDAT
WHERE CANDIDAT.NumCandidat = NOTATION.NumCandidat
AND NOTATION.Codeépreuve = EPREUVE. Codeépreuve
4°) SELECT NomCandidat
FROM NOTATION, EPREUVE, CANDIDAT
WHERE DesignEpreuve LIKE “Mathématiques”
AND (Note * Coeff) BETWEEN 50 AND 60
AND CANDIDAT.NumCandidat = NOTATION.NumCandidat
AND NOTATION.Codeépreuve = EPREUVE. Codeépreuve
5°) SELECT NomEtab FROM ETABLISSEMENT, ZONE
WHERE LibelléZone LIKE « Cambrai »
AND ETABLISSEMENT.CodeZone = ZONE.CodeZone
6°) SELECT Désignépreuve, Coeff
FROM EPREUVE
WHERE Coeff IN(SELECT Max(Coeff) FROM EPREUVE)
Le IN peut être remplacé par le = ici
7°) SELECT NomCandidat, AVG(Note) AS Moyenne simple
FROM CANDIDAT,NOTATION
WHERE CANDIDAT.NumCandidat = NOTATION.NumCandidat
GROUP BY CANDIDAT.NomCandidat
HAVING CANDIDAT.NomCandidat LIKE "FABER"
8°) En 3 étapes
R1 : SELECT NomCandidat, Sum([Coeff]*[Note]) AS [Tot pts]
FROM CANDIDAT, NOTATION, EPREUVE
WHERE CANDIDAT.NumCandidat = NOTATION.NumCandidat
AND NOTATION.Codeépreuve = EPREUVE.Codeépreuve
GROUP BY NomCandidat
HAVING CANDIDAT.NomCandidat) LIKE "FABER"
R2 : SELECT Sum(Coeff) AS Tot coeff FROM EPREUVE
Requête finale :
SELECT NomCandidat, (Tot Gen Pts/Tot coeff) AS Moyenne coefficientée
FROM R1, R2
En une seule requête :
SELECT NomCandidat, Sum(([Note]*[Coeff])/(SELECT Sum(Coeff)
FROM EPREUVE) AS Moy coefficientée
FROM CANDIDAT , NOTATION, EPREUVE
WHERE CANDIDAT.NumCandidat = NOTATION.NumCandidat
AND NOTATION.Codeépreuve = EPREUVE.Codeépreuve
GROUP BY NomCandidat
HAVING CANDIDAT.NomCandidat) LIKE "FABER"
9°) En 3 étapes
R1 : SELECT NomCandidat, Avg(Note) AS Moyenne simple
FROM CANDIDAT, NOTATION
WHERE CANDIDAT.NumCandidat = NOTATION.NumCandidat
GROUP BY NomCandidat
R2 : SELECT Min(Moyenne simple) AS Mini Moy FROM R1
Requête finale :
SELECT NumCandidat, NomCandidat, Moyenne simple
FROM R1, R2 WHERE Moyenne simple = Mini Moy
10°) En 2 étapes
R1 : SELECT NomCandidat, Sum(([Note]*[Coeff])/(SELECT Sum(Coeff)
FROM EPREUVE) AS Moy coefficientée
FROM CANDIDAT , NOTATION, EPREUVE
WHERE CANDIDAT.NumCandidat = NOTATION.NumCandidat
AND NOTATION.Codeépreuve = EPREUVE.Codeépreuve
GROUP BY NomCandidat
Requête finale :
SELECT Avg(Moy coefficientée) AS Moy générale des moy coeff FROM R1