TP Atelier 02 – VUES et INDEX

Remarques préalables :

- Travail à faire avec l’éditeur de requête sur le la BD CAFE

-  Le texte de chaque requête doit être placé dans un fichier (ex : fichier TP02-R1.sql pour la requête R1).

Ecrire et exécuter les requêtes ci-dessous :

R1 : Créer un index sur le nom de la consommation.

CREATE INDEX idxNomConso

ON conso(libcons);

R2 : Créer un index sur le couple code postal/ville.

CREATE INDEX idxCpVille

ON serveur(cpserv, libserv);

R3 : On effectue souvent des recherches à partir du nom du serveur. On vous demande de trouver une solution pour accélérer ces requêtes et d'écrire la requête correspondante.

CREATE INDEX idxNomServ

ON serveur(nomserv);

R4 : On effectue souvent des jointures entre facture et serveur. On vous demande de trouver une solution pour accélérer ces requêtes et d'écrire la requête correspondante.

CREATE VIEW v_factServ

AS

SELECT f.*, s.nomserv, s.rueserv

FROM serveur s

INNER JOIN facture f  ON s.numserv = f.numserv

R5 : Créer une vue permettant d'afficher le chiffre d'affaires de la journée. La date du jour est contenue dans CURRENT_DATE.

Vérifier que la vue est correcte (pour cela modifier la date d’une facture au jour d’aujourd’hui).

CREATE VIEW v_caParJour

AS

SELECT SUM(qte * prixcons) ca, datefact

FROM facture f

INNER JOIN ligne_fact l ON f.numfact = l.numfact

INNER JOIN conso c ON c.numcons = l.numcons

GROUP BY datefact

R6 : Créer une vue permettant d'obtenir les factures établies par les serveurs de Paris.

CREATE VIEW v_factServParis

AS

SELECT numfact, datefact, nomserv

FROM facture f

INNER JOIN serveur s ON f.numserv = s.numserv

WHERE villeserv = 'PARIS'

R7 :

1. créer une vue comportant tous les serveurs de BELFORT sans with check option puis modifier la ville (mettre Paris) de l'un des serveurs à travers la vue et constater que cela fonctionne mais que le tuple n'est plus visible lors de la sélection de la vue.

CA NE MARCHE PAS !!!

create view v_serv_BELFORT

as

SELECT *

from serveur

WHERE villeserv = 'BELFORT';

2. créer une 2^ème vue comportant tous les serveurs de BELFORT mais cette fois avec with check option puis modifier la ville (mettre Paris) de l'un des serveurs à travers la vue et constater que cela ne fonctionne pas.

create view v_serv_BELFORT

as

SELECT *

from serveur

WHERE villeserv = 'BELFORT'

WITH CHECk OPTION;   => IMPOSSIBLE dans l’éditeur de requête