Exercices requêtes avec langage SQL
Exercice n°1 :
Soit le modèle relationnel suivant relatif à une base de données sur des représentations musicales :
REPRESENTATION (n°représentation, titre_représentation, lieu)
MUSICIEN (nom, n°représentation*)
PROGRAMMER (date, n°représentation*, tarif)
Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *
Questions :
1 - Donner la liste des titres des représentations.
2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.
3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.
4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.
Exercice n°2 :
Soit le modèle relationnel suivant relatif à une base de données sur des représentations musicales :
REPRESENTATION (n°représentation, titre_représentation, lieu)
MUSICIEN (nom, n°représentation*)
PROGRAMMER (date, n°représentation*, tarif)
Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *
Questions :
Requête 1 - Donner la liste des titres des représentations.
Requête 2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.
Requête 3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.
Requête 4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.
Exercice n°3 :
Soit le modèle relationnel suivant relatif
ETUDIANT (Numetu, Nometu, Dtnaiss, Cdsexe)
SEXE (Cdsexe, Lbsexe)
ENSEIGNANT (Numens, Nomens, Grade, Ancien)
MATIERE (Numat, Nomat, Coeff, Numens)
NOTES (Numetu, Numat, Note)
É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.
Exercice n°4 :
Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes du Tour de France 97, dont une des étapes de
type "contre la montre individuel" se déroula à Saint-Etienne :
EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif)
COUREUR(NuméroCoureur, NomCoureur, CodeEquipe*, CodePays*)
PAYS(CodePays, NomPays)
TYPE_ETAPE(CodeType, LibelléType)
ETAPE(NuméroEtape, DateEtape, VilleDép, VilleArr, NbKm, CodeType*)
PARTICIPER(NuméroCoureur*, NuméroEtape*, TempsRéalisé)
ATTRIBUER_BONIFICATION(NuméroEtape*, km, Rang, NbSecondes, NuméroCoureur*)
Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *
Questions :
1 - Quelle est la composition de l'équipe Festina (Numéro, nom et pays des coureurs) ?
2 - Quel est le nombre de kilomètres total du Tour de France 97 ?
3 - Quel est le nombre de kilomètres total des étapes de type "Haute Montagne"?
4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?
6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue des 13
premières étapes sachant que les
bonifications ont été intégrées dans les temps réalisés à chaque étape ?
7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?
Correction de l'exercice n°1
1 - Donner la liste des titres des représentations.
SELECT titre_représentation FROM REPRESENTATION ;
2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.
SELECT titre_représentation FROM REPRESENTATION
WHERE lieu="Opéra Bastille" ;
3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.
SELECT nom, titre_représentation
FROM MUSICIEN, REPRESENTATION
WHERE MUSICIEN.n°représentation = REPRESENTATION.n°représentation ;
4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.
SELECT titre_représentation, lieu, tarif
FROM REPRESENTATION, PROGRAMMER
WHERE PROGRAMMER.n°représentation =
REPRESENTATION.n°représentation
AND date='14/06/96' ;
Correction de l'exercice n°2
1 - Donner la liste des titres des représentations.
SELECT titre_représentation FROM REPRESENTATION ;
2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.
SELECT titre_représentation FROM REPRESENTATION
WHERE lieu="Opéra Bastille" ;
3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.
SELECT nom, titre_représentation
FROM MUSICIEN, REPRESENTATION
WHERE MUSICIEN.n°représentation = REPRESENTATION.n°représentation ;
4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.
SELECT titre_représentation, lieu, tarif
FROM REPRESENTATION, PROGRAMMER
WHERE PROGRAMMER.n°représentation = REPRESENTATION.n°représentation
AND date='14/06/96' ;
Correction de l'exercice n°3
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;
2:SELECT Nomens, Grade
FROM ENSEIGNANT E, MATIERE M
WHERE E.Numens=M.Numens
AND Nomat='Histoire';
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');
4:SELECT Nomat, Coeff
FROM MATIERE M, ENSEIGNANT E
WHERE M.Numens=E.Numens
AND Grade IN('PR', 'MCF');
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;
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');
7: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;
8:SELECT Nomens, Grade, Ancien
FROM ENSEIGNANT E, MATIERE M
WHERE E.Numens=M.Numens
GROUP BY Nomens, Grade, Ancien
HAVING COUNT(Nomens)>1;
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;
Correction de l'exercice n°4
1 - Quelle est la composition de l'équipe FESTINA (Numéro, nom et pays des coureurs) ?
SELECT NuméroCoureur, NomCoureur, NomPays
FROM EQUIPE A, COUREUR B, PAYS C
WHERE A.CodeEquipe=B.CodeEquipe And B.CodePays=C.CodePays
And NomEquipe="FESTINA" ;
2 - Quel est le nombre de kilomètres total du Tour de France 97 ?
SELECT SUM(Nbkm) FROM ETAPE ;
3 - Quel est le nombre de kilomètres total des étapes de type HAUTE MONTAGNE ?
SELECT SUM(Nbkm) FROM ETAPE A, TYPE_ETAPE B
WHERE A.CodeType=B.CodeType And LibelléType="HAUTE MONTAGNE" ;
4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
SELECT NomCoureur FROM COUREUR
WHERE NuméroCoureur NOT IN (SELECT NuméroCoureur FROM
ATTRIBUER_BONIFICATION) ;
5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?
SELECT NomCoureur FROM PARTICIPER A, COUREUR B
WHERE A.NuméroCoureur=B.NuméroCoureur
GROUP BY NuméroCoureur, NomCoureur
HAVING COUNT(*)=(SELECT COUNT(*) FROM ETAPE) ;
6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue
des 13 premières étapes sachant que les bonifications ont été intégrées dans les temps réalisés à chaque étape ?
SELECT NomCoureur, CodeEquipe, CodePays, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A, COUREUR B
WHERE A.NuméroCoureur=B.NuméroCoureur and NuméroEtape<=13
GROUP BY A.NuméroCoureur, NomCoureur, CodeEquipe, CodePays
ORDER BY Total;
7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?
SELECT NomEquipe, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A, COUREUR B, EQUIPE C
WHERE A.NuméroCoureur=B.NuméroCoureur And
B.CodeEquipe=C.CodeEquipe
And NuméroEtape<=13
GROUP BY B.CodeEquipe, NomEquipe
ORDER BY Total;
