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.

  1. Afficher quel était l'âge moyen des garçons et des filles au premier janvier 2000.
  2. Afficher le nom et le grade des enseignants d'histoire.
  3. Afficher les noms et numéro des étudiants qui n'ont pas de notes en Sociologie.
  4. Afficher le nom et le coefficient des matières qui sont enseignées par des maîtres de conférences ou des assistants.
  5. Afficher pour chaque étudiant (nom et numéro), et par ordre alphabétique, la moyenne qu'il a obtenue dans chaque matière.
  6. 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.
  7. Afficher, pour chaque étudiant (nom et numéro) qui a une note dans chacune des matières, la moyenne obtenue au diplôme.
  8. Afficher le nom le grade et l'ancienneté des enseignants qui enseignent dans plus d'une matière.
  9. Afficher le nombre de garçons et le nombre de filles qui ont réussi au diplôme : moyenne >= 10.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172Requê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;  
Article publié le 13 Décembre 2011