Série
Requêtes SQL
On souhaite décrire de manière très simplifiée le mode de fonctionnement d'un ensemble de sociétés
pharmaceutiques.
• Une société pharmaceutique est identifiée par son code Siret, a un nom, un siège social (lieu) et un PDG.
• Une société a des employés. Un employé est identifié par un matricule, porte un nom, a une date de
naissance, a été embauché a une certaine date, perçoit un salaire et est dirigé par un supérieur.
• Un médicament est identifié par un numéro au sein de la société qui le développe, porte un nom, a un type
de présentation et contient un principe actif.
• Un principe actif est une molécule identifiée par son nom, a une formule. Une pathologie est identifiée par
un libellé et possède une description. Un principe actif agit potentiellement sur plusieurs pathologies.
Le modèle relationnel suivant correspond aux spécifications ci-dessus décrites :
Employé (matEmp, nomEmp, dateNaissEmp, dateEmb, salaireEmp, #matEmpSup, #codeSiret)
Société (codeSiret, nomSoc, siegeSoc, #matEmpPDG)
Medicament (numMed, nomMed, typePres, #nomMol, #codeSiret)
Pathologie (libellePat, descPat)
Molécule (nomMol, formuleMol)
Agir (#libellePat, #nomMol)
Répondre en SQL aux requêtes suivantes :
1. Donner les noms (en majuscule) et matricules des employés de la société de code Siret 5.
Select upper (nomEmp), matEmp from employe where codeSiret= 5;
2. Donner les noms (première lettre en majuscule) et matricules des employés de la société Bayer Pharma.
Select initcap(nomEmp), matEmp
from employe E, société S
where E.codeSiret = S.codeSiret and lower(nomSoc)= 'bayer pharma' ;
ou and upper(nomSoc) = 'BAYER PHARMA' ;
------------------------------------------------------------------------------
Select initcap(nomEmp), matEmp
from employe
where codeSiret = (select codeSiret from société where nomSoc= 'Bayer Pharma' );
1
3. Donner les noms ( 3 premières lettres) et matricules des employés des sociétés Bayer Pharma, Pierre
Fabre, Biogalénique et Sanofi Synthelabo.
Select substr(nomEmp,1,3), matEmp
from employé E, société S
where E.codeSiret = S.codeSiret
and lower(nomSoc) in ('bayer pharma', 'pierre fabre', 'biogalénique', 'sanofi synthelabo') ;
4. Lister les employés qui ont été embauchés entre 2010 et 2012.
Select * from employé where dateEmb >= '01/01/2010' and dateEmb <= '31/12/2012' ;
Select * from employé where dateEmb between '01/01/2010' and '31/12/2012' ;
Select * from employé where to_number(to_char(dateEmb,'YYYY')) between 2010 and 2012 ;
Select * from employe where extract (year from dateEmb) between 2010 and 2012 ;
5. Donner la liste des pathologies triée par ordre alphabétique.
Select * from pathologie order by libellePat ;
6. Donner les sociétés dont le siège social se trouve à Paris.
Select * from Société where siegeSoc like '%Paris%' ;
7. Donner le nombre de médicaments décrits dans la base de données.
Select count (*) "Nombre Medicaments" from medicament ;
8. Donner pour chaque société le nombre de molécules actives mises au point.
Select codeSiret, count(distinct(nomMol)) " Nombre Molécules Actives"
From medicament
Group by codeSiret ;
9. Donner les sociétés qui ont mis au point au moins deux molécules actives.
Select codeSiret
From medicament
Group by codeSiret
Having count(distinct(nomMol)) >= 2 ;
10. Quelle est la société qui a le plus de molécules actives ?
Select codeSiret
From medicament
Group by codeSiret
Having count(distinct(nomMol)) = (Select max(count(distinct(nomMol)))
from medicament
group by codeSiret) ;
2
11. Quelles sont les molécules actives qui ont un rôle thérapeutique dans l'hypertension artérielle ?
Select nomMol
From Agir
Where libellePat = 'hypertension artérielle' ;
12. Combien de molécules actives ont un rôle dans l'hypertension artérielle ?
Select count(nomMol) from Agir where libellePat ='hypertension artérielle';
13. Quels sont les médicaments qui sont impliqués dans le traitement de l'hypertension artérielle ?
Select numMed from Medicament where nomMol in (Select nomMol
From Agir
Where libellePat ='hypertension artérielle') ;
14. Quelles sont les molécules qui ont un rôle thérapeutique dans au moins deux pathologies ?
Select nomMol From Agir group by nomMol having count(libellePat)>= 2;
ou Select nomMol From Agir group by nomMol having count(*) >= 2;
15. Quelles sont les molécules qui agissent à la fois sur la grippe et sur le syndrome de Kawasaki ?
Select nomMol From Agir where libellePat='Grippe'
Intersect Select nomMol From Agir where libellePat='Syndrome de Kawasaki';
16. Quelles sont les pathologies qui peuvent être soulagées par plusieurs molécules actives ?
Select libellePat From Agir group by libellePat having count (nomMol)>1 ;
17. Quelles sont les noms de sociétés qui ont mis au point des molécules actives à l'origine de médicaments
appartenant la famille des analgésiques ?
Select nomSoc
from Societe, medicament
where Societe.codeSiret = medicament.codeSiret and nomMol='analgésique';
18. Quelles sont les molécules qui n'agissent que sur la grippe ?
Select nomMol from Agir where libellePat='grippe'
Minus Select nomMol from Agir where libellePat != 'grippe';
19. Quelles sont les molécules qui agissent sur la grippe mais qui n'agissent pas sur l'hypertension artérielle ?
Select nomMol from agir where libellePat='grippe'
Minus Select nomMol from agir where libellePat='hypertension artérielle';
20. Quels sont les molécules actives qui agissent dans toutes les pathologies ?
3
select nomMol
from agir a1
where not exists (select libellePat
from pathologie
where libellePat not in (select a2.libellePat
from agir a2
where a1.nomMol=a2.nomMol)) ;
21. Donner la somme, la moyenne, le minimum et le maximum des salaires des PDG des sociétés.
Select sum(salaireEmp) "Som Sal PDG", avg(salaireEmp) "Moy Sal PDG",
min(salaireEmp) "Min Sal PDG", max(salaireEmp) "Max Sal PDG"
From Employe
Where matEmpSup = null ;
Exemples de données
Medicament Agir
