Discussion :

[D3V1L J4M]

Bonjour à tous.
Voici le script qui permet de faire ma BD:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
DROP TABLE incompatibilite ;
DROP TABLE allergie ;
DROP TABLE prescription ;
DROP TABLE patient ;
DROP TABLE medicament ;
DROP TABLE medecin ;
 
CREATE TABLE patient (
noPatient       numeric(3),
nom             varchar(10) NOT NULL
                check(upper(nom) = nom),
prenom          varchar(10) NOT NULL,
sexe            char NOT NULL
                check(sexe in ('M','F')),
age             numeric(3) NOT NULL
                check(age between 0 and 150),
CONSTRAINT patientPK PRIMARY KEY (noPatient)
);
 
CREATE TABLE medicament (
noMedicament    numeric(3),
description     varchar(10) NOT NULL,
noFabricant    numeric(3) NOT NULL,
noMedicamentFabricant numeric(3) NOT NULL,
prix            numeric(6,2) NOT NULL
                check(prix >= 0),
CONSTRAINT medicamentPK PRIMARY KEY (noMedicament),
CONSTRAINT medicamentUKfm UNIQUE (noFabricant,noMedicamentFabricant)
);
 
 
CREATE TABLE medecin (
noMedecin       numeric(3) NOT NULL,
nom             varchar(10) NOT NULL,
prenom          varchar(10) NOT NULL,
telephone       numeric(10) NOT NULL,
CONSTRAINT medecinPK PRIMARY KEY (noMedecin)
);
 
CREATE TABLE prescription (
noMedicament    numeric(3) NOT NULL,
noPatient       numeric(3) NOT NULL,
dateDebutPrescrit char(10) NOT NULL,
dateFinPrescrit   char(10) NOT NULL,
noMedecin       numeric(3) NOT NULL,
CONSTRAINT prescriptionPK PRIMARY KEY (noMedicament,noPatient,dateDebutPrescrit),
CONSTRAINT presciptionFKm FOREIGN KEY (noMedicament) REFERENCES medicament,
CONSTRAINT presciptionFKp FOREIGN KEY (noPatient) REFERENCES patient,
CONSTRAINT prescriptionFKmed FOREIGN KEY (noMedecin) REFERENCES medecin
);
 
 
CREATE TABLE allergie (
noPatient       numeric(3) NOT NULL,
noMedicament    numeric(3) NOT NULL,
CONSTRAINT allergiePK PRIMARY KEY (noMedicament,noPatient),
CONSTRAINT allergieFKm FOREIGN KEY (noMedicament) REFERENCES medicament,
CONSTRAINT allergieFKp FOREIGN KEY (noPatient) REFERENCES patient
);
 
 
CREATE TABLE incompatibilite (
noMedicament1   numeric(3) NOT NULL,
noMedicament2   numeric(3) NOT NULL,
CONSTRAINT incompPK PRIMARY KEY (noMedicament1,noMedicament2),
CONSTRAINT incompFKm1 FOREIGN KEY (noMedicament1) REFERENCES medicament,
CONSTRAINT incompFKm2 FOREIGN KEY (noMedicament2) REFERENCES medicament
);
 
INSERT INTO patient (noPatient,nom,prenom,sexe,age)
             VALUES (1,'P1','N1','M',31);
INSERT INTO patient (noPatient,nom,prenom,sexe,age)
             VALUES (2,'P2','N2','F',19);
INSERT INTO patient (noPatient,nom,prenom,sexe,age)
             VALUES (3,'PAT3','Nom3','M',20);
INSERT INTO patient (noPatient,nom,prenom,sexe,age)
             VALUES (4,'PAT4','Nom4','F',30);
INSERT INTO patient (noPatient,nom,prenom,sexe,age)
             VALUES (5,'P5','N5','F',31);
 
INSERT INTO medicament (noMedicament,description,noFabricant,noMedicamentFabricant,prix)
               VALUES  (1,'Medic 1',1,1,109.99);
INSERT INTO medicament (noMedicament,description,noFabricant,noMedicamentFabricant,prix)
               VALUES  (2,'Medic 2',2,1,210.25);
INSERT INTO medicament (noMedicament,description,noFabricant,noMedicamentFabricant,prix)
               VALUES  (3,'dnitro XyZ',10,10,109.99);
INSERT INTO medicament (noMedicament,description,noFabricant,noMedicamentFabricant,prix)
               VALUES  (4,'enItRo aBc',20,10,210.25);
 
INSERT INTO medecin (noMedecin,nom,prenom,telephone)
             VALUES (1,'P1-med','N1-med',8198212096);
INSERT INTO medecin (noMedecin,nom,prenom,telephone)
             VALUES (2,'P2-med','N2-med',8198211234);
INSERT INTO medecin (noMedecin,nom,prenom,telephone)
             VALUES (3,'P3-med','N3-med',8198212096);
INSERT INTO medecin (noMedecin,nom,prenom,telephone)
             VALUES (4,'P4-med','N4-med',8198211234);
 
