Re-re-représentation de représentants

[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 produits vendus à un client de Lyon par un représentant de Lyon.
Question
On souhaite les noms des produits vendus à un client par un représentant issu de la même ville.
Question
On souhaite les noms des représentants qui n'ont jamais rien vendu.
Proposez deux solutions sans sous-requête (et sans vue) et deux solutions avec des sous-requêtes.
Question
On souhaite les numéros des clients ayant acheté quelque chose à au moins un représentant issu de la même ville.
Proposez une solution avec et une solution sans sous-requête.
Question
On souhaite les numéros de tous les produits tels qu'il n'en existe aucun autre ayant un poids plus faible.
Proposez une solution avec une vue, une solution avec une sous-requête dans la clause FROM et une solution avec une sous-requête dans la clause WHERE.