Exercice 1
Soit la base de données relationnelle des vols quotidiens d’une compagnie
aérienne qui
contient les tables Avion, Pilote et Vol.
Table Avion (NA : numéro d'avion (clé primaire),
Nom : nom d'avion,
Capacite : capacité d'avion de type entier,
Localite : ville de localité de l’avion )
Table Pilote (NP : numéro pilote,
Nom : nom du pilote ,
Adresse : adresse du pilote )
Table Vol (NV : numéro de vol ,
NP : numéro de pilote,
NA : numéro avion ,
VD : ville de départ ,
VA : ville d’arrivée ,
HD : heure de départ ,
HA : heure d’arrivée )
Exprimez en SQL les requêtes suivantes:
1) Insérer les avions suivants dans la table Avion :
(100, AIRBUS, 300, RABAT), (101,B737,250,CASA), (101,
B737,220,RABAT)
2) Afficher tous les avions
3) Afficher tous les avions par ordre croissant sur le nom
4) Afficher les noms et les capacités des avions
5) Afficher les localités des avions sans redondance
6) Afficher les avions dans la localité est Rabat ou Casa
7) Modifier la capacité de l’avion numéro 101, la nouvelle
capacité et 220
8) Supprimer les avions dans la capacité et inférieure à
200
9) Afficher la capacité maximale, minimale, moyenne des
avions
10) Afficher les données des avions dont la capacité et la
plus basse
11) Afficher les données des avions dont la capacité et
supérieure à la capacité moyenne
12) Afficher le nom et l’adresse des pilotes assurant les
vols IT100 et IT104
13) Afficher les numéros des pilotes qui sont en service
14) Afficher les numéros des pilotes qui ne sont pas en
service
15) Afficher les noms des pilotes qui conduisent un
AIRBUS
Corrigé de l’exercice 1
1) Insert into avion values (100, 'AIRBUS', 300, 'RABAT');
Insert into avion values (101, 'B737', 250, 'CASA');
Insert into avion values (101, 'B737', 220, 'RABAT');
2) select * from avion;
3) select * from avion order by Nom asc;
4) select nom, Capacite from avion;
5) select distinct Localite from avion;
6) select * from avion where Localite='Rabat' or
Localite='Casa' ;
7) Update avion set Capacite=220 where NA=101;
8) Delete from avion where Capacite <200;
9) Select Max(Capacite), Min(Capacite),
Avg(Capacite) from avion;
10) Select * from avion where Capacite=min(Capacite);
11) Select * from avion where Capacite>=avg(Capacite);
12) Select Nom, Adresse from Pilote, Vol where [Link]
= [Link] and NV='IT100' and NV='IT104';
13) Select NP from Vol;
14) Select NP from Pilote
where NP not in (select NP from Vol);
15) Select [Link] from Pilote, Vol, Avion where
[Link] = [Link] and [Link] = [Link] and
[Link]='AIRBUS';
Exercice 2
On considère la base de données d’un festival de
musique : Dans une représentation peut participer un ou
plusieurs musiciens. Un musicien ne peut participer qu’à
une seule représentation.
Representation(N_Rep, titre_Rep, lieu)
Musicien (N_mus , nom , #N_Rep)
Programmer (Date , #N_Rep , tarif)
Exprimez en SQL les requêtes suivantes :
1)Donner La liste des titres des représentations.
2)Donner La liste des titres des représentations ayant
lieu au « théâtre Rabat ».
3)Donner La liste des noms des musiciens et les titres des
représentations auxquelles ils participent.
4)Donner La liste des titres des représentations, les lieux
et les tarifs du 21/07/2022.
5)Donner Le nombre des musiciens qui participent à la
représentations n°15.
6)Donner Les titres des représentations et leurs dates
dont le tarif ne dépasse pas 30DH.
Corrigé de l’exercice 2
1)SELECT titre_Rep FROM Representation;
2) SELECT titre_Rep FROM Representation where
lieu="Rabat";
3)SELECT [Link], R.titre_Rep FROM Musicien M ,
Representation R where R.Num_rep=M.Num_rep;
4)SELECT R.titre_Rep, [Link],[Link] FROM Programmer
P, Representation R WHERE P.Num_rep = R.Num_rep and
[Link]="21-07-2022";
5)SELECT COUNT (*) FROM Musicien where Num_rep =15;
6)SELECT R.titre_Rep , [Link] FROM Representation R ,
Programmer P where R.N_Rep=P.N_Rep and [Link]<=30 ;
Exercice 3
On considère la base de données suivante:
Départements(N_dep, Nom_dep, VILLE)
Employes(N_E, Nom_E, PROF, SAL, COMM, #N_dep)
Exprimez en SQL les requêtes suivantes :
1)Donnez la liste des employés ayant une commission
2)Donnez les noms, emplois et salaires des employés par
emploi croissant, et pour chaque emploi, par salaire
décroissant
3)Donnez le salaire moyen des employés
4)Donnez le salaire moyen du département Production
5)Donnes les numéros de département et leur salaire
maximum
6)Donnez les différentes professions et leur salaire
moyen
7)Donnez le salaire moyen par profession le plus bas
8)Donnez le ou les emplois ayant le salaire moyen le plus
bas, ainsi que ce salaire moyen
Corrigé de l’exercice 3
1)SELECT * FROM Employes WHERE COMM NOT NULL;
2)SELECT Nom_E, PROF, SAL FROM Employes ORDER BY
PROF ASC, SAL DESC ;
3)SELECT AVG(SAL) FROM Employes ;
4)SELECT AVG([Link]) FROM Employes E , Departement D
where E.N_dep=D.N_dep and
D.Nom_dep="production" ;
5)SELECT N_dep, MAX(SAL) FROM Employes GROUP BY
N_dep ;
6)SELECT PROF, MAX(SAL) FROM Employes GROUP BY
PROF ;
7)SELECT PROF, AVG(SAL) as moy FROM Employes
GROUP BY PROF ORDER BY moy ASC LIMIT 1 ;
Exercice 4
On considère la base de données suivante relatif à la
gestion des notes annuelles d’une promotion
d’étudiants:
ETUDIANT(NEtudiant, Nom, Prenom)
MATIERE(CodeMat, NomMat, CoeffMat)
EVALUER(#NEtudiant, #CodeMat, Date, Note)
Exprimez en SQL les requêtes suivantes:
1) Quel est le nombre total d’étudiants ?
2) Quelles sont, parmi l’ensemble des notes, la note la
plus haute et la note la plus basse ?
3) Quelles sont les moyennes de chaque étudiant dans
chacune des matières ?
4)Quelles sont les moyennes par matière ? Avec la vue
MGETU de la question 3 ( MOYETUMAT)
5)Quelle est la moyenne générale de chaque étudiant ?
Avec la vue MGETU de la question 3 ( MOYETUMAT)
6) Quelle est la moyenne générale de la promotion ? Avec
la vue MGETU de la question 5 :
7) Quels sont les étudiants qui ont une moyenne générale
supérieure ou égale à la moyenne générale de la
promotion? Avec la vue MGETU de la question 5
Corrigé de l’exercice 4
1) SELECT count(*) FROM ETUDIANT ;
2) SELECT MIN(Note) as ’plus basse note’, MAX(Note) as
’plus haute note’ FROM EVALUER ;
3) SELECT [Link], [Link], AVG([Link]) AS
MoyEtuMat
FROM EVALUER EV, MATIERE M, ETUDIANT E WHERE
[Link] = [Link] AND [Link] =
[Link] GROUP BY [Link], [Link] ;
4)SELECT NomMat, AVG(MoyEtuMat) FROM MOYETUMAT
GROUP BY NomMat ;
5) SELECT NEtudiant, SUM
(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM MOYETUMAT GROUP BY NEtudiant ;
6)SELECT AVG(MgEtu) FROM MGETU ;
7) SELECT NEtudiant , Nom , Prenom , MgEtu FROM
MGETU
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU) ;
8)SELECT PROF FROM Employes GROUP BY PROF
HAVING AVG(SAL)=(SELECT AVG(SAL) as moy FROM
Employes GROUP BY PROF ORDER BY moy ASC LIMIT 1) ;
Exercice 5
On considère la base de données suivante:
Developpeur (NumDev, NomDev, AdrDev, EmailDev,
TelDev)
Projet (NumProj, TitreProj, DateDeb, DateFin)
Logiciel (CodLog, NomLog, PrixLog, #NumProj)
Realisation (#NumProj, #NumDev)
Exprimez en SQL les requêtes suivantes:
1)afficher les noms et les prix des logiciels appartenant
au projet ayant comme titre « gestion de stock », triés
dans l’ordre décroissant des prix .
2)afficher le total des prix des logiciels du projet numéro
10. Lors de l’affichage, le titre de la colonne sera « cours
total du projet ».
3)Afficher le nombre de développeurs qui ont participé
au projet intitulé « gestion de stock »
4)Afficher les projets qui ont plus que 5 logiciels.
5) Les numéros et noms des développeurs qui ont
participés dans tout les projets.
6)Afficher Les numéros de projets dans lesquelles tous
les développeurs y participent dans sa réalisation.
Corrigé de l’exercice 5
1)SELECT [Link], [Link] FROM Logiciel L , Projet P
where [Link] =[Link] and [Link]=
"gestion␣de␣stock"
ORDER BY [Link] DESC ;
2) SELECT SUM(PrixLog) as "cout␣total␣du␣projet"
FROM Logiciel WHERE NumPRoj=10 ;
3) SELECT count(*) FROM Developpeur D , Realisation
R, Projet P where [Link] =[Link] and
[Link]= [Link] ;
4) SELECT NumProj, TitreProj FROM PRojet P , Logiciel L
where [Link]=[Link]
GROUP BY NumProj, TitreProj
HAVING count(*)>5 ;
5) SELECT NumDev, NomDev FROM Developpeur D ,
Realisation R where [Link] = [Link]
GROUP BY NumDev, NomDev
HAVING count(*)=(SELECT COUNT(*) FROM Projet) ;
6) SELECT NumProj, TitreProj FROM Projet P , Realisation
R where [Link]=[Link]
GROUP BY NumProj, TitreProj
HAVING count(*)=(SELECT COUNT(*) FROM
Developpeur);
Exercice 6
Soit la base de données "gestion_projet" permettant de
gérer les projets relatifs au développement de logiciels
suivante :
Developpeur (NumDev, NomDev, AdrDev, EmailDev,
TelDev)
Projet (NumProj, TitreProj, DateDeb, DateFin)
Logiciel (CodLog, NomLog, PrixLog, #NumProj)
Realisation (#NumProj, #NumDev)
Ecrire en SQL les requêtes suivantes :
1. Créer les tables « Projet » et « Logiciel ». préciser
clairement les types des données.
2. Afficher les noms et les prix des logiciels appartenant
au projet ayant comme titre « gestion de stock », triés
dans l’ordre décroissant des prix.
3. Afficher le total des prix des logiciels du projet numéro
10. Lors de l’affichage, le titre de la colonne sera « coût
total du projet ».
4. Afficher le nombre de développeurs qui ont participé
au projet intitulé « gestion de stock ».
5. Afficher les projets qui ont plus que 5 logiciels.
6. Les numéros et noms des développeurs qui ont
participés dans tous les projets.
7. Reporter la date de fin des projets dont le titre
contient « Web » à la date « 17/04/2022 »
Corrigé de l’exercice 6
1) CREATE TABLE Projet (
NumProj INT UNSIGNED PRIMARY KEY
AUTO_INCREMENT,
TitreProj VARCHAR(40),
DateDeb Date,
DateFin Date,
ENGINE=InnoDB;
CREATE TABLE Logiciel (
CodeLog INT UNSIGNED PRIMARY KEY
AUTO_INCREMENT,
NomLog VARCHAR(40),
PrixLog DECIMAL,
NumProj INT UNSIGNED,
CONSTRAINT fk_projet_numero
FOREIGN KEY (NumProj)
REFERENCES Projet(NumProj) )
ENGINE=InnoDB;
2) SELECT [Link], [Link]
FROM Logiciel L
INNER JOIN Projet P ON [Link]=[Link]
WHERE [Link]="gestion␣de␣stock"
ORDER BY [Link] DESC
3) SELECT SUM(PrixLog) as "cout␣total␣du␣projet"
FROM Logiciel
WHERE NumPRoj=10
4) SELECT count(*)
FROM Developpeur D
INNER JOIN Realisation R ON [Link]=[Link]
INNER JOIN Projet P ON [Link]=[Link]
WHERE [Link]="gestion␣de␣stock"
5) SELECT NumProj, TitreProj
FROM Projet P
INNER JOIN Logiciel L ON [Link]=[Link]
GROUP BY NumProj, TitreProj
HAVING count(*)>5
6) SELECT NumDev, NomDev
FROM Developpeur D
INNER JOIN Realisation R ON [Link]=[Link]
GROUP BY NumDev, NomDev
HAVING count(*)=(SELECT COUNT(*) FROM Projet)
7) UPDATE Projet
SET DateFin = '17/04/2022'
WHERE TitreProj like ‘%Web%’ ;
Exercice 7
On considère la base de données suivante:
Client (IdCli ,nom,ville)
Produit ( IdPro, Nom, marque, Prix,Qstock )
Vente (#IdCL,#IdPro, date ,qte)
Exprimer en SQL les requêtes suivantes :
1. Donner les différentes marques de produit.
2. Lister les produits de marque IBM, Apple ou Asus.
3. Donner les noms des clients qui ont acheté le produit
'p1'.
4. Donner les noms des produits qui n'ont pas été acheté.
5. Donner les noms des clients ayant acheté un produit
en quantité supérieure à chacune des quantités de
produits
achetées par le client 'c1'.
6. Donner les noms des produits moins chers que la
moyenne des prix de tous les produits.
7. Supprimer les ventes des clients de Fès antérieures au
01-mar-2022.
Corrigé de l’exercice 7
1) select Distinct [Link] from produit p
2)select * from produit p where [Link] in ('IBM',
'APPEL', 'ASUS')
3)select [Link] from client c where IdCli in( select [Link]
from vente v where [Link]='p1')
4)select [Link] from produit p where not exists (select *
from vente v where [Link]= [Link])
5) select [Link] from vente v where [Link]>=all(select
[Link] from vente w where [Link] = 'c1')
6) select [Link] from produit p1 where [Link] < (select
AVG ([Link]) from produit p2 )
7) delete from vente where date < '01-mars-2022' and
IdCli in ( select [Link] from client c where [Link]= 'fes')