INSERT INTO allergie (noPatient,noMedicament)
               VALUES (1,2);
INSERT INTO allergie (noPatient,noMedicament)
               VALUES (2,2);
INSERT INTO allergie (noPatient,noMedicament)
               VALUES (4,3);
INSERT INTO allergie (noPatient,noMedicament)
               VALUES (4,4);
INSERT INTO allergie (noPatient,noMedicament)
               VALUES (5,2);
 
INSERT INTO incompatibilite (noMedicament1,noMedicament2)
               VALUES (1,2);
INSERT INTO incompatibilite (noMedicament1,noMedicament2)
               VALUES (3,4);
 
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (1,1,'2001-01-24','2001-02-14',1);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (2,1,'2001-01-10','2001-01-14',2);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (3,3,'2001-01-24','2001-02-14',1);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (3,4,'2001-01-24','2001-01-14',2);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (1,2,'2000-02-24','2000-02-14',2);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (2,2,'2000-02-24','2000-02-14',2);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (3,1,'2000-01-10','2000-01-14',2);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (4,1,'2000-01-10','2000-01-14',2);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (5,1,'2000-01-10','2000-01-14',2);
INSERT INTO prescription (noPatient,noMedicament,dateDebutPrescrit,dateFinPrescrit,nomedecin)
               VALUES    (5,4,'2000-01-10','2000-01-14',2);

Avec cela, j'ai une longue liste de requêtes à construire, mais j'ai encore de la misère avec 3 d'entres elles.

1ère dont on ne trouve pas l'erreur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
--6.	Écrivez un énoncé SELECT qui retourne la liste des allergies pour les patients de sexe féminin 
--avec l'âge moyen par allergie. Triez le résultat par noMedicament.  
--
--Retournez les colonnes suivantes: 
--1.	noMedicament 
--2.	description 
--3.	age_moyen (titre de colonne = "Age_Moyen") 
 
SELECT dbo.medicament.noMedicament, dbo.medicament.description, AVG(dbo.patient.age) AS Age_Moyen
FROM (dbo.allergie INNER JOIN dbo.patient ON dbo.allergie.noPatient = dbo.patient.noPatient)
	INNER JOIN dbo.medicament ON dbo.medicament.noMedicament = dbo.allergie.noMedicament
WHERE dbo.patient.sexe = 'F'
GROUP BY dbo.medicament.noMedicament
ORDER BY dbo.medicament.noMedicament

2e, très dur pour un débutant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
--9.	Écrivez un énoncé SELECT qui retourne la liste des patients à qui on a prescrit deux médicaments incompatibles. 
--Retournez les colonnes suivantes: 
--1.	noPatient 
--2.	noMedicament1 
--3.	noMedicament2 
 
--SELECT dbo.prescription.noPatient, dbo.prescription.noMedicament
--FROM (dbo.incompatibilite RIGHT JOIN dbo.prescription ON dbo.prescription.noMedicament = dbo.incompatibilite.noMedicament1
--AND dbo.incompatibilite.noMedicament2 = dbo.prescription.noMedicament)
 
SElECT dbo.prescription.noMedicament
FROM dbo.prescription INNER JOIN dbo.incompatibilite ON dbo.incompatibilite.noMedicament1 = dbo.prescription.noMedicament

On ne sait pas comment la monter celle-là.

Puis, la 3e:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
--10.	Écrivez un énoncé SELECT qui retourne la liste des patients traités par medecin.  
--Un medecin traite un patient s'il lui a prescrit un médicament. Triez le résultat par noMedecin.  
--Si un medecin n'a rien prescrit, il faut quand même afficher l'information du medecin. La liste ne doit pas contenir de doublon. 
--
--Retournez les colonnes suivantes: 
--1.	noMedecin 
--2.	nom 
--3.	prenom 
--4.	nopatient 
--5.	nom 
--6.	prenom 
 
SELECT dbo.medecin.noMedecin, dbo.medecin.nom, dbo.medecin.prenom, dbo.patient.noPatient, dbo.patient.nom, dbo.patient.prenom
FROM (dbo.medecin INNER JOIN (dbo.prescription) ON dbo.prescription.noMedecin = dbo.medecin.noMedecin) INNER JOIN dbo.patient
GROUP BY dbo.medecin.noMedecin

J'ai fait des recherches pour trouver de l'aide, mais c'est trop spécifique. Alors si quelqu'un pourrait m'expliquer comment développer ces requêtes, ce serait très apprécié.

Merci beaucoup.

[D3V1L J4M]

Oublier la première, c'est faite

[griese]

bonjour,
J'ai une question pour la 2eme requete, quel est l'intérêt de la jointure puisque les infos que vous voulez affichez sont présent dans la table prescription ?

