Discussion :

[anadolu]

Bonjour

J ai un exercice et je suis bloqué au niveau de comment définir des conditions

Ma base de données est:
voitures(nov, type, chevaux) ou nov est le numero de la voiture (clef primaire), type le type de voiture et chevaux le nombre de chevaux
conducteurs(noc, nom, prenom) ou noc est le numero de conducteur (clef primaire) et ensuite son nom et prenom
achats(nov,nob, prix) nov et nob clefs primaires et clefs secondaires (donc nov liée à voitures et noc liée à conducteurs)

Ma base de données est petite:
Conducteurs:
1 Durant Jean
2 Dupont Jacques
3 Dugenou Thomas
4 Dujardin Ralph

Achats:
6 1 10000
4 1 20000
7 2 20000
8 4 15000
2 3 10000

Voitures:
1 jazz 80
2 clio 90
3 polo 100
4 golf 150
5 megane 105
6 clio 120
7 alpine 300
8 ferrari 500

La question est
Quels sont les conducteurs qui n'ont acheté que des voitures de type clio ?

Donc en fait des acheteurs qui ont acheté des clio il y en a 2: Durant Jean et Dugenou Thomas.
Ca je sais le faire

Code :

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

1
2
3
4
5
select conducteurs.nom 
from achats 
join conducteurs on conducteurs.noc=achats.noc 
join voitures on voitures.nov=achats.nov 
where voitures.nov = any (select nov from voitures where upper(type)='CLIO');

Je sais également comment faire pour récupérer ceux qui auraient acheté plus de 1 voiture:

Code :

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

1
2
3
4
5
select conducteurs.nom 
from achats 
join conducteurs on conducteurs.noc=achats.noc 
join voiture on voiture.nov=achats.nov 
group by conducteurs.nom having count(achats.noc)>=2;

Par rapport à la question je sais que je dois dire que je veux seulement ceux ayant acheté une CLIO si achat égal à 1 et que si l'achat est supérieur à 1, il faut vérifier que ce ne soit que des nov = 2 ou 6?

Merci pour toute aide

[tatayo]

Bonjour,
Il faut prendre le problème dans l'autre sens: ceux qui n'ont acheté que des Clios, sont ceux qui d'une part ont acheté au moins une voiture, et qui d'autre part n'ont pas acheté autre chose.

Je te laisse trouver la requête qui correspond...

Tatayo.

[anadolu]

Merci beaucoup

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select conducteurs.nom, conducteurs.prenom
from conducteurs
join achats on conducteurs.noc=achats.noc
join voitures on achats.nov=voitures.nov
where conducteurs.noc not in (
select conducteurs.noc
 from conducteurs
 join achats
on conducteurs.noc=achats.noc
 join voitures
on achats.nov=voitures.nov
where upper(voitures.type)<>'CLIO');

Vous confirmez?

[aieeeuuuuu]

ça devrait fonctionner.

La jointure sur la table voitures dans la requete principale semble toutefois inutile.

Par ailleurs, il y a plus simple :

Code :

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

1
2
3
4
5
6
7
 
select conducteurs.nom, conducteurs.prenom
from conducteurs
join achats on conducteurs.noc=achats.noc
join voitures on achats.nov=voitures.nov
GROUP BY conducteurs.nom, conducteurs.prenom
HAVING COUNT(NULLIF(voitures.type, 'CLIO')) = 0

[escartefigue]

Par ailleurs, il y a plus simple :

Code :

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

1
2
3
4
5
6
7
 
select conducteurs.nom, conducteurs.prenom
from conducteurs
join achats on conducteurs.noc=achats.noc
join voitures on achats.nov=voitures.nov
GROUP BY conducteurs.nom, conducteurs.prenom
HAVING COUNT(NULLIF(voitures.type, 'CLIO')) = 0

Cette solution ne convient pas, car il faut également vérifier que le conducteur n'a pas acheté d'autre modèle de voiture :

La question est
Quels sont les conducteurs qui n'ont acheté que des voitures de type clio ?

, il manque donc un NOT EXISTS...

[anadolu]

Ah d'accord! Mais à ce point là on n'a pas encore vu le nullif

[aieeeuuuuu]

il faut également vérifier que le conducteur n'a pas acheté d'autre modèle de voiture

C'est bien le cas avec cette requête : ceux qui n'ont pas acheté de voiture disparaitront lors de la jointure avec la table achats

[aieeeuuuuu]

Ah d'accord! Mais à ce point là on n'a pas encore vu le nullif

NULLIF est juste un CASE.
Vous pouvez d'ailleurs le remplacer par un CASE :

Code :

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

1
2
 
HAVING COUNT(CASE WHEN voitures.type =  'CLIO' THEN NULL ELSE voitures.type  END)) = 0

[anadolu]

D'accord, merci. Mais de même nous n'utilisons pas encore le case même si je vois très bien à quoi vous faites référence