Support : Cas Gesnotes

1. 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.

2. Support : Contenu tables Gesnotes Voici les occurrences contenues dans les différentes tables de gesnotes.mdb (Extrait)

3. 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 ?

4. Question1 correction Donnez la liste des épreuves passées par les canidats (Nom de l’épreuve et Coeff) QBE Access Résultat

5. Question2 correction Quelle note a obtenu le candidat PAIDISSE à l’épreuve de Français ? QBE Access Résultat

6. Question3 correction Calculez le total de points (Note * Coeff) obtenu par chaque candidat dans chaque épreuve. QBE Access Résultat (Extrait)

7. Question4 correction Quels candidats ont un nombre de points compris entre 50 et 60 à l’épreuve de mathématiques ? QBE Access Résultat

8. Question5 correction Donnez la liste des établissements de la zone Cambrai. QBE Access Résultat

9. Question6 correction Quelle épreuve a le plus grand coefficient ? QBE Access Résultat

10. Question7 correction Quelle est la moyenne simple du candidat FABER dans les 7 épreuves ? QBE Access Résultat

11. Question8 correction Quelle est la moyenne coefficientée du candidat FABER dans les 7 épreuves ? QBE Access Résultat

12. 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)

13. 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

14. SQL Standard 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 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"