SQL — 2ème partie

Télécharger le fichier disques2009.sql qui contient toutes les instructions SQL nécessaires à la création de la base.

Utiliser d'abord la commande :

où base est un nom à votre choix (qui ne doit pas déjà exister).

Connectez vous ensuite à votre base

Puis tapez (ou recopiez) la commande :

Cela aura pour effet de créer les tables et de les remplir.

L'utilisateur qui a créé les tables a tous les droits sur ces tables. Si rien n'est précisé aucun autre utilisateur ne peut faire la moindre modification sur les tables (contenu ou structure). Ce qui fait que vous ne pourrez tester les commandes de ce chapitre que sur une base de données que vous aurez créée personnellement (voir Chapitre 1, Créer votre propre base).

On étudiera plus tard (Chapitre 3, Les droits) la possibilité de donner (ou enlever) aux autres utilisateurs les droits de modifier la base de données.

Une table ayant été créée, son propriétaire peut accorder (GRANT) ou retirer (REVOKE) à un autre utilisateur (ou à tous : PUBLIC) les droits suivants :

La syntaxe en est :

À tout moment vous pouvez afficher les droits actuellement donnés par la commande « \dp » ou « \z »^[1].

Solution de l'exercice 2. 

BEGIN ;
SELECT COUNT(*)
  FROM artiste
 WHERE art_modif IS NULL ;

UPDATE artiste
   SET art_modif = CURRENT_TIMESTAMP
 WHERE art_modif IS NULL ;

SELECT COUNT(*)
  FROM artiste
 WHERE art_modif IS NULL ;
COMMIT ;

Solution de l'exercice 3. 

BEGIN ;
SELECT *
  FROM autorisations
 WHERE aut_droits  IN (SELECT dro_num
                         FROM droits
                        WHERE dro_nom='Membre') ;
UPDATE autorisations
   SET aut_droits = (SELECT dro_num
                       FROM droits
                      WHERE dro_nom='Anonyme')
 WHERE aut_droits IN (SELECT dro_num
                       FROM droits
                      WHERE dro_nom='Membre') ;
SELECT *
  FROM autorisations
 WHERE aut_droits  IN (SELECT dro_num
                         FROM droits
                        WHERE dro_nom='Membre') ;
COMMIT ;

Solution de l'exercice 4. 

BEGIN ;
DELETE
  FROM artiste
 WHERE art_nom = 'Steppenwolf' ;
COMMIT ;

Solution de l'exercice 5. 

CREATE VIEW disqueartiste
         AS SELECT dis_titre AS da_titre, art_prenom AS da_prenom,
                   art_nom AS da_nom, art_groupe AS da_groupe,
                   dis_num AS da_disnum, art_num AS da_artnnum
              FROM disque, artiste
             WHERE dis_artiste = art_num ;
SELECT *
  FROM disqueartiste
 WHERE da_nom LIKE 'Ar%' ;
UPDATE artiste
   SET art_nom = 'Arnaud'
 WHERE art_nom = 'Arno' ;
SELECT *
  FROM disqueartiste
 WHERE da_nom LIKE 'Ar%' ;

Solution de l'exercice 1. 

GRANT SELECT
   ON genre
   TO nom de l'autre user ;

Solution de l'exercice 2. 

GRANT INSERT,UPDATE
   ON genre
   TO nom de l'autre user ;

Solution de l'exercice 3. 

GRANT ALL ON disque TO nom de l'autre user ;

Solution de l'exercice 4. 

CREATE VIEW disqvue
         AS SELECT TRIM(art_prenom||' '||art_nom||' '||art_groupe) AS artiste,
                   dis_titre AS titre,
                   dis_annee AS annee
              FROM disque,artiste
             WHERE dis_artiste = art_num ;
GRANT SELECT ON disqvue
             TO nom de l'autre user ;

Solution de l'exercice 5. 

CREATE VIEW davidBowie
         AS SELECT cha_titre AS "titre de la chanson",
                   dis_titre AS "titre du disque",
                   TRIM(d.art_prenom||' '||d.art_nom||' '||d.art_groupe) AS "artiste",
                   int_numero AS "position sur le disque",
                   dis_annee AS "année du disque"
              FROM disque,artiste d,artiste b,chanson,interprete
             WHERE cha_num = int_chanson
               AND int_disque = dis_num
               AND dis_artiste = d.art_num
               AND int_artiste = b.art_num
               AND LOWER(b.art_prenom) = 'david'
               AND LOWER(b.art_nom) = 'bowie'
               AND LOWER(b.art_groupe) = '' ;
GRANT ALL
   ON davidBowie
   TO nom de l'autre user ;

Solution de l'exercice 6. 

REVOKE INSERT ON genre FROM nom de l'autre user ;

Solution de l'exercice 1.  Écrire une ligne de commandes commençant par

SELECT NOW();

contenant ensuite 10 fois

SELECT cha_num,cha_titre FROM chanson WHERE cha_titre ='Lady Stardust';

et se terminant par

SELECT NOW();

Il vous reste à faire la différence des deux temps, et à diviser par 10... Vous avez alors la durée nécessaire à l'exécution de cette requête sans index.

Pour créer l'index :

CREATE INDEX idx_nom ON chanson (cha_titre) ;

En recommençant la première opération vous pourrez calculer la durée nécessaire à l'exécution de cette requête avec index. Cette dernière durée doit être beaucoup plus petite que la première.

Solution de l'exercice 2. 

DROP INDEX idx_nom ;