CPGE Oujda                                                                                                                                                Spé

Exercices SQL

Exercice1 : Soit le schéma relationnel suivant :

ARTICLES (NOART, LIBELLE, STOCK, PRIXINVENT)
FOURNISSEURS (NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
ACHETER (NOFOUR#, NOART#, PRIXACHAT, DELAI)

Question 1 : numéros et libellés des articles dont le stock est inférieur à 10 ?

Question 2 : Liste des articles dont le prix d'inventaire est compris entre 100 et 300 ?

Question 3 : Liste des fournisseurs dont on ne connaît pas l'adresse ?

Question 4 : Liste des fournisseurs dont le nom commence par "STE" ?

Question 5 : noms et adresses des fournisseurs qui proposent des articles pour lesquels le délai d'approvisionnement est supérieur à 20 jours ?

Question 6  : Nombre d'articles référencés ?

Question 7 : Valeur du stock ?

Question 8 : numéros et libellés des articles triés dans l'ordre décroissant des stocks ?

Question 9 : Liste pour chaque article (numéro et libellé) du prix d'achat maximum, minimum et moyen ?

Exercice2 :  Soit le schéma relationnel suivant :

REPRESENTATION (n°représentation, titre_représentation, lieu)
MUSICIEN (nom, n°représentation*)
PROGRAMMER (date, n°représentation*, tarif)

Q1 - Donner la liste des titres des représentations.

Q2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.

Q3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.

Q4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.

Exercice3 :  Soit le schéma relationnel suivant :

ETUDIANT(N°Etudiant, Nom, Prénom)
MATIERE(CodeMat, LibelléMat, CoeffMat)
EVALUER(N°Etudiant*, CodeMat*, Date, Note)

Q1 - Quel est le nombre total d'étudiants ?

Q2 - Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plus basse ?

Q3 - Quelles sont les moyennes de chaque étudiant dans chacune des matières ?

Q4 - Quelles sont les moyennes par matière ?

Q5 - Quelle est la moyenne générale de chaque étudiant ?

Q6 - Quelle est la moyenne générale de la promotion ?

Q7 - Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion ?

Exercice4 :  Soit le schéma relationnel suivant :

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*)

Q1 - Quelle est la composition de l'équipe FESTINA (Numéro, nom et pays des

Q2 - Quel est le nombre de kilomètres total du Tour de France 97 ?

Q3 - Quel est le nombre de kilomètres total des étapes de type HAUTE MONTAGNE ?

Q4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?

Q5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?

Q6 - 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 ?

Solutions Exercices SQL

Exercice1 : Soit le schéma relationnel suivant :

ARTICLES (NOART, LIBELLE, STOCK, PRIXINVENT)
FOURNISSEURS (NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
ACHETER (NOFOUR#, NOART#, PRIXACHAT, DELAI)

Question 1 : numéros et libellés des articles dont le stock est inférieur à 10 ?

SELECT NOART, LIBELLE

FROM ARTICLES  WHERE STOCK<10

Question 2 : Liste des articles dont le prix d'inventaire est compris entre 100 et 300 ?

SELECT *

FROM ARTICLES

WHERE PRIXINVENT BETWEEN 100 AND 300

Question 3 : Liste des fournisseurs dont on ne connaît pas l'adresse ?

 SELECT *

FROM FOURNISSEURS

WHERE ADRFOUR IS NULL;

Question 4 : Liste des fournisseurs dont le nom commence par "STE" ?

SELECT *

FROM FOURNISSEURS

WHERE NOMFOUR LIKE 'STE%';

Question 5 : noms et adresses des fournisseurs qui proposent des articles pour lesquels le délai d'approvisionnement est supérieur à 20 jours ?

R1=SELECTION(ACHETER, DELAI>20)
R2=JOINTURE(R1, FOURNISSEURS, NOFOUR)
R2=PROJECTION(R1, NOMFOUR, ADRFOUR, VILLEFOUR)

Testez votre solution SQL :

SELECT NOMFOUR, ADRFOUR, VILLEFOUR

FROM FOURNISSEURS F JOIN ACHETER A ON F.NOFOUR=A.NOFOUR

WHERE DELAI>20;

Question 6  : Nombre d'articles référencés ?

NbArticles=CALCULER(ARTICLES, Comptage())

Testez votre solution SQL :

SELECT COUNT(*) AS NbArticles

FROM ARTICLES; SELECT COUNT(*) AS NbArticles

FROM ARTICLES;

Question 7 : Valeur du stock ?

ValeurStock=CALCULER(ARTICLES, Somme(STOCK*PRIXINVENT))

SELECT SUM(STOCK*PRIXINVENT) AS ValeurStock

FROM ARTICLES;

Question 8 : numéros et libellés des articles triés dans l'ordre décroissant des stocks ?

 R1=PROJECTION(ARTICLES, NOART, LIBELLE, STOCK)
R2=TRI(R1, STOCK décroissant)

Testez votre solution SQL :

SELECT NOART, LIBELLE, STOCK

FROM ARTICLES

ORDER BY STOCK DESC ; SELECT NOART, LIBELLE, STOCK

FROM ARTICLES

ORDER BY STOCK DESC ;

Question 9 : Liste pour chaque article (numéro et libellé) du prix d'achat maximum, minimum et moyen ?

R1=REGROUPER_ET_CALCULER(ACHETER, NOART, PAMAX : MAX(PRIXACHAT), PAMIN : MIN(PRIXACHAT), PAMOY : MOYENNE(PRIXACHAT))
R2=JOINTURE(R1, ARTICLES, NOART)
R3=PROJECTION(R2, NOART, LIBELLE, PAMAX, PAMIN, PAMOY)

Testez votre solution SQL :

SELECT A.NOART, LIBELLE, MAX(PRIXACHAT) AS PMAX, MIN(PRIXACHAT) AS PMIN, AVG(PRIXACHAT) AS PMOY

FROM ACHETER A JOIN ARTICLES B ON A.NOART = B.NOART

GROUP BY A.NOART, LIBELLE;

Exercice2 :  Soit le schéma relationnel suivant :

REPRESENTATION (n°représentation, titre_représentation, lieu)
MUSICIEN (nom, n°représentation*)
PROGRAMMER (date, n°représentation*, tarif)

Q1 - Donner la liste des titres des représentations.

R = PROJECTION(REPRESENTATION, titre_représentation)
Et en langage SQL...

SELECT titre_représentation
FROM REPRESENTATION ;

Q2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.

R1 = SELECTION(REPRESENTATION, lieu="Opéra Bastille")
R2 = PROJECTION(R1, titre_représentation)
Et en langage SQL...

SELECT titre_représentation
FROM REPRESENTATION
WHERE lieu="Opéra Bastille" ;

Q3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.

R1 = JOINTURE(MUSICIEN, REPRESENTATION, Musicien.n°représentation=Représentation.n°représentation)
R2 = PROJECTION(R1, nom, titre_représentation)
Et en langage SQL...

SELECT nom, titre_représentation
FROM MUSICIEN INNER JOIN REPRESENTATION ON MUSICIEN.n°représentation = REPRESENTATION.n°représentation ;

Q4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.

R1 = SELECTION(PROGRAMMER, date=14/09/96)
R2 = JOINTURE(R1, REPRESENTATION, R1.n°représentation=Représentation.n°représentation)
R3 = PROJECTION(R2, titre_représentation, lieu, tarif)
Et en langage SQL...

SELECT titre_représentation, lieu, tarif
FROM REPRESENTATION INNER JOIN PROGRAMMER ON PROGRAMMER.n°représentation = REPRESENTATION.n°représentation
WHERE date='14/06/96' ;

Exercice3 :  Soit le schéma relationnel suivant :

ETUDIANT(N°Etudiant, Nom, Prénom)
MATIERE(CodeMat, LibelléMat, CoeffMat)
EVALUER(N°Etudiant*, CodeMat*, Date, Note)

Q1 - Quel est le nombre total d'étudiants ?

N=CALCULER(ETUDIANT, Comptage())
Et en langage SQL...

SELECT COUNT(*) FROM ETUDIANT ;

Q2 - Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plus basse ?

R=CALCULER(EVALUER, Minimum(Note), Maximum(Note))
Et en langage SQL...

SELECT MIN(Note), MAX(Note) FROM EVALUER ;

Q3 - Quelles sont les moyennes de chaque étudiant dans chacune des matières ?

R1=REGROUPER_ET_CALCULER(EVALUER, N°Etudiant, CodeMat, MoyEtuMat : Moyenne(Note))
R2=JOINTURE(R1, MATIERE, MATIERE.CodeMat=R1.CodeMat)
R3=JOINTURE(R2, ETUDIANT, ETUDIANT.N°Etudiant=R2.N°Etudiant)
MOYETUMAT=PROJECTION(R3, N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat, MoyEtuMat)
Et en langage SQL...

CREATE VIEW MOYETUMAT AS
SELECT ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat, AVG(Note) AS MoyEtuMat
FROM EVALUER INNER JOIN MATIERE ON EVALUER.CodeMat = MATIERE.CodeMat
INNER JOIN ETUDIANT ON EVALUER.N°Etudiant = ETUDIANT.N°Etudiant
GROUP BY ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat;

Remarque : la commande CREATE VIEW va permettre de réutiliser le résultat de la requête (notamment aux deux questions suivantes) comme s'il s'agissait d'une nouvelle table (bien qu'elle soit regénérée dynamiquement lors de son utilisation).
Sous Access, il ne faut pas utiliser la commande CREATE VIEW mais seulement enregistrer la requête. Il est alors possible de s'en resservir comme une table.

Q4 - Quelles sont les moyennes par matière ?

Idem question 3 puis :
R4=REGROUPER_ET_CALCULER(MOYETUMAT, LibelléMat, Moyenne(MoyEtuMat))
Et en langage SQL...

Avec la vue MOYETUMAT de la question 3 :
SELECT LibelléMat, AVG(MoyEtuMat)
FROM MOYETUMAT
GROUP BY LibelléMat ;

Q5 - Quelle est la moyenne générale de chaque étudiant ?

Idem question 3 puis :
MGETU=REGROUPER_ET_CALCULER(MOYETUMAT, N°Etudiant, Nom, Prénom, MgEtu : Somme(MoyEtuMat*CoeffMat)/Somme(CoeffMat))
Et en langage SQL...

Avec la vue MOYETUMAT de la question 3 :
CREATE VIEW MGETU AS
SELECT N°Etudiant, Nom, Prénom, SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM MOYETUMAT
GROUP BY N°Etudiant, Nom, Prénom ;

Q6 - Quelle est la moyenne générale de la promotion ?

Idem question 5 puis :
MG=CALCULER(MGETU, Moyenne(MgEtu))
Et en langage SQL...

Avec la vue MGETU de la question 5 :
SELECT AVG(MgEtu) FROM MGETU ;

Q7 - Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion ?

Idem question 5 et 6 puis :
R=SELECTION(MGETU, MgEtu>=MG)
Et en langage SQL...

Avec la vue MGETU de la question 5 :
SELECT N°Etudiant, Nom, Prénom, MgEtu
FROM MGETU
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU) ;

Exercice4 :  Soit le schéma relationnel suivant :

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*)

