Exercice récapitulatif en Langage SQL

Rédigé par GC Team, Publié le 13 Décembre 2011, Mise à jour le Mardi, 30 Novembre 1999 00:00
Participez au vote ☆☆☆☆☆★★★★★

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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

Requête 1:
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;
 
Requête 2:
SELECT Nomens, Grade
FROM ENSEIGNANT E, MATIERE M
WHERE E.Numens=M.Numens
AND Nomat='Histoire';
 
Requête 3:
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');
 
Requête 4:
SELECT Nomat, Coeff
FROM MATIERE M, ENSEIGNANT E
WHERE M.Numens=E.Numens
AND Grade IN('PR', 'MCF');
 
Requête 5:
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;
 
Requête 6:
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');
 
Requête7:
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;
 
Requête 8:
SELECT Nomens, Grade, Ancien
FROM ENSEIGNANT E, MATIERE M
WHERE E.Numens=M.Numens
GROUP BY Nomens, Grade, Ancien
HAVING COUNT(Nomens)>1;
 
Requête 9:
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;