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
) ;
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
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;
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)
) ;