Travaux pratiques sur base des étudiants
Formation-SQL
1 Introduction
2 Exercices
Exercice 1:
Donner les titres des cours ayant au moins un étudiant inscrit et dont le nombre d’heures de
TD est au moins 18.
SELECT t i t r e
FROM UE
WHERE NumUE in ( SELECT NumUE FROM I n s c r i t )
AND HTD >= 1 8 ;
Exercice 2:
Donner les noms des étudiants qui ont ’Albert A.’ comme enseignant.
SELECT DISTINCT NomEt
FROM E t u d i a n t , I n s c r i t , Enseigne , Enseignant
WHERE NomEns = ’ A l b e r t A. ’
AND E t u d i a n t . NumEt = I n s c r i t . NumEt
AND I n s c r i t .NumUE = Enseigne .NumUE
AND Enseignant . NumEns = Enseigne . NumEns ;
Exercice 3:
Donner les noms des enseignants qui enseignent dans la même UE que ’Albert A.’ (sauf Albert
A. lui-même).
SELECT DISTINCT E2 . NomEns
FROM Enseignant E1 , Enseignant E2 , Enseigne C1 , Enseigne C2
WHERE E1 . NumEns = C1 . NumEns
AND E2 . NumEns = C2 . NumEns
AND C1 .NumUE = C2 .NumUE
AND E2 . NomEns <> ’ A l b e r t A. ’
AND E1 . NomEns = ’ A l b e r t A. ’ ;
Exercice 4:
Donner le titre des UEs dont le nombre d’heures total par groupe est au moins 46.
SELECT t i t r e
FROM UE
WHERE hcours + htd + htp >= 4 6 ;
1
Exercice 5:
Donner le nombre d’UE n’ayant pas de TP (on appellera NB_UES l’attribut donnant ce résultat).
SELECT COUNT( ∗ ) AS NB_UES
FROM UE
WHERE HTP = 0 ;
Exercice 6:
Donner le nombre d’étudiants qui suivent le cours d’Analyse (on appellera NB_ETUDIANTS
l’attribut donnant ce nombre).
SELECT COUNT( ∗ ) AS NB_ETUDIANTS
FROM UE NATURAL JOIN I n s c r i t
WHERE T i t r e = ’ Analyse ’ ;
Exercice 7:
Donner la moyenne du nombre d’heures de cours, de TD et de TP par UE. On appelera MOY_COURS
la moyenne des heures, MOY_TD celle des TD et MOY_TP celle des TPs.
SELECT AVG(HCOURS) AS MOY_COURS, AVG(HTD) AS MOY_TD, AVG(HTP) AS MOY_TP
FROM UE ;
Exercice 8:
Donner le numéro de l’UE ayant le plus d’heures par groupe.
SELECT NumUE
FROM UE
WHERE HCours+HTD+HTP >= ALL ( SELECT HCours+HTD+HTP FROM UE) ;
Exercice 9:
Calculer le nombre total de groupes ayant ’Carine C.’ comme chargée de TD. On nommera
NB_GROUPES l’attribut contenant le résultat.
SELECT SUM(NTD) AS NB_GROUPES
FROM Enseigne NATURAL JOIN Enseignant
WHERE NomEns = ’ Carine C . ’ ;
Exercice 10:
Calculer le nombre de groupes de TP par enseignant. On donnera le nom de l’enseignant et on
appellera NB_TP son nombre de groupes de TP.
SELECT NomEns , SUM(NTP) AS NB_TP
FROM ( Enseignant NATURAL JOIN Enseigne )
GROUP BY NumEns, NomEns ;
Exercice 11:
Donner les numéros des enseignants qui effectuent plus de 17 heures de cours magistraux.
SELECT NumEns
FROM ( Enseigne NATURAL JOIN UE)
2
GROUP BY NumEns
HAVING SUM( NCours∗HCours ) > 1 7 ;
Exercice 12:
Donner les numéros des enseignants qui effectuent plus de 17 heures de cours magistraux et
qui participent au cours de ’Bases de donnees’.
SELECT NumEns
FROM ( Enseigne NATURAL JOIN UE)
GROUP BY NumEns
HAVING SUM( NCours∗HCours ) > 17
AND NumEns IN ( SELECT NumEns FROM (UE NATURAL JOIN Enseigne ) WHERE T i t r e = ’ Bases
de donnees ’ ) ;
Exercice 13:
Donner le(s) titre(s) de la (des) UE(s) ayant le plus d’inscrits.
SELECT T i t r e
FROM (UE NATURAL JOIN I n s c r i t )
GROUP BY NumUE, T i t r e
HAVING COUNT( ∗ ) >= ALL ( SELECT COUNT( ∗ ) FROM I n s c r i t GROUP BY NumUE) ;
Exercice 14:
Donner pour chaque étudiant le nombre total d’heures qu’il suit. On donnera dans le résultat
le numéro de l’étudiant ainsi qu’un attribut HEURES qui indiquera son nombre d’heures.
SELECT NumEt , SUM(HCOURS+HTD+HTP) AS HEURES
FROM ( I n s c r i t NATURAL JOIN UE)
GROUP BY NumEt ;
Exercice 15:
Donner pour chaque enseignant le nombre total d’heures qu’il effectue. On donnera dans le
résultat le nom de l’enseignant ainsi qu’un attribut HEURES qui indiquera son nombre d’heures.
SELECT NomEns , SUM( NCours∗HCours+NTD∗HTD+NTP∗HTP) AS HEURES
FROM Enseignant , Enseigne , UE
WHERE Enseignant . NumEns = Enseigne . NumEns
AND Enseigne .NumUE = UE .NumUE
GROUP By Enseignant . NumEns, NomEns ;
Exercice 16:
Donner pour chaque UE le nombre total d’heures tous groupes confondus. On exprimera le
résultat en fonction du titre de l’UE et d’un attribut TOTAL_HEURES indiquant le nombre total
d’heures. On triera le résultat par nombre d’heures croissant.
SELECT t i t r e , SUM( NCours∗HCours+NTD∗HTD+NTP∗HTP) AS TOTAL_HEURES
FROM Enseigne , UE
WHERE Enseigne .NumUE = UE .NumUE
GROUP BY t i t r e , UE .NumUE
ORDER BY SUM( NCours∗HCours+NTD∗HTD+NTP∗HTP) ;
Exercice 17:
3
Donner les couples enseignant/étudiant tel que l’on soit sûr que l’enseignant ne rencontre pas
l’étudiant lors d’un cours, d’un TD ou d’un TP. On indiquera les numéros dans les couples.
SELECT NumEns, NumEt
FROM Enseignant , E t u d i a n t
WHERE (NumEns, NumEt) NOT IN ( SELECT NumEns, NumEt FROM Enseigne NATURAL JOIN
Inscrit ) ;
Exercice 18:
Donner la liste des couples d’étudiants qui suivent au moins deux cours ensemble. On in-
diquera le numéro du premier étudiant par l’attribut ETU_A et celui de second par l’attribut
ETU_B. On ne souhaite pas avoir des couples avec le même numéro pour ETU_A et ETU_B,
mais on souhaite obtenir une relation symétrique (i.e. si on a (3333,4444) dans le résultat, on
doit avoir (4444,3333) également).
SELECT [Link] AS ETU_A, B . NumEt AS ETU_B
FROM I n s c r i t A, I n s c r i t B
WHERE [Link] = B .NumUE
AND A. NumEt <> B . NumEt
GROUP BY A. NumEt , B . NumEt
HAVING COUNT( ∗ ) >= 2 ;
Exercice 19:
Donner le nom du ou des étudiants qui suit le plus grand nombre d’heures de cours.
SELECT NomEt
FROM Etudiant , I n s c r i t , UE
WHERE E t u d i a n t . NumEt = I n s c r i t . NumEt
AND I n s c r i t .NumUE = UE .NumUE
GROUP BY E t u d i a n t . NumEt , NomEt
HAVING SUM( HCours+HTD+HTP) >= ALL
( SELECT SUM( HCours+HTD+HTP)
FROM I n s c r i t , UE
WHERE I n s c r i t .NumUE = UE .NumUE
GROUP BY NumEt) ;
Exercice 20:
Donner le nombre total d’heures de cours/TD/TP dispensées à l’université. On nommera
TOTAL_HEURES ce nombre.
SELECT SUM( NCours∗HCours+NTD∗HTD+NTP∗HTP) AS TOTAL_HEURES
FROM Enseigne NATURAL JOIN UE ;