Ecole Nationale
TD 4
Supérieure
d’Ingénieursde Tunis
Le Language SQL
Exercice N°1:
— Departement (NDep, NomD)
— Employe (Matricule,, Nom, Prenom, DateNaissance, Adresse, Fonction, Salaire, #Ndep,
#matricule_superieur)
— Projet (Nproj ,NomP, Lieu, #NDep)
— Travaille (#Matricule, #NProj,, Heures)
L’attribut matricule_superieur dans la relation Employecontient
contient le matricule du supérieur direct de
l’employé. Chaque employé appartient à un département et peut travailler sur zéroou plusieurs
plusieur projets.
Chaque projet est rattaché à un département qui peut être di
différent
érent de celui des employés travaillant sur ce
projet.
Exprimer en SQL les requêtes suivantes :
1) Afficher les projets (Numéro)) sur lesquelles travaillent Taha Ben Salah et Donia Makki.
SELECT T. Nproj
FROM Travaille T, Employe E
WHERE T. Matricule =E. Matricule
AND [Link]=’Ben Salah’ AND E. Prenom =’Taha ’
INTERSECT
SELECT T. Nproj
FROM Travaille T, Employe E
WHERE T. Matricule =E. Matricule
AND [Link]=’Makki’ AND E. Prenom =’Donia ’;
natural join
SELECT T. Nproj
FROM Travaille T natural join Employe E
where [Link]=’Ben Salah’ AND E. Prenom =’Taha ’
INTERSECT
SELECT T. Nproj
FROM Travaille T natural join Employe E
WHERE [Link]=’Makki’ AND E. Prenom =’Donia ’;
1
ou bien ( avec Join et inner join)
SELECT T. Nproj
FROM Travaille T join Employe E on T. Matricule =E. Matricule
where [Link]=’Ben Salah’ AND E. Prenom =’Taha ’
INTERSECT
SELECT T. Nproj
FROM Travaille T join Employe E on T. Matricule =E. Matricule
WHERE AND [Link]=’Makki’ AND E. Prenom =’Donia ’;
//
SELECT T. Nproj
FROM Travaille T inner join Employe E on T. Matricule =E. Matricule
where [Link]=’Ben Salah’ AND E. Prenom =’Taha ’
INTERSECT
SELECT T. Nproj
FROM Travaille T inner join Employe E on T. Matricule =E. Matricule
WHERE AND [Link]=’Makki’ AND E. Prenom =’Donia ’;
2) Afficher les employés (Nom et Prénom) dont le supérieur est Taha Ben Salah.
SELECT Nom ,Prenom
FROM Employe
WHERE Matricule_superieur = (SELECT Matricule
FROM Employe WHERE Nom =’Ben Salah ’
ND Prenom =’Taha ’);
3) Afficher les employés (Nom et Prénom) qui ne travaillent sur aucun projet.
SELECT Nom, Prenom
FROM Employe
WHERE Matricule NOT IN ( SELECT Matricule FROM Travaille );
4) Afficher les Numéros des projets qui ont au moins un participant de chaque département.
SELECT T. Nproj
FROM Travaille T, Projet P, Employe E
WHERE T. Nproj =P. Nproj AND T. Matricule =E. Matricule
GROUP BY T. Nproj
HAVING count ( DISTINCT E. Ndep )=( SELECT count (*)
FROM Departement );
ou bien
SELECT T. Nproj
FROM Travaille T Join Projet P on T. Nproj =P. Nproj
Join Employe E on T. Matricule =E. Matricule
GROUP BY T. Nproj
HAVING count ( DISTINCT E. Ndep )=( SELECT count (*)
FROM Departement );
2
ou bien
SELECT T. Nproj
FROM Travaille T natural Join Projet P natural Join Employe E
GROUP BY T. Nproj
HAVING count ( DISTINCT E. Ndep )=( SELECT count (*)
FROM Departement );
5) Afficher les Noms des employés qui ne travaillent pas sur un projet à Tunis.
SELECT Nom FROM Employe WHERE
Matricule NOT IN( SELECT T. Matricule
FROM Travaille T, Projet P
WHERE T. Nproj =[Link] AND P. Lieu =’Tunis ’);
ou bien
SELECT Nom FROM Employe WHERE
Matricule NOT IN( SELECT T. Matricule
FROM Travaille T natural join Projet P
WHERE P. Lieu =’Tunis ’);
Exercice N2:
Patient (numSS, nomP, prenomP, dateNaiss, #numRPPS)
Medecin (numRPPS, nomM, prenomM, specialite, ville, adresse)
Consulte (#numRPPS, #numSS, date, diagnostic, PrixC)
Exprimer en SQL les requêtes suivantes :
1- Créer la table Consulte.
A : Create table Consulte (numRPPSnumber (8),
numSS number (8),
Date date,
Diagnostic varchar2(50),
PrixC number (10,3) ,
Primary key (numRPPS, numSS, date),
Foreign key (numRPPS) references Medecin,
Foreign key (numSS) references Patient);
ou bien B:
Create table Consulte (numRPPS number (8) references Medecin,
numSS number (8) references Patient,
Date date,
Diagnostic varchar2(50),
PrixC number (10,3) ,
Primary key (numRPPS, numSS, date) );
3
ou bien C:
Create table Consulte (numRPPSnumber (8),
numSS number (8),
Date date,
Diagnostic varchar2(50),
PrixC number (10,3) );
Alter table consulte
Add (constraint PK Primary key (numRPPS, numSS, date) ,
constraint FK1 Foreign key (numRPPS) references Medecin,
constraint FK2 Foreign key (numSS) references Patient);
2- Insérer des tuples de votre choix à chacune des tables de la base de données.
Insert intoMedecin values (2, ‘Bejaoui’, ‘Saleh’, ‘cadiologue’, ‘Tunis ‘, ‘rue xx Bizerte 2000’) ;
Insert into Patient values (1, ‘Ali’, ‘Mohamed’, ‘01/02/1973’, 2) ;
Insert into Consulte values(2, 1, ‘01/05/2016’, ‘Branchite’, 40) ;
3- Afficher le nom et le prénom des médecins dont la spécialité contient la chaîne "logue".
SELECT nomM, prenomM
FROM Medecin
WHERE specialite LIKE ‘%logue%’ ;
4- Afficher les informations des médecins qui ont la même spécialité et la même ville que le médecin
Ali Ben Salah.
SELECT nomM,prenomM
FROM Medecin
WHERE (specialite,ville) = (SELECT specialite, ville
FROM Medecin
WHERE nomM=’Ben Salah’ AND prenomM=‘Ali’) ;
5- Afficher la liste des patients (nom, prénom) avec le nom de leur médecin traitant, par
ordre alphabétique inverse sur les noms puis sur les prénoms ;
SELECT nomP, prenomP, nomM
FROM Patient NATURAL JOIN Medecin
ORDER BY nomP DESC, prenomP DESC ;
6- Afficher le nombre de consultations de chaque médecin (numRPPS) pour la journée du 14-10-
2008, étiqueter cette colonne ‘nbConsult’.
SELECT numRPPS, COUNT(*) AS nbConsult
FROM Consulte
WHERE date = ‘14/10/2008’
GROUP BY numRPPS ;
4
7- Afficher le Numéro de sécurité sociale du patient qui a le plus de consultations.
SELECT numSS
FROM CONSULTE
GROUP BY numSS
HAVING COUNT(*)= (SELECT MAX(COUNT(*)
FROM CONSULTE
GROUP BY numSS) ;
8- Afficher tous les patients (numSS, nom, prénom) qui ont consulté un autre médecin que leur
médecin traitant.
SELECT numSS, nomP, prénomP
FROM PATIENTpt
WHERE EXISTS (SELECT *
FROM CONSULTE
WHERE numSS=[Link]
AND numRPPS!=[Link]);
9- Supprimer les consultations ayant un prix < 25 et une date entre 01/01/2000 et 30/12/2002.
Delete from consulte
Where prixC<25 and date between ‘01/01/2000’ and ‘30/12/2002’;
