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

[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 clients qui n'ont rien acheté à un représentant de Paris.
Indice
1
 nc 
2
----
3
  1
4
  2
5
  4
6
  5
Indice
1
SELECT C.NC
2
FROM CLIENTS C
3
WHERE 'Paris' NOT IN 
4
(
5
...
6
) ;
Solution
1
SELECT C.NC
2
FROM CLIENTS C
3
WHERE 'Paris' NOT IN 
4
(
5
   SELECT R.VILLE 
6
   FROM VENTES V, REPRESENTANTS R 
7
   WHERE V.NR=R.NR 
8
   AND V.NC=C.NC
9
) ;
Solution alternative
1
SELECT C.NC
2
FROM CLIENTS C
3
WHERE NOT EXISTS (
4
   SELECT R.* 
5
   FROM VENTES V, REPRESENTANTS R 
6
   WHERE V.NR=R.NR 
7
   AND V.NC=C.NC 
8
   AND R.VILLE='Paris' 
9
) ;
Question
On souhaite les noms des représentants ayant vendu quelque chose aux clients 1 et 2.
Indice
1
   nomr   
2
----------
3
 Stephane
4
 Leonard
Indice
1
SELECT R.NOMR
2
FROM REPRESENTANTS R
3
WHERE 1 IN (...) 
4
AND 2 IN (...);
Solution
1
SELECT R.NOMR
2
FROM REPRESENTANTS R
3
WHERE 1 IN (SELECT V.NC FROM VENTES V WHERE V.NR=R.NR) 
4
AND 2 IN (SELECT V.NC FROM VENTES V WHERE V.NR=R.NR);
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
Indice
1
   ville   |  nomr   |   nomr   
2
-----------+---------+----------
3
 Compiègne | Antoine | Leonard
4
 Compiègne | Antoine | Benjamin
5
 Lyon      | Leonard | Stephane
Solution
1
CREATE VIEW VR as SELECT R.NOMR, SUM(V.QT) total, C.VILLE
2
FROM REPRESENTANTS R, VENTES V, CLIENTS C
3
WHERE R.NR = V.NR 
4
AND V.NC = C.NC
5
GROUP BY R.NOMR, C.VILLE ;
6
7
SELECT R1.VILLE, R1.NOMR, R2.NOMR
8
FROM VR R1, VR R2 
9
WHERE R1.VILLE = R2.VILLE 
10
AND R1.total > R2.total ;
1
SELECT R1.VILLE, R1.NOMR, R2.NOMR
2
FROM (
3
   SELECT R.NOMR, SUM(V.QT) total, C.VILLE
4
   FROM REPRESENTANTS R, VENTES V, CLIENTS C
5
   WHERE R.NR = V.NR 
6
   AND V.NC = C.NC
7
   GROUP BY R.NOMR, C.VILLE
8
)  R1, (
9
   SELECT R.NOMR, SUM(V.QT) total, C.VILLE
10
   FROM REPRESENTANTS R, VENTES V, CLIENTS C
11
   WHERE R.NR = V.NR 
12
   AND V.NC = C.NC
13
   GROUP BY R.NOMR, C.VILLE
14
)  R2 
15
WHERE R1.VILLE = R2.VILLE 
16
AND R1.total > R2.total ;
1
SELECT DISTINCT R1.NOMR, R2.NOMR, C1.VILLE
2
FROM REPRESENTANTS R1, REPRESENTANTS R2, CLIENTS C1
3
WHERE 
4
   (SELECT SUM(V.QT) FROM VENTES V, CLIENTS C WHERE V.NR = R1.NR AND V.NC = C.NC AND C.VILLE = C1.VILLE) >
5
   (SELECT SUM(V.QT) FROM VENTES V, CLIENTS C WHERE V.NR = R2.NR AND V.NC = C.NC AND C.VILLE = C1.VILLE) ;
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.
Indice
1
 nomr   
2
---------
3
 Leonard
Solution
Avec une vue
1
CREATE VIEW vue AS
2
SELECT R.NOMR, C.NC
3
FROM REPRESENTANTS R, CLIENTS C
4
EXCEPT
5
SELECT R.NOMR, V.NC
6
FROM REPRESENTANTS R, VENTES V
7
WHERE V.NR=R.NR;
8
9
SELECT R.NOMR
10
FROM REPRESENTANTS R, VENTES V
11
WHERE R.NR=V.NR
12
EXCEPT
13
SELECT NOMR FROM vue;
Sans vue
1
SELECT R.NOMR
2
FROM REPRESENTANTS R
3
WHERE R.NR IN (
4
   SELECT V.NR 
5
   FROM VENTES V 
6
   GROUP BY V.NR 
7
   HAVING COUNT(DISTINCT V.NC) = (SELECT COUNT (*) FROM CLIENTS)) ;
1
SELECT R.NOMR
2
FROM REPRESENTANTS R
3
WHERE 
4
   (SELECT COUNT(DISTINCT V.NC) FROM VENTES V WHERE V.NR=R.NR) 
5
      =
6
   (SELECT COUNT (*) FROM CLIENTS) ;
1
SELECT R.NOMR
2
FROM REPRESENTANTS R
3
WHERE NOT EXISTS (
4
   SELECT C.*
5
   FROM CLIENTS C
6
   WHERE NOT EXISTS (
7
      SELECT V.*
8
      FROM VENTES V
9
      WHERE C.NC=V.NC AND V.NR=R.NR) 
10
) ;