Exercice corrigé récapitulatif en Langage SQL

Afficher quel était l’âge moyen des garçons et des filles au premier janvier 2000.

 
SELECT Lbsexe,AVG(DATEDIFF('2000-01-01',Dtnaiss)/365) AS "Age Moyen"
FROM SEXE S,ETUDIANT E
WHERE E.Cdsexe=S.Cdsexe
GROUP BY Lbsexe;

Afficher le nom et le grade des enseignants d’histoire.

SELECT Nomens, Grade
FROM ENSEIGNANT E, MATIERE M
WHERE E.Numens=M.Numens
AND Nomat='Histoire';

Afficher les noms et numéro des étudiants qui n’ont pas de notes en Sociologie.

 
SELECT DISTINCT(Numetu), Nometu
FROM ETUDIANT
WHERE Numetu NOT IN
     (SELECT Numetu FROM NOTES N, MATIERE M
      WHERE N.Numat=M.Numat
      AND Nomat='Sociologie');

Afficher le nom et le coefficient des matières qui sont enseignées par des maîtres de conférences ou des assistants.

 
SELECT Nomat, Coeff
FROM MATIERE M, ENSEIGNANT E
WHERE M.Numens=E.Numens
AND Grade IN('PR', 'MCF');

Afficher pour chaque étudiant (nom et numéro), et par ordre alphabétique, la moyenne qu’il a obtenue dans chaque matière.

 
SELECT Nometu, E.Numetu, Nomat, AVG(Note)
FROM ETUDIANT E, NOTES N, MATIERE M
WHERE E.Numetu=N.Numetu
AND N.Numat=M.Numat
GROUP BY Nometu, E.Numetu, Nomat
ORDER BY Nometu;

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.

 
SELECT Nometu,DATEDIFF(CURRENT_DATE,Dtnaiss)/365 AS Age, Cdsexe
 FROM ETUDIANT E, NOTES N, MATIERE M
WHERE E.Numetu=N.Numetu
AND N.Numat=M.Numat
AND Nomat='Informatique'
AND Note>(SELECT AVG(Note) FROM NOTES N, MATIERE M
       WHERE N.Numat=M.Numat
       AND Nomat='Informatique');

Afficher, pour chaque étudiant (nom et numéro) qui a une note dans chacune des matières, la moyenne obtenue au diplôme.

 
SELECT Nometu, E.Numetu, SUM(Note*Coeff)/SUM(Coeff) AS Moyenne
FROM ETUDIANT E, NOTES N, MATIERE M
WHERE E.Numetu=N.Numetu
AND N.Numat=M.Numat
GROUP BY Nometu, E.Numetu
HAVING COUNT(Note)=5
ORDER BY Nometu;

Afficher le nom le grade et l’ancienneté des enseignants qui enseignent dans plus d’une matière.

 
SELECT Nomens, Grade, Ancien
FROM ENSEIGNANT E, MATIERE M
WHERE E.Numens=M.Numens
GROUP BY Nomens, Grade, Ancien
HAVING COUNT(Nomens)>1;

Afficher le nombre de garçons et le nombre de filles qui ont réussi au diplôme : moyenne >= 10.

 
SELECT Lbsexe, COUNT(Lbsexe)
FROM SEXE S, ETUDIANT E
WHERE S.Cdsexe=E.Cdsexe
AND 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;