Institut Supérieur d'Informatique et de Mathématiques de Monastir
CORRECTION SERIE DE TD N° 3
Langage de Requêtes SQL
Sections : LF2 et LA2 Informatique Matière : SGBD
Exercice 1 :
Soit le schéma relationnel suivant :
EMP (Matr, NomE, Poste, DateEmb, Sup#, Salaire, Comm, NumDept#)
DEPT (NumDept, NomDept, Lieu, directeur#)
PROJET (CodeP, NomP)
PARTICIPATION (Matr#, CodeP#, Fonction)
Répondez aux questions suivantes :
1. Créer les tables pour traduire le schéma relationnel donné, suivant le tableau 1.
Tableau 1. Type de chaque colonne
Colonne Matr Sup Salaire Comm directeur DateEmb
Type Number Date
Colonne Poste NomDept Lieu NomP Fonction NomE
Type Varchar(30)
Réponse :
--suppression des tables si elles existent déjà :
drop table PARTICIPATION cascade constraints;
drop table PROJET cascade constraints;
drop table EMP cascade constraints;
drop table DEPT cascade constraints;
--création des tables :
create table DEPT (
NumDept number primary key,
NomDept varchar(30),
Lieu varchar(30),
directeur number
);
create table EMP (
Matr number primary key,
Page 1/10
NomE varchar(30),
Poste varchar(30),
DateEmb date,
Sup number,
Salaire number,
Comm number,
NumDept number,
constraint Fk_sup_emp foreign key(sup) references emp (matr),
constraint Fk_NumDept_emp foreign key(NumDept) references DEPT (NumDept)
);
create table PROJET (
CodeP number primary key,
NomP varchar(30)
);
create table PARTICIPATION (
Matr number,
CodeP number,
Fonction varchar(30),
constraint pk_part primary key(Matr,CodeP),
constraint Fk_Matr_part foreign key(Matr) references emp (matr),
constraint Fk_CodeP_part foreign key(CodeP) references projet (CodeP)
);
alter table DEPT add constraint fk_directeur_dept foreign key(directeur) references emp
(matr);
2. Peupler toutes les tables par 5 tuplets chacune.
Réponse :
delete from PARTICIPATION cascade;
delete from PROJET cascade ;
delete from EMP cascade ;
delete from DEPT cascade ;
insert into DEPT(NumDept,NomDept,Lieu) values(10,'Dept1','Monastir');
insert into DEPT(NumDept,NomDept,Lieu) values(20,'Dept2','Monastir');
insert into DEPT(NumDept,NomDept,Lieu) values(30,'Dept3','Monastir');
insert into DEPT(NumDept,NomDept,Lieu) values(40,'Dept4','Monastir');
Page 2/10
insert into DEPT(NumDept,NomDept,Lieu) values(50,'Dept5','Monastir');
insert into EMP values (100,'Emp1', 'Poste1', '12/02/2016', NULL, 1500, NULL,10);
insert into EMP values (101,'Emp2', 'Poste2', '12/03/2016', 100, 1000, NULL,20);
insert into EMP values (102,'Emp3', 'Poste2', '12/02/2016', 100, 1100, NULL,30);
insert into EMP values (103,'Emp4', 'Poste4', '12/03/2016', NULL, 1400, NULL,40);
insert into EMP values (104,'Emp5', 'Poste5', '12/03/2016', 103, 1100, NULL,50);
update dept set directeur=100 where NumDept=10;
update dept set directeur=101 where NumDept=20;
update dept set directeur=102 where NumDept=30;
update dept set directeur=103 where NumDept=40;
update dept set directeur=104 where NumDept=50;
insert into PROJET values (1001,'Projet1');
insert into PROJET values (1002,'Projet2');
insert into PROJET values (1003,'Projet3');
insert into PROJET values (1004,'Projet4');
insert into PROJET values (1005,'Projet5');
insert into PARTICIPATION values(100,1001,'Fonction1');
insert into PARTICIPATION values(101,1002,'Fonction2');
insert into PARTICIPATION values(102,1003,'Fonction3');
insert into PARTICIPATION values(103,1004,'Fonction4');
insert into PARTICIPATION values(104,1004,'Fonction5');
3. Définir la ou les requête(s) permettant de modifier la contrainte de clé étrangère imposée
sur l’attribut « Sup » de la table « EMP » pour qu’il soit possible d’attribuer un NULL à
cet attribut pour tous les employés dépendant d’un supérieur hiérarchique supprimé.
Réponse :
-- modifier la clé étrangère pour permettre un NULL lors de la suppression d'un employé:
alter table emp drop constraint fk_sup_emp;
alter table emp add constraint fk_sup_emp foreign key(sup) references emp (matr) on
delete set NULL;
Page 3/10
alter table PARTICIPATION drop constraint Fk_Matr_part;
alter table PARTICIPATION add constraint Fk_Matr_part foreign key(Matr) references
emp (matr) on delete set NULL;
alter table DEPT drop constraint fk_directeur_dept;
alter table DEPT add constraint fk_directeur_dept foreign key(directeur) references emp
(matr) on delete set NULL;
4. Est-il possible d’appliquer cette modification pour toutes les tables ? pourquoi ? comment
peut-on démontrer ça ?
Réponse :
Cette modification n'est pas appplicable car la clé primaire (matr) de emp participe dans
la clé primaire de participation et on sait qu'on peut pas attribuer un NULL pour un attribut
appartenant à une clé primaire.
Pour tester cette modification :
SQL> delete from emp cascade where matr=100;
La réponse du moteur SQL est :
SQL> delete from emp cascade where matr=100 ;
delete from emp cascade where matr=100
*
ERREUR Ó la ligne 1 :
ORA-01407: impossible de mettre Ó jour ("SYSTEM"."PARTICIPATION"."MATR")
avec NULL
5. Définir la ou les requête(s) permettant de modifier la contrainte de clé étrangère imposée
sur l’attribut « Sup » de la table « EMP » pour qu’il soit possible de supprimer toutes les
occurrences qui référencent tout employé supprimé.
Réponse :
-- modifier la clé étrangère pour permettre la suppression des occurences qui reférencent
un employé qu'on veut le supprimer:
alter table emp drop constraint fk_sup_emp;
alter table emp add constraint fk_sup_emp foreign key(sup) references emp (matr) on
delete cascade;
alter table PARTICIPATION drop constraint Fk_Matr_part;
Page 4/10
alter table PARTICIPATION add constraint Fk_Matr_part foreign key(Matr) references
emp (matr) on delete cascade;
alter table DEPT drop constraint fk_directeur_dept;
alter table DEPT add constraint fk_directeur_dept foreign key(directeur) references emp
(matr) on delete cascade;
6. Est-il possible d’appliquer cette modification pour toutes les tables ? pourquoi ? comment
peut-on démontrer ça ?
Réponse :
Cette modification est appplicable car on poeut supprimer toute une occurence d'une clé
primaire ( table participation).
Pour tester cette modification :
delete from emp cascade where matr=100;
La réponse du moeteur SQL est :
SQL> delete from emp cascade where matr=100 ;
1 ligne supprimÚe.
7. Ajouter le champ « Adresse » à la table EMP de type chaîne de caractères (30). Il est
initialisé par défaut à ‘Monastir’.
Réponse :
Alter table EMP add Adresse varchar(30) default ‘Monastir’ ;
8. Ajouter un nouvel employé sans spécifier la valeur de la colonne « Adresse ».
Réponse :
Insert into EMP (Matr, NomE, Poste, DateEmb, Sup#, Salaire, Comm, NumDept) values
(3,’Emp1’,’Poste1’,’22/02/2016’,2,1000,NULL,10) ;
9. Augmenter la taille du champ « Adresse » par 10 caractères.
Réponse :
Alter table EMP modify Adresse varchar(40) ;
10. Vérifier cette modification de la table EMP.
Réponse :
DESCRIBE EMP
11. Supprimer le champ « Adresse » de la table EMP.
Page 5/10
Réponse :
Alter table EMP drop column Adresse ;
12. Le département ayant le plus petit numéro est déplacé vers ‘Sousse’.
Réponse :
Update DEPT set Lieu =’Sousse’ where numdept = (select min(Numdept) from DEPT);
13. Les commissions des employés ayant des salaires supérieurs à 1500 DT sont augmentées
par 10%.
Réponse :
Update EMP set COMM=COMM*1.1 where salaire >1500;
14. Les employés, ayant un salaire inférieur à 1000 DT, sont affectés au département ayant le
numéro le plus haut.
Réponse :
Update EMP set Numdept = (select max(NumDept) from DEPT) where salaire<1000;
Exercice 2 :
Pour le même schéma relationnel de l’exercice 1. Répondez aux requêtes suivantes :
1. Sélectionner les numéros et les noms de tous les départements triés par leurs noms.
Réponse :
Select numDept, nomDept from DEPT Order by Nomdept;
2. Sélectionner les matricules de tous les employés.
Réponse :
Select matr from emp ;
3. Sélectionner les matricules, les noms et les supérieurs hiérarchiques (SUP) de tous les
employés du département 20.
Réponse :
Select Matr, NomE, Sup from EMP where Numdept=20;
4. Sélectionner la liste des projets triés par leurs codes.
Réponse :
Select * from Projet Order by codeP;
Page 6/10
5. Sélectionner la liste des projets effectués par l’ensemble des employés groupés par les
matricules et les noms des employés.
Réponse :
Select CodeP, nomP, Matr, NomE from EMP, PROJET, PARTICIPATION
Where [Link]= [Link] and
[Link]=[Link]
Group by Matr, NomE, CodeP, nomP
6. Sélectionner les postes des employés dont le salaire est supérieur à 13000.
Réponse :
Select Poste from EMP where Salaire >13000;
7. Sélectionner la liste des divers postes, en n’affichant chaque poste qu’une seule fois
Réponse :
Select distinct Poste from EMP;
8. On considèrera qu’une commission NULL correspond à un employé qui ne touche aucune
commission (donc commission = 0). Quels sont les noms des employés dont le salaire est
inférieur à la commission.
Réponse :
Select NomE from EMP where Salaire<NVL(COMM,0);
9. Sélectionner les noms des employés qui ne touchent pas de commission
Réponse :
Select NomE from EMP where COMM is NULL;
10. Sélectionner les noms des employés dont la 2ème lettre est un E.
Réponse :
Select NomE from EMP where NomE like ‘_E%’;
11. Sélectionner les numéros de département dont au moins un employé touche une
commission
Réponse :
Select NumDept from EMP where COMM is not NULL;
Page 7/10
12. Sélectionner les noms des employés qui touchent un salaire entre 6.000 et 10.000.
Réponse :
Select NOME from EMP where Salaire between 6000 and 10000;
13. Sélectionner les noms des employés qui n’ont pas participé à aucun projet.
Réponse :
Select NomE from EMP where Matr not in (Select distinct Matr from PARTICIPATION);
Ou,
Select CodeP from Projet
Minus
Select distinct CodeP from PARTICIPATION;
14. Sélectionner les noms des projets qui n’ont pas été effectués.
Réponse :
Select NomP from Projet where CodeP not in (Select distinct CodeP from
PARTICIPATION);
15. Sélectionner les noms des départements où leurs employés ont participé à tous les projets.
Réponse :
Note : Comme il n’y pas une implémentation directe de l’opération de division sous
SQL, alors plusieurs implémentation sont possibles suivant le cas en question.
Pour notre cas, la clé primaire de la table participation est composée par
(Matr+codeP) alors on ne peut pas avoir deux tuplets avec les mêmes Matr et CodeP.
Parsuite, on peut implémenter la division en utilisant des fonctions d’agrégat
(COMPTE) :
Select NumDept,count(*) nb from emp group by NumDept
Intersect
Select numdept, count(*) nb from emp where matr in (select Matr from Participation
group by Matr having count(*) =(select count (*) from Projet)) group by numdept;
Page 8/10
Exercice 3 :
Pour le même schéma relationnel de l’exercice 1 :
EMP (Matr, NomE, Poste, DateEmb, Sup#, Salaire, Comm, NumDept#)
DEPT (NumDept, NomDept, Lieu, directeur#)
PROJET (CodeP, NomP)
PARTICIPATION (Matr#, CodeP#, Fonction)
Répondez aux requêtes suivantes :
1. Sélectionner le nombre des projets effectués par chaque employé.
Réponse :
Select matr, count(*) nbProjets from participation group by matr;
2. Sélectionner le nombre des projets effectués par chaque employé qui a effectué plus que
deux projets.
Réponse :
Select matr, count(*) nbProjets from participation group by matr having count(*)>2;
3. Sélectionner le nombre des projets effectués par chaque employé qui a un salaire entre
600 et 1000.
Réponse :
Select [Link], count([Link]) nbProjets from Participation P, Emp E where
[Link]=[Link] and [Link] between 600 and 1000 group by [Link];
4. Sélectionner le nombre des projets effectués par chaque employé qui appartient au
département ayant le plus grand nombre d’employés.
Réponse :
Select [Link], count([Link]) nbProjets from Participation P, Emp E where
[Link]=[Link] and [Link] in (Select NumDept from Emp group by NumDept having
count (*) = (select max(count(*)) from Emp group by Numdept) ) group by [Link];
5. Sélectionner le nombre des projets effectués par chaque chef hiérarchique.
Réponse :
Select [Link], count([Link]) nbProjets from Participation P, Emp E where
[Link]=[Link] and [Link] is NULL group by [Link];
Page 9/10
6. Sélectionner le nombre des projets effectués, s’il est inférieur à 2, par chaque chef
hiérarchique ayant une équipe composée par au moins 5 employés.
Réponse :
Select [Link], count([Link]) nbProjets from Participation P, Emp E where
[Link]=[Link] and [Link] in (select [Link] from Emp E1, Emp E2 where
[Link]=[Link] group by [Link] having count(*)>5) and [Link] is NULL group by
[Link] having count(*)<2;
7. Sélectionner le nombre d’employés qui ont participé à chacun des projets effectués.
Réponse :
Select CodeP, count(*) nbEmp from participation group by CodeP;
8. Sélectionner le nombre d’employés, s’il dépasse deux employés pour ceux qui ont
participé à chacun des projets effectués.
Réponse :
Select CodeP, count(*) nbEmp from participation group by CodeP having count(*);
9. Sélectionner le nombre d’employés qui ont participé à chacun des projets dont leurs noms
commencent par la lettre ‘A’.
Réponse :
Select CodeP, count(*) nbEmp from participation where NomP like ‘A%’ group by
CodeP;
10. Sélectionner le nombre d’employés, s’il est inférieur à 3, qui ont participé à chacun des
projets dont leurs noms inclurent les lettres ‘A’ et ‘B’.
Réponse :
Select CodeP, count(*) nbEmp from participation where NomP like ‘%A%B%’ OR
NomP like ‘%B%A%’ group by CodeP;
Page 10/10