Vue

Le service après vente est instruit à se comporter « pragmatiquement » vis à vis des clients, selon leur catégorie :

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

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

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

Le mécanisme de vue est fréquemment utilisé pour cacher la complexité d'un schéma de BD aux yeux d'opérateurs nécessitant un schéma simple, ou pour cacher des informations confidentielles aux opérateurs n'ayant pas le droit d'accéder à des informations sensibles dans le schéma.

Dans notre cas, les opérateurs du service après vente désirent avoir la catégorie du client au téléphone avec une instruction très simple :

select * from 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 "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 du client 1.

Indice

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

Solution

1
SELECT categorie FROM v_chiffre_affaire WHERE num=1;