Re-re-re-représentation de représentants (épisode ultime)

[1 h]

Soit la base de données suivante :

Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
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)
REPRESENTANTS (#NR, NOMR, VILLE)
PRODUITS (#NP, NOMP, COUL, PDS)
CLIENTS (#NC, NOMC, VILLE)
VENTES (#NR=>REPRESENTANTS(NR), #NP=>PRODUITS(NP), #NC=>CLIENTS(NC), QT)
num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
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);
66
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 5, 1);
/* Les requêtes peuvent être testées dans un SGBDR, en créant une base de données avec le script SQL suivant */

/*
DROP TABLE VENTES ;
DROP TABLE CLIENTS ;
DROP TABLE PRODUITS ;
DROP TABLE REPRESENTANTS ;
*/

CREATE TABLE REPRESENTANTS (
	NR INTEGER PRIMARY KEY,
	NOMR VARCHAR,
	VILLE VARCHAR
);

CREATE TABLE PRODUITS (
	NP INTEGER PRIMARY KEY,
	NOMP VARCHAR,
	COUL VARCHAR,
	PDS INTEGER
);

CREATE TABLE CLIENTS (
	NC INTEGER PRIMARY KEY,
	NOMC VARCHAR,
	VILLE VARCHAR
);

CREATE TABLE VENTES (
	NR INTEGER REFERENCES REPRESENTANTS(NR),
	NP INTEGER REFERENCES PRODUITS(NP),
	NC INTEGER REFERENCES CLIENTS(NC),
	QT INTEGER,
	PRIMARY KEY (NR, NP, NC)
);

INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (1, 'Stephane', 'Lyon');
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (2, 'Benjamin', 'Paris');
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (3, 'Leonard', 'Lyon');
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (4, 'Antoine', 'Brest');
INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (5, 'Bruno', 'Bayonne');

INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (1, 'Aspirateur', 'Rouge', 3546);
INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (2, 'Trottinette', 'Bleu', 1423);
INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (3, 'Chaise', 'Blanc', 3827);
INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (4, 'Tapis', 'Rouge', 1423);

INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (1, 'Alice', 'Lyon');
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (2, 'Bruno', 'Lyon');
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (3, 'Charles', 'Compiègne');
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (4, 'Denis', 'Montpellier');
INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (5, 'Emile', 'Strasbourg');

INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 1, 1, 1);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 1, 2, 1);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (2, 2, 3, 1);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (4, 3, 3, 200);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 2, 190);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 3, 2, 300);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 2, 120);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 4, 120);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 4, 2);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 1, 3);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 1, 5);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 3, 1);
INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 5, 1);
Écrire en SQL les requêtes permettant d'obtenir les informations suivantes.
Question
On souhaite les numéros des clients qui n'ont rien acheté à un représentant de Paris.
Question
On souhaite les noms des représentants ayant vendu quelque chose aux clients 1 et 2.
Question
Pour chaque couple de représentants ayant vendu dans la même ville, on souhaite le nom de la ville, le nom de ces représentants tel que celui a vendu le plus est en premier.
Proposez une solution avec vue, une solution avec sous-requête dans la clause FROM et une solution avec sous-requête dans la clause WHERE
Question
On souhaite les noms des représentants ayant vendu quelque chose à tous les clients.
On proposera une solution avec une vue et au moins une solution avec des sous-requêtes.