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

[1 h]

Soit la base de données suivante :

REPRESENTANTS (#NR, NOMR, VILLE)
PRODUITS (#NP, NOMP, COUL, PDS)
CLIENTS (#NC, NOMC, VILLE)
VENTES (#NR=>REPRESENTANTS(NR), #NP=>PRODUITS(NP), #NC=>CLIENTS(NC), QT)

Écrire en SQL les requêtes permettant d'obtenir les informations suivantes.

On souhaite les numéros des clients qui n'ont rien acheté à un représentant de Paris.

 nc 
----
  1
  2
  4
  5

SELECT C.NC
FROM CLIENTS C
WHERE 'Paris' NOT IN 
(
...
) ;

SELECT C.NC
FROM CLIENTS C
WHERE 'Paris' NOT IN 
(
   SELECT R.VILLE 
   FROM VENTES V, REPRESENTANTS R 
   WHERE V.NR=R.NR 
   AND V.NC=C.NC
) ;

Solution alternative

SELECT C.NC
FROM CLIENTS C
WHERE NOT EXISTS (
   SELECT R.* 
   FROM VENTES V, REPRESENTANTS R 
   WHERE V.NR=R.NR 
   AND V.NC=C.NC 
   AND R.VILLE='Paris' 
) ;

On souhaite les noms des représentants ayant vendu quelque chose aux clients 1 et 2.

   nomr   
----------
 Stephane
 Leonard

SELECT R.NOMR
FROM REPRESENTANTS R
WHERE 1 IN (...) 
AND 2 IN (...);

SELECT R.NOMR
FROM REPRESENTANTS R
WHERE 1 IN (SELECT V.NC FROM VENTES V WHERE V.NR=R.NR) 
AND 2 IN (SELECT V.NC FROM VENTES V WHERE V.NR=R.NR);

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

   ville   |  nomr   |   nomr   
-----------+---------+----------
 Compiègne | Antoine | Leonard
 Compiègne | Antoine | Benjamin
 Lyon      | Leonard | Stephane

CREATE VIEW VR as SELECT R.NOMR, SUM(V.QT) total, C.VILLE
FROM REPRESENTANTS R, VENTES V, CLIENTS C
WHERE R.NR = V.NR 
AND V.NC = C.NC
GROUP BY R.NOMR, C.VILLE ;
SELECT R1.VILLE, R1.NOMR, R2.NOMR
FROM VR R1, VR R2 
WHERE R1.VILLE = R2.VILLE 
AND R1.total > R2.total ;

SELECT R1.VILLE, R1.NOMR, R2.NOMR
FROM (
   SELECT R.NOMR, SUM(V.QT) total, C.VILLE
   FROM REPRESENTANTS R, VENTES V, CLIENTS C
   WHERE R.NR = V.NR 
   AND V.NC = C.NC
   GROUP BY R.NOMR, C.VILLE
)  R1, (
   SELECT R.NOMR, SUM(V.QT) total, C.VILLE
   FROM REPRESENTANTS R, VENTES V, CLIENTS C
   WHERE R.NR = V.NR 
   AND V.NC = C.NC
   GROUP BY R.NOMR, C.VILLE
)  R2 
WHERE R1.VILLE = R2.VILLE 
AND R1.total > R2.total ;

SELECT DISTINCT R1.NOMR, R2.NOMR, C1.VILLE
FROM REPRESENTANTS R1, REPRESENTANTS R2, CLIENTS C1
WHERE 
   (SELECT SUM(V.QT) FROM VENTES V, CLIENTS C WHERE V.NR = R1.NR AND V.NC = C.NC AND C.VILLE = C1.VILLE) >
   (SELECT SUM(V.QT) FROM VENTES V, CLIENTS C WHERE V.NR = R2.NR AND V.NC = C.NC AND C.VILLE = C1.VILLE) ;

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.

 nomr   
---------
 Leonard

Avec une vue

CREATE VIEW vue AS
SELECT R.NOMR, C.NC
FROM REPRESENTANTS R, CLIENTS C
EXCEPT
SELECT R.NOMR, V.NC
FROM REPRESENTANTS R, VENTES V
WHERE V.NR=R.NR;
SELECT R.NOMR
FROM REPRESENTANTS R, VENTES V
WHERE R.NR=V.NR
EXCEPT
SELECT NOMR FROM vue;

Sans vue

SELECT R.NOMR
FROM REPRESENTANTS R
WHERE R.NR IN (
   SELECT V.NR 
   FROM VENTES V 
   GROUP BY V.NR 
   HAVING COUNT(DISTINCT V.NC) = (SELECT COUNT (*) FROM CLIENTS)) ;

SELECT R.NOMR
FROM REPRESENTANTS R
WHERE 
   (SELECT COUNT(DISTINCT V.NC) FROM VENTES V WHERE V.NR=R.NR) 
      =
   (SELECT COUNT (*) FROM CLIENTS) ;

SELECT R.NOMR
FROM REPRESENTANTS R
WHERE NOT EXISTS (
   SELECT C.*
   FROM CLIENTS C
   WHERE NOT EXISTS (
      SELECT V.*
      FROM VENTES V
      WHERE C.NC=V.NC AND V.NR=R.NR) 
) ;

AccueilExercice > Exercice : Re-re-re-représentation de représentants (épisode ultime)< PrécédentSuivant >