Pour la troisieme, c'est surement un probleme de jointure, les parenthèse, je suis pas sur que ca marche. Moi quand j'ai des soucis avec le INNER JOIN, je passe mes jointures dans les clauses WHERE. Je sais que c'est pas propre mais ca a le mérite de marcher :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT M.noMedecin, M.nom, M.prenom, A.noPatient, A.nom, A.prenom
FROM dbo.medecin M, dbo.prescription P, dbo.patient A
WHERE M.noMedecin=
GROUP BY dbo.medecin.noMedecin=P.noMedecin
AND (Il manque une égalité pour la jointure avec la table patient)
GROUP BY  M.noMedecin, M.nom, M.prenom, A.noPatient, A.nom, A.prenom

Il me semble qu'il y a une règle SQL qui veut que tous ce qui est dans le SELECT est dans le GROUP BY

[D3V1L J4M]

Bah, le INNER JOIN ne se fait pas lorsqu'on veut joindre deux tables sur un champs qui se veut le même ?

Car dans dbo.prescription, il y a le noMedicament (qui provient de la table dbo.medicament), le noPatient (qui provient de la table dbo.medicament) et le noMedecin qui provient de la table dbo.medecin).

Donc, si on veut joindre ces tables, il faut nécessairement faire des liens (INNER JOIN)

Peut-être ai-je mal compris le principe aussi et que je suis carréement dans le champ

[griese]

Si bien sur mais vu qu'aucunes informations ne sont récupérer dans les autres tables et que il n'y a pas de filtre sur des infos des autres bases, je ne vois pas l'intérêt de la jointure. Sans la jointure le résultat devrait etre le meme.

[D3V1L J4M]

Mais selon ce que je croyais, la jointure créerait une certaine sélection. Quel sera donc ma condition de where si j'élimine ma jointure ?!

[griese]

En fait une jointure est la pour joindre les informations de deux tables en prenant pour références deux champs identiques dans chaque table. Elles par exemple pour récupérer des infos d'une tables par rapport à une autre. Exemple, tu as une table Personne :
PERSONNE
Nom
Prénom
Adresse
Num_Département

Puis une table département:
Num_département
Département

Puis tu veux faire une requete qui, au lieu d'afficher le numéro du département, t'affiche le nom du département.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT P.Nom, P.Prénom, P.Adresse, D.Département
FROM P.Personne INNER JOIN D.Département ON P.Num_département=D.département;

Au lieu d'avoir ca :
TOTO | Maurice | blabla | 35
TITI | Pierre | trucmachin |44
TRUC | Albert |bidule |75
...

Tu auras :
TOTO | Maurice | blabla | Ile et Vilaine
TITI | Pierre | trucmachin |Loire Atlantique
TRUC | Albert |bidule |Paris
...
Dans ton cas, tu ne dois pas supprimer la jointure, Si tu regarde l'énoncé, tu dois affihcer les champs Nomedicament1 et Nomedicament2, donc tu as besoin de ta jointure pour ca.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT P.noPatient, I.noMedicament1, I.noMedicament2
FROM dbo.prescription P, dbo.incompatibilite I
WHERE P.noMedicament=I.noMedicament1
AND P.noMedicament=I.noMedicament2;

J'ai fais mes jointures dans la clause car je suis pas très à l'aise avec le INNER JOIN, je préfère te donner une solution dont je suis sûr qu'elle marche. Pour la requete je suis pas sur à 100% mais faut tester.

[D3V1L J4M]

Un gros merci, ça commence vraiment à s'éclaircir. Va falloir que je regarde plus attentivement, mais bon, c'est un début

Pour la requête, le AND fait qu'elle ne fonctionnera jamais, car dans la table incomptatibilité, il n'y a pas deux medicaments pareils qui sont incomptabibles.
En changeant le AND pour un OR, j'ai au moins la liste de tous les médicaments auquels il y a une correspondance au moins.

Je travaille là-dessus, y'en reste plus gros à faire

Merci encore.

[D3V1L J4M]

Bon, j'ai contacté un des mes collègues et puis, de son côté ça l'a fonctionné...

Mais bon, il a pris une solution assez drastique, et je dois dire que jamais je ne saurais arrivé à la trouver par moi-même.

La voici:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT dbo.prescription.noPatient, dbo.incompatibilite.noMedicament1, dbo.incompatibilite.noMedicament2
FROM dbo.incompatibilite
	INNER JOIN dbo.prescription ON dbo.incompatibilite.noMedicament1 = dbo.prescription.noMedicament
INTERSECT
SELECT dbo.prescription.noPatient, dbo.incompatibilite.noMedicament1, dbo.incompatibilite.noMedicament2
FROM dbo.incompatibilite
 
	INNER JOIN dbo.prescription ON dbo.incompatibilite.noMedicament2 = dbo.prescription.noMedicament
ORDER BY dbo.prescription.noPatient;

Sur ce, un gros merci à toi griese pour ton aide, ça m'a aidé à en apprendre un peu plus.

Merci!

[toinexplore]

J'ai l'impression d'avoir du mal à comprendre INNER JOIN, est-ce normal ?