Exercice récapitulatif en Langage SQL
Objectif :
Créer des requêtes en SQL
Travail à faire:
ETUDIANT (Numetu, Nometu, Dtnaiss, Cdsexe)
SEXE (Cdsexe, Lbsexe)
ENSEIGNANT (Numens, Nomens, Grade, Ancien)
MATIERE (Numat, Nomat, Coeff, Numens)
NOTES (Numetu, Numat, Note)
À partir de la Base de données ETUDIANTS ci dessus, écrire les requêtes SQL permettant de répondre aux questions suivantes.
- Afficher quel était l'âge moyen des garçons et des filles au premier janvier 2000.
- Afficher le nom et le grade des enseignants d'histoire.
- Afficher les noms et numéro des étudiants qui n'ont pas de notes en Sociologie.
- Afficher le nom et le coefficient des matières qui sont enseignées par des maîtres de conférences ou des assistants.
- Afficher pour chaque étudiant (nom et numéro), et par ordre alphabétique, la moyenne qu'il a obtenue dans chaque matière.
- Afficher le nom, l'âge et le sexe des étudiants qui ont eu une note d'informatique supérieure à la moyenne générale de la classe.
- Afficher, pour chaque étudiant (nom et numéro) qui a une note dans chacune des matières, la moyenne obtenue au diplôme.
- Afficher le nom le grade et l'ancienneté des enseignants qui enseignent dans plus d'une matière.
- Afficher le nombre de garçons et le nombre de filles qui ont réussi au diplôme : moyenne >= 10.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172 | Requête 1:SELECT Lbsexe,AVG(DATEDIFF('2000-01-01',Dtnaiss)/365) AS "Age Moyen"FROM SEXE S,ETUDIANT EWHERE E.Cdsexe=S.CdsexeGROUP BY Lbsexe; Requête 2:SELECT Nomens, GradeFROM ENSEIGNANT E, MATIERE MWHERE E.Numens=M.NumensAND Nomat='Histoire'; Requête 3:SELECT DISTINCT(Numetu), NometuFROM ETUDIANTWHERE Numetu NOT IN (SELECT Numetu FROM NOTES N, MATIERE M WHERE N.Numat=M.Numat AND Nomat='Sociologie'); Requête 4:SELECT Nomat, CoeffFROM MATIERE M, ENSEIGNANT EWHERE M.Numens=E.NumensAND Grade IN('PR', 'MCF'); Requête 5:SELECT Nometu, E.Numetu, Nomat, AVG(Note)FROM ETUDIANT E, NOTES N, MATIERE MWHERE E.Numetu=N.NumetuAND N.Numat=M.NumatGROUP BY Nometu, E.Numetu, NomatORDER BY Nometu; Requête 6:SELECT Nometu,DATEDIFF(CURRENT_DATE,Dtnaiss)/365 AS Age, Cdsexe FROM ETUDIANT E, NOTES N, MATIERE MWHERE E.Numetu=N.NumetuAND N.Numat=M.NumatAND Nomat='Informatique'AND Note>(SELECT AVG(Note) FROM NOTES N, MATIERE M WHERE N.Numat=M.Numat AND Nomat='Informatique'); Requête7:SELECT Nometu, E.Numetu, SUM(Note*Coeff)/SUM(Coeff) AS MoyenneFROM ETUDIANT E, NOTES N, MATIERE MWHERE E.Numetu=N.NumetuAND N.Numat=M.NumatGROUP BY Nometu, E.NumetuHAVING COUNT(Note)=5ORDER BY Nometu; Requête 8:SELECT Nomens, Grade, AncienFROM ENSEIGNANT E, MATIERE MWHERE E.Numens=M.NumensGROUP BY Nomens, Grade, AncienHAVING COUNT(Nomens)>1; Requête 9:SELECT Lbsexe, COUNT(Lbsexe)FROM SEXE S, ETUDIANT EWHERE S.Cdsexe=E.CdsexeAND E.Numetu IN (SELECT E.Numetu FROM ETUDIANT E, NOTES N, MATIERE M WHERE E.Numetu=N.Numetu AND N.Numat=M.Numat GROUP BY E.Numetu HAVING (SUM(Note*Coeff)/SUM(Coeff)>=10 AND COUNT(Nomat)=5))GROUP BY Lbsexe; |