Q1 - Quelle est la composition de l'équipe FESTINA (Numéro, nom et pays des coureurs) ?

R1=SELECTION(EQUIPE, NomEquipe="FESTINA")
R2=JOINTURE(R1, COUREUR, R1.CodeEquipe=COUREUR.CodeEquipe)
R3=JOINTURE(R2, PAYS, R2.CodePays=PAYS.CodePays)
R4=PROJECTION(R3, NuméroCoureur, NomCoureur, NomPays)
Et en langage SQL...

SELECT NuméroCoureur, NomCoureur, NomPays
FROM EQUIPE A INNER JOIN COUREUR B ON A.CodeEquipe=B.CodeEquipe
INNER JOIN PAYS C ON B.CodePays=C.CodePays
WHERE NomEquipe="FESTINA" ;

Q2 - Quel est le nombre de kilomètres total du Tour de France 97 ?

N=CALCULER(ETAPE, SOMME(NbKm))
Et en langage SQL...

SELECT SUM(Nbkm) FROM ETAPE ;

Q3 - Quel est le nombre de kilomètres total des étapes de type HAUTE MONTAGNE ?

 
R1=SELECTION(TYPE_ETAPE, LibelléType="HAUTE MONTAGNE")
R2=JOINTURE(R1, ETAPE, R1.CodeType=ETAPE.CodeType)
N=CALCULER(R2, SOMME(NbKm))
Et en langage SQL...

