Vue

[20 min]

Le service après vente (SAV) est instruit à se comporter vis à vis des clients, selon leur catégorie :

  • "VIP" si le chiffre d'affaire pour ce client est supérieur à 500 euros,

  • "client ordinaire" si le chiffre d'affaire est compris entre 50 et 500 euros,

  • et "client à potentiel" si le chiffre d'affaire est inférieur à 50 euros.

Le mécanisme de vue est fréquemment utilisé pour masquer la complexité d'un schéma de BD aux yeux d'opérateurs nécessitant un schéma simple (on parle de schéma externe).

Dans notre cas, les opérateurs du SAV désirent avoir la catégorie du client au téléphone grâce à une instruction très simple :

select * from v_chiffre_affaire where code_client=# ;

Question

Écrivez la requête SQL LMD qui permet de renvoyer la liste des clients (num, nom et prénom) avec leur chiffre d'affaire et leur catégorie (VIP, Ordinaire ou Potentiel)

Indice

Utiliser l'extension SQL CASE WHEN d'Oracle :

SELECT CASE WHEN ... THEN  'X' WHEN ... THEN 'Y' ELSE 'Z' END FROM ...

Solution

1
SELECT c.num, c.nom, c.prenom, sum(p.prix*lf.qte) chiffre_affaire,
2
  CASE
3
    WHEN sum(p.prix*lf.qte)>500 THEN 'VIP'
4
    WHEN sum(p.prix*lf.qte) BETWEEN 50 AND 500 THEN 'Ordinaire'
5
  ELSE 'Potentiel'
6
  END categorie
7
FROM client c, produit p, facture f, ligne_fact lf
8
WHERE p.num=lf.produit
9
AND lf.facture=f.num
10
AND f.client=c.num
11
GROUP BY c.num, c.nom, c.prenom;

Question

Écrivez la requête SQL LDD qui crée la vue "v_chiffre_affaire" permettant de répondre au besoin des opérateurs du service après vente.

Solution

1
CREATE VIEW v_chiffre_affaire AS
2
SELECT c.num, c.nom, c.prenom, sum(p.prix*lf.qte) chiffre_affaire,
3
  CASE
4
    WHEN sum(p.prix*lf.qte)>500 THEN 'VIP'
5
    WHEN sum(p.prix*lf.qte) BETWEEN 50 AND 500 THEN 'Ordinaire'
6
  ELSE 'Potentiel'
7
  END categorie
8
FROM client c, produit p, facture f, ligne_fact lf
9
WHERE p.num=lf.produit
10
AND lf.facture=f.num
11
AND f.client=c.num
12
GROUP BY c.num, c.nom, c.prenom;

Question

Écrire la requête SQL LMD qui renvoie la catégorie d'un client ayant pour nom Morin.

Indice

Utilisez la vue dans la clause FROM de votre requête.

Solution

1
SELECT * FROM v_chiffre_affaire WHERE nom='morin';