REPRESENTANTS (NumRep, NomRep, Ville Rep)
PRODUITS (NumProd, NomProd, CoulProd, PoidsProd)
CLIENTS (NumCli, NomCli, VilleCli)
VENTES (NumRep, NumProd, NumCli, QT)
1) Afficher tous les détails de tous les clients.
SELECT *
FROM CLIENTS
2) Afficher les numéros et les noms des produits de couleur rouge et de poids supérieur à 2000.
SELECT NumProd, NomProd
FROM PRODUITS
WHERE CoulProd='Rouge'
AND PoidsProd>2000;
3) Afficher les représentants ayant vendu au moins un produit.
SELECT NomRep
FROM VENTES
4) Afficher les noms des clients de BOUAKE ayant acheté un produit pour une quantité
supérieure à 180.
SELECT DISTINCT NomCli
FROM CLIENTS, VENTES
WHERE VENTES. NumCli = CLIENTS. NumCli
AND QT>180
AND VilleCli='Lyon';
5) Affiche les noms des représentants et des clients à qui ces représentants ont vendu un
produit de couleur rouge pour une quantité supérieure à 100.
SELECT DISTINCT NomRep, NomCli
FROM REPRESENTANT, CLIENTS, PRODUITS, VENTES
WHERE VENTES .NumCli =[Link]
AND [Link]= REPRESENTANTS .NumR ep
AND [Link]=[Link]
AND QT>100
AND [Link]='Rouge';
TD : Requêtes SQL (BDR.TD2-1) INSA 3IF 2008-2009 Loïc Maisonnasse 1 Objectif Ce TD a pour
objectif de vous familiariser avec le langage de requêtes SQL. Et de vous apprendre à écrire des
requêtes SQL pour rechercher des informations précises dans une base de données en utilisant
les différents opérateurs de SQL. 2
Exercice 1 2.1 Schéma Soit la base de données suivante permettant de gérer les consultations
entre des médecins identifiés par leurs numéros RPPS (répertoire partagé des professionnels de
santé) et des patients identifiés par leur numéro de sécurité social et associés à un médecin
traitant.
PATIENT (NumSS, NomP, PrenomP, Sexe, dateNaiss, #NumMed)
Le patient est identifié par son numSS numéro de sécurité social et son médecin traitant est
numRPPS
MEDECIN (NumMed, nomMed, prenomMed, spécialite, ville, adresse)
Le médecin est identifié par NumMed et il peut avoir une spécialité.
CONSULTE (#NumMed , #numSS, date, diagnostic, numOrd#)
Un patient consulte un médecin à une certaine date. Le médecin effectue un diagnostic et lui
prescrit une ordonnance dont l'identifiant est numOrd.
2.2 Requêtes
Écrire en SQL les requêtes qui permettent d’effectuer les actions suivantes :
1) Afficher la liste de tous les médecins
SELECT *
FROM MEDECIN
2) Afficher le nom et le prénom de tous les patients, triés par ordre inverse sur les noms;
SELECT nomP, prenomP
FROM PATIENT
ORDER BY nom DESC ;
3) Afficher le nom, le prénom et la spécialité de tous les médecins de Lyon, si le médecin n’a
pas de spécialité, afficher ‘généraliste’
SELECT nomM, prénomM, nvl(specialite, 'généraliste') AS Spé
FROM MEDECIN
WHERE ville='Lyon'
4) Afficher tous les patients nés après le 01/05/1985
SELECT nomP, prenomP
FROM PATIENT
WHERE dateNaiss>'1985-05-01'
5) Afficher le nom et le prénom des médecins dont la spécialité contient la chaîne "logue"
; SELECT nomM, prénomM
FROM MEDECIN WHERE specialite LIKE '%logue%'
6) Afficher les prénoms des patients dont le nom est Dupont, Durant ou Martin ;
SELECT prenom
FROM PATIENT
WHERE nom IN ('Dupont', 'Durant', 'Martin')
7-Afficher le nombre de médecins à Lyon
SELECT COUNT(*)
FROM MEDECIN
WHERE ville='Lyon'
8) Afficher la liste des consultations qui n’ont pas donné lieu à une ordonnance
SELECT * FROM CONSULT
WHERE numOrd is null
9) Afficher tous les médecins qui ont la même spécialité et qui sont dans la même ville que le
médecin Paul DESMON
SELECT nomM, prenomM
FROM MEDECIN
WHERE (specialite, ville) = (SELECT specialite, ville
FROM MEDECIN
WHERE nomM=’DESMON’ AND prenomM=’Paul’
10) 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, MEDECIN
WHERE [Link]=[Link]
ORDER BY nomP desc, prenomP desc
11) Afficher pour chaque patient (nom, prénom) le nom et le prénom des médecins qu’il a
consultés
SELECT nomP, prénomP, nomM
FROM PATIENT, MEDECIN, CONSULT
WHERE [Link]=CONSULTE. NumP
AND CONSULTE. NumP =[Link]
12) Afficher le nombre de consultations de chaque médecin (NumMed) pour la journée du 14-
10-2008, étiqueter cette colonne ‘nbConsult’
SELECT NumMed, COUNT (*) as (‘nbConsult’)
FROM CONSULTE
WHERE date=’14-10-2008’ GROUP BY NumP
13) Numéro de sécurité sociale du patient qui a le plus de consultations
SELECT NumP
FROM CONSULTE
GROUP BY NumP HAVING COUNT (*) = (SELECT MAX (COUNT(*)) FROM CONSULT GROUP BY
NumP)
14) Afficher tous les patients (NumP, nom, prénom) qui ont consulté un autre médecin que leur
médecin traitant
SELECT NumP, nomP, prénomP
FROM PATIENT
WHERE EXISTS (SELECT * FROM CONSULTE WHERE NumP =[Link] AND
numRPPS!=[Link]); SELECT NumP, nomP, prénomP FROM CONSULTE, PATIENT WHERE
PATIENT. NumP <> CONSULT. NumP AND PATIENT. NumP =[Link]
15) Afficher le nom et le prénom du ou des médecins qui sont médecins traitants du plus grand
nombre de patients ;
SELECT DISTINCT nomM, prénomM
FROM MEDECIN
WHERE [Link] = (SELECT numRPPS FROM PATIENT GROUP BY numRPPS
HAVING COUNT(numSS) = (SELECT MAX(COUNT(numSS)) FROM PATIENT GROUP BY
numRPPS));
16) Afficher tous les patients (nom, prénom) qui ont consulté le médecin ayant leNumMed
‘12345’ entre le 18-06-2008 et le 17-07-2008 ;
SELECT nomP, prenomP
FROM PATIENT
WHERE NumP IN (SELECT numSS FROM CONSULTE WHERE numRPPS='12345' AND date
BETWEEN '18-06-2008' AND '17-07-2008') ;
17) Afficher tous les patients (nom, prénom) qui ont consulté le médecin ayant le numRPPS
‘12345’ le 18-06-2008 et le 17-07-2008 ;
SELECT nomP, prénomP FROM PATIENT, CONSULT AS c1, CONSULT AS c2 WHERE
[Link] = [Link] AND [Link] = [Link] AND [Link] =
[Link] AND [Link] = ‘12345’ AND [Link] = ‘18-06-2008’ AND [Link] = ‘17-07-
2008’ SELECT nomP, prénomP FROM PATIENT, CONSULT WHERE PATIENT numSS =
[Link] AND date = ‘18-06-2008’ INTERSECT SELECT nomP, prénomP FROM
PATIENT, CONSULT WHERE PATIENT numSS = [Link] AND date = ‘17-07-2008’
18) Liste des patients qui ont consulté au moins deux médecins de la même spécialité le même
jour ;
SELECT nomP, prénomP
FROM PATIENT, CONSULTE, MEDECIN
WHERE [Link] = [Link] AND [Link] =
[Link]
GROUP BY [Link], [Link], [Link]écialité
HAVING COUNT(DISTINCT numRPPS) > 2 ;
SELECT nomP, prénomP FROM PATIENT P
WHERE (SELECT COUNT(DISTINCT numRPPS) FROM CONSULTE, MEDECIN WHERE
[Link] = [Link] AND [Link] = [Link] GROUP BY
[Link], [Link], [Link]écialité) >2
Exercice 2 2.3 Schéma On souhaite maintenant ajouter les deux tables suivantes pour
pouvoir gérer les prescriptions des médecins. PRESCRIPTION (numOrd, idMed#, nbBoites)
Une ligne de l’ordonnance du médecin ‘numOrd’ prévoit de donner nbBoites du
médicament idMed MEDICAMENT (idMed, nomMed, prix, categorie) Le médicament est
identifié, il a un certain prix et correspond à une catégorie de médicament
2.4 Création des tables
19) Créer la table PRESCRIPTION ;
CREATE TABLE PRESCRIPTION (numOrd NUMBER, idMed NUMBER, nbBoites NUMBER,
PRIMARY KEY (numOrd, idMed), FOREIGN KEY (idMed) REFERENCES MEDICAMENT(idMed)
20) Créer la table MEDICAMENT ; CREATE TABLE MEDICAMENT (idMed NUMBER PRIMARY KEY,
nomMed CHAR(40), prix FLOAT)
21) Ajouter le médicament n° 5432, avec le nom ‘aspirine’ et le prix ’11,05’ et de catégorie
‘Antalgique’; INSERT INTO MEDICAMENT VALUES (5432, ‘aspirine’, ’11,05’, ‘Antalgique’)
2.5 Requêtes
22) Pour chaque médicament écrire une requête qui donne le résultat suivant : < nom du
médicament > coute < prix >euros toutes taxes et < prix / 1.055> hors taxes SELECT nom||' coute
'||prix||' euros toutes taxes et '|| prix/1.055 " hors taxes" FROM MEDICAMENT
23) Donner le prix et le nom des médicaments dont le prix est supérieur au prix moyen de
leur catégorie.
SELECT nomMed, prix
FROM MEDICAMENT
WHERE [Link] > (SELECT AVG(prix) FROM MEDICAMENT WHERE [Link]=categorie)
24) Donner le nom de la catégorie et le prix du médicament le moins cher de la catégorie ayant le
plus grand prix moyen.
SELECT categorie, MIN(prix)
FROM MEDICAMENT
GROUP BY categorie HAVING avg (prix) = (SELECT MAX(AVG(prix)) FROM MEDICAMENT
GROUP BY categorie)
25) Afficher pour tous les médecins (numRPPS) en activité le ’11-08-2008’ la liste des patients
(numSS) qu’ils ont traité ainsi que le montant total de leurs ordonnances (somme des médicaments
prescrits avec le prix multiplié par le nombre de boites) ;
SELECT numRPPS, numSS, SUM(prix*nbBoite)
FROM CONSULT c,PRESCRIPTION p,MEDICAMENT m WHERE [Link]=[Link] AND
[Link]=[Link] AND [Link]='11-08-2008' group by (numRPPS, numSS)
26) Afficher le prix de la plus petite ordonnance ;
SELECT SUM(prix*nbBoites)
FROM PRESCRIPTION,MEDICAMENT
WHERE PRESCRIPTION.id_med = MEDICAMENT.id_med
GROUP BY (numOrd)
HAVING SUM(prix*nbBoites)= (SELECT MIN(SUM(prix*nbBoites)) FROM
PRESCRIPTION,MEDICAMENT WHERE PRESCRIPTION.id_med = MEDICAMENT.id_med
GROUP BY (numOrd) )
2.6 Requêtes facultatives
27) Afficher la liste des médecins accompagnée du prix maximum de leur ordonnance ;
SELECT nomM,prenomM, max(px)
FROM MEDECIN, (SELECT numOrd, numRPPS, SUM(prix*nbBoites) AS px FROM
CONSULTE,PRESCRIPTION,MEDICAMENT
WHERE [Link]=[Link] PRESCRIPTION.id_med = MEDICAMENT.id_med
GROUP BY numOrd, numRPPS) WHERE [Link]=[Link] 28) Donner le nom et le
prénom des patients qui ont eu la plus grosse prescription ; SELECT nomP,prenomP FROM
PATIENT,CONSULT WHERE [Link]=[Link] AND [Link] IN (SELECT
numOrd FROM PRESCRIPTION,MEDICAMENT WHERE PRESCRIPTION.id_med = MEDICAMENT.id_med
GROUP BY (numOrd) HAVING SUM(prix*nbBoites)= (SELECT MAX(SUM(prix*nbBoites)) FROM
PRESCRIPTION,MEDICAMENT WHERE PRESCRIPTION.id_med = MEDICAMENT.id_med GROUP BY
(numOrd) ) ) 29) Donner le nom du médicament le plus prescrit par les ‘pneumologues’ ; SELECT
idMed FROM MEDECIN, CONSULT, PRESCRIPTION WHERE [Link] = ‘pneumologue’ AND
[Link]=[Link] AND [Link]=[Link] GROUP BY
idMed HAVING count(*)=(SELECT MAX(COUNT(*)) FROM MEDECIN, CONSULT, PRESCRIPTION WHERE
[Link] = ‘pneumologue’ AND [Link]=[Link] AND
[Link]=[Link] GROUP BY idMed)