SELECT SUM(Nbkm)
FROM ETAPE A INNER JOIN TYPE_ETAPE B ON A.CodeType=B.CodeType
WHERE LibelléType="HAUTE MONTAGNE" ;

Q4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?

R1=PROJECTION(COUREUR, NuméroCoureur)
R2=PROJECTION(ATTRIBUER_BONIFICATION, NuméroCoureur)
R3=DIFFERENCE(R1,R2)
R4=JOINTURE(R3, COUREUR, R3.NuméroCoureur=COUREUR.NuméroCoureur)
R5=PROJECTION(R4, NomCoureur)
Et en langage SQL...

SELECT NomCoureur
FROM COUREUR
WHERE NuméroCoureur NOT IN (SELECT NuméroCoureur FROM ATTRIBUER_BONIFICATION) ;
ou
SELECT NomCoureur
FROM COUREUR C LEFT JOIN ATTRIBUER_BONIFICATION A ON C.NuméroCoureur=A.NuméroCoureur
WHERE A.NuméroCoureur IS NULL ;  

Q5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?

R1=PROJECTION(PARTICIPER, NuméroCoureur, NuméroEtape)
R2=PROJECTION(ETAPE, NuméroEtape)
R3=DIVISION(R1, R2)
R4=JOINTURE(R3, COUREUR, R3.NuméroCoureur=COUREUR.NuméroCoureur)
R5=PROJECTION(R4, NomCoureur)

ou

N=CALCULER(ETAPE, Comptage())
R1=REGROUPER_ET_CALCULER(PARTICIPER, NuméroCoureur, Nb:Comptage())
R2=SELECTION(R1, Nb=N)
R3=JOINTURE(R2, COUREUR, R2.NuméroCoureur=COUREUR.NuméroCoureur)
R4=PROJECTION(R3, NomCoureur)
Et en langage SQL...

SELECT NomCoureur
FROM PARTICIPER A INNER JOIN COUREUR B ON A.NuméroCoureur=B.NuméroCoureur
GROUP BY NuméroCoureur, NomCoureur
HAVING COUNT(*)=(SELECT COUNT(*) FROM ETAPE) ;

Q6 - 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 ?

R1=SELECTION(PARTICIPER, NuméroEtape=13)
R2=PROJECTION(R1, NuméroCoureur)
     -> R2 représente l'ensemble des coureurs présents jusqu'à la 13ème étape (ce qui va permettre de ne pas prendre en compte dans le classement ceux qui ont abandonné avant !)
R3=JOINTURE(R2, PARTICIPER, R2.NuméroCoureur=PARTICIPER.NuméroCoureur)
R4=SELECTION(R3, NuméroEtape<=13)
R5=REGROUPER_ET_CALCULER(R4, NuméroCoureur, Total:Somme(TempsRéalisé))
R6=JOINTURE(R5, COUREUR, R5.NuméroCoureur=COUREUR.NuméroCoureur)
R7=PROJECTION(R6, NomCoureur, CodeEquipe, CodePays, Total)
R8=TRI(R7, Totalé)
Et en langage SQL...

SELECT NomCoureur, CodeEquipe, CodePays, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A INNER JOIN COUREUR B ON A.NuméroCoureur=B.NuméroCoureur 
WHERE NuméroEtape<=13 AND A.NuméroCoureur IN
 (SELECT NuméroCoureur FROM PARTICIPER WHERE NuméroEtape=13)
GROUP BY A.NuméroCoureur, NomCoureur, CodeEquipe, CodePays ORDER BY 4 ;