Modèle conceptuel des données Modèle relationnel SQL

ANGD Mathrice : Sites web dynamiques TD1 - Corrigé
CIRM Marseille
SYSTÈME D'INFORMATION HOSPITALIER
Modèle conceptuel des données
Cf annexe avec le MCD
Modèle relationnel
Cf annexe avec le MPD
SQL
La base décrite en SQL pour tester les requêtes avec un SGBD est disponible à cette
adresse : http://www.lpthe.jussieu.fr/~bremont/cirm/TD1.tgz
Traduire en français les requêtes SQL suivantes:
1. Création de la table “Service” avec IdService en clé primaire.
2. Affiche la date de naissance des médecins dont le nom est Pasteur et le prénom Louis
3. Affiche la liste (avec tous les attributs) de tous les hôpitaux de Lorient
4. Affiche les noms de tous les hôpitaux ayant un service de pneumologie.
Écrivez les phrases suivantes en requête SQL
1. Décrivez la table «Hopital» en SQL
CREATE TABLE Hopital (
IdHopital tinyint(10) NOT NULL auto_increment,
Nom varchar(50) NOT NULL,
Adresse varchar(50) NOT NULL,
Ville varchar(50) NOT NULL,
Rattachea tinyint(10) NOT NULL,
PRIMARY KEY (IdHopital)
)
2. Écrivez la requête permettant d'avoir la totalité des codes CCAM,
SELECT Codification FROM CCAM;
3. Écrivez la requête permettant d'avoir la liste des patientes sur l'ensemble de la
région,
SELECT DISTINCT Patient.Nom, Patient.Prenom
FROM Patient, Sejourne
WHERE Patient.IdPatient = Sejourne.IdPatient
AND Patient.Sexe = 'f'
AND Sejourne.DateSortie is Null;
4. Écrivez la requête permettant de lister les médecins nés avant le 1er janvier 1960,
SELECT Nom, Prenom FROM Medecin
WHERE DDN < '1960-01-01';
5. Écrivez la requête permettant de connaître les hôpitaux ayant un service d'ORL,
D.Brémont
6 au 10 novembre 2006
1/2
ANGD Mathrice : Sites web dynamiques TD1 - Corrigé
CIRM Marseille
SELECT Hopital.Nom
FROM Hopital, Service, Specialite
WHERE Hopital.IdHopital = Service.IdHopital
AND Service.IdSpecialite = Specialite.IdSpecialite
AND Specialite.Nom = 'ORL';
6. Écrivez la requête permettant de lister tous les actes médicaux réalisés sur le
patient « Kermadec » né le 05/01/1970.
SELECT Soigne.*
FROM Soigne, Patient
WHERE Soigne.IdPatient = Patient.IdPatient
AND Patient.Nom = 'Kermadec'
AND Patient.DDN = '1970-01-05';
7. Écrivez la requête permettant de savoir combien de patients sont dans un service
d'urgences,
SELECT COUNT(DISTINCT Sejourne.IdPatient)
FROM Sejourne, Service, Specialite
WHERE Sejourne.IdService = Service.IdService
AND Service.IdSpecialite = Specialite.IdSpecialite
AND Sejourne.DateSortie is Null
AND Specialite.Nom = 'Urgences';
Autre solution :
SELECT count(Sejourne.IdPatient) FROM Sejourne, Service,
Specialite
WHERE DateEntree <= curdate()
AND (DateSortie is null or DateSortie="0000-00-00" or
DateSortie >= curdate())
AND Sejourne.IdService=Service.IdService
AND Service.IdSpecialite=Specialite.IdSpecialite
AND Specialite.Nom="Urgences";
Attention "is null" ne marche que si le champ à été défini "NULL" à la
création de la table, sinon avec "NOT NULL" la date sera "0000-00-00"
8. Écrivez la requête permettant d'avoir la liste des patients présents dans le service
des urgences du CHU Pontchaillou.
SELECT Patient.Nom, Patient.Prenom
FROM Sejourne, Service, Specialite, Hopital, Patient
WHERE Sejourne.IdService = Service.IdService
AND Service.IdSpecialite = Specialite.IdSpecialite
AND Sejourne.DateSortie is Null
AND Specialite.Nom = 'Urgences'
AND Service.IdHopital = Hopital.IdHopital
AND Hopital.nom = 'Pontchaillou'
AND Patient.IdPatient = Sejourne.IdPatient;
D.Brémont
6 au 10 novembre 2006
2/2