Re-re-représentation de représentants

[1 h]

Soit la base de données suivante :

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);
66
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.
Indice
1
 np 
2
----
3
  1
4
  4
5
  3
6
Question
On souhaite les noms des produits vendus à un client par un représentant issu de la même ville.
Indice
1
    nomp    
2
------------
3
 Aspirateur
4
 Chaise
5
 Tapis
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.
Indice
1
 nomr  
2
-------
3
 Bruno
Indice
Pour une solution sans vue, utilisez une jointure externe.
Indice
Pour une solution sans vue, utilisez une différence.
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.
Indice
1
 nc 
2
----
3
  1
4
  2
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.
Indice
1
np 
2
----
3
  2
4
  4