Représentation de représentants

[30 minutes]

Soit le schéma relationnel et le code SQL suivants :

1
REPRESENTANTS (#NR, NOMR, VILLE)
2
PRODUITS (#NP, NOMP, COUL, PDS)
3
CLIENTS (#NC, NOMC, VILLE)
4
VENTES (#NR=>REPRESENTANTS(NR), #NP=>PRODUITS(NP), #NC=>CLIENTS(NC), QT)
1
/* Les requêtes peuvent être testées dans un SGBDR, en créant une base de données avec le script SQL suivant */
2
3
/*
4
DROP TABLE VENTES ;
5
DROP TABLE CLIENTS ;
6
DROP TABLE PRODUITS ;
7
DROP TABLE REPRESENTANTS ;
8
*/
9
10
CREATE TABLE REPRESENTANTS (
11
	NR INTEGER PRIMARY KEY,
12
	NOMR VARCHAR,
13
	VILLE VARCHAR
14
);
15
16
CREATE TABLE PRODUITS (
17
	NP INTEGER PRIMARY KEY,
18
	NOMP VARCHAR,
19
	COUL VARCHAR,
20
	PDS INTEGER
21
);
22
23
CREATE TABLE CLIENTS (
24
	NC INTEGER PRIMARY KEY,
25
	NOMC VARCHAR,
26
	VILLE VARCHAR
27
);
28
29
CREATE TABLE VENTES (
30
	NR INTEGER REFERENCES REPRESENTANTS(NR),
31
	NP INTEGER REFERENCES PRODUITS(NP),
32
	NC INTEGER REFERENCES CLIENTS(NC),
33
	QT INTEGER,
34
	PRIMARY KEY (NR, NP, NC)
35
);
36
37
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (1, 'Stephane', 'Lyon');
38
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (2, 'Benjamin', 'Paris');
39
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (3, 'Leonard', 'Lyon');
40
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (4, 'Antoine', 'Brest');
41
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (5, 'Bruno', 'Bayonne');
42
43
INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (1, 'Aspirateur', 'Rouge', 3546);
44
INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (2, 'Trottinette', 'Bleu', 1423);
45
INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (3, 'Chaise', 'Blanc', 3827);
46
INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (4, 'Tapis', 'Rouge', 1423);
47
48
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (1, 'Alice', 'Lyon');
49
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (2, 'Bruno', 'Lyon');
50
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (3, 'Charles', 'Compiègne');
51
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (4, 'Denis', 'Montpellier');
52
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (5, 'Emile', 'Strasbourg');
53
54
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 1, 1, 1);
55
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 1, 2, 1);
56
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (2, 2, 3, 1);
57
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (4, 3, 3, 200);
58
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 2, 190);
59
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 3, 2, 300);
60
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 2, 120);
61
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 4, 120);
62
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 4, 2);
63
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 1, 3);
64
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 1, 5);
65
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 3, 1);
Écrire en SQL les requêtes permettant d'obtenir les informations ci-après.
Question
Tous les détails de tous les clients. 
Solution
1
SELECT *
2
FROM CLIENTS ;
Question
Les numéros et les noms des produits de couleur rouge et de poids supérieur à 2000. 
Solution
1
SELECT NP, NOMP
2
FROM PRODUITS
3
WHERE COUL='Rouge' 
4
AND PDS>2000 ;
Complément
Si on ne sait pas comment la couleur a été saisie (minuscules/majuscules), il faut utiliser les fonctions de conversion de caractères telles que UPPER ou LOWER.
1
SELECT NP, NOMP
2
FROM PRODUITS
3
WHERE LOWER(COUL)='rouge' 
4
AND PDS>2000 ;
Question
Les  représentants ayant vendu au moins un produit. 
Solution
1
SELECT NR
2
FROM VENTES ;  
Les vendeurs représentés dans la table ventes ont vendu quelque chose.
1
SELECT DISTINCT NR
2
FROM VENTES 
3
ORDER BY NR ;  
On peut dédoublonner et trier le résultat.
Question
Les noms des clients de Lyon ayant acheté un produit pour une quantité supérieure à 180. 
Solution
1
SELECT DISTINCT C.NOMC
2
FROM CLIENTS C, VENTES V
3
WHERE V.NC=C.NC 
4
AND V.QT>180 
5
AND C.VILLE='Lyon' ;
Question
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. 
Solution
1
SELECT DISTINCT R.NOMR, C.NOMC
2
FROM REPRESENTANTS R, CLIENTS C, PRODUITS P, VENTES V
3
WHERE V.NC=C.NC 
4
AND V.NR=R.NR 
5
AND V.NP=P.NP 
6
AND V.QT>100 
7
AND P.COUL='Rouge' ;