TD_SQL_ISAMM_M1_2010

Décembre 2010

Université de Manouba Institut supérieur des Arts de Multimédia Mastère Ingénierie des Médias TD+TP Bases de Données SQL ORACLE Pré requit : La base de données BIBLIOTHEQUE est initialement construite selon le MPD suivante : livre num_livre nom_livre Nbre_page Auteur Date_version

NUMBER VARCHAR2(100) NUMBER VARCHAR2(30) DATE

exemlaires code_exemplaire NUMBER num_livre NUMBER En_stock SMALLINT

Operation_bibliotheque Id_operation code_exemplaire Type_operation DATE_OPERATION NUM_CARTE_ETUDIANT

NUMBER NUMBER VARCHAR2(15) DATE NUMBER

Après une durée de manipulations on a constaté qu’il existe des incohérences de données et des informations manquantes dans la base de données, et le but de ce sujet c’est de faire la maintenance de cette base de données. Pour la création de la base de données il ya deux script à exécuter : - Script de création des Tablespaces (connectant SYSTEM) : C:\BIBLIO\BD\crebas_biblio_tbs.sql - Script de création des tables (connectant BIBLIO) : C:\BIBLIO\BD\crebas_biblio3.sql - Script d’insertion des données (connectant BIBLIO): C:\BIBLIO\BD\testdata.sql

Exercice 1: Les livres sont réparties sur trois types (INDUSTRIEL, SCIENTIFIQU SCIENTIFIQUE, E, INFORMATIQUE) Ecrire les instructions SQL suivantes pour: - Afficher les données de la table LIVRE - Modifier la structure de la table LIVRE (ajout de la colonne TYPE_LIVRE de type varchar2(30)) Remplir la colonne TYPE_LIV (INDUSTRIEL, SCIENTIFIQUE, INFORMATIQU INFORMATIQUE E ) en se basant sur les valeurs des colonnes existantes dans la table LIVRE /*** MISE A JOUR DE LA TABLE LIVRE ***/ select * from livre t order by 1 asc asc; ; alter table LIVRE add TYPE_LIVRE VARCHAR2 VARCHAR2( (50 50); );

TD+TP SQL ORACLE 2010/2011

Page 1

update livre t set t.type_livre = 'INDUSTRIEL' where t.nom_livre like 'MEC%'; update livre t set t.type_livre = 'SCIENTIFIQUE' where t.num_livre in (1,4,5,7); update livre t set t.type_livre = 'INFORMATIQUE' where t.type_livre is null;

Exercice 2: Le nombre de prêtes d’un exemplaire d’un livre doit être égale exactement au nombre de remboursement ou égale au nombre de remboursement plus 1. On doit chercher s’il y a des exemplaires qui ont eu une incohérence par rapport à cette règle de gestion. Ecrire les instructions SQL suivantes pour : - Afficher le nombre de prêtes pour chaque exemplaire - Afficher le nombre de remboursements pour chaque exemplaire - Afficher la différence entre le nombre de prêtes et le nombre de remboursements pour chaque exemplaire - Afficher seulement les exemplaires qui ont une incohérence par rapport à la règle de gestion ci-dessus. /*** ANALYSE DES incohérences de données existantes dans la table operation_bibliotheque ***/ /*RQ1*/ select o.code_exemplaire, count(*) nbre_prete from operation_bibliotheque o where o.type_operation = 'PRETER' group by o.code_exemplaire; /*RQ2*/ select o.code_exemplaire, count(*) nbre_remb from operation_bibliotheque o where o.type_operation = 'REMBOURSER' group by o.code_exemplaire; /*RQ3*/ select k.code_exemplaire, k.nbre_prete - g.nbre_remb DIFF_OP from (select o.code_exemplaire, count(*) nbre_prete from operation_bibliotheque o where o.type_operation = 'PRETER' group by o.code_exemplaire) k, (select from where group

o.code_exemplaire, count(*) nbre_remb operation_bibliotheque o o.type_operation = 'REMBOURSER' by o.code_exemplaire) g

where g.code_exemplaire = k.code_exemplaire and k.nbre_prete - g.nbre_remb in (0, 1);


Page 2

/*RQ4*/ select e.code_exemplaire, e.num_livre, e.en_stock, h.diff_op from exemlaires e, (select k.code_exemplaire, k.nbre_prete - g.nbre_remb DIFF_OP from (select o.code_exemplaire, count(*) nbre_prete from operation_bibliotheque o where o.type_operation = 'PRETER' group by o.code_exemplaire) k, (select from where group


where g.code_exemplaire = k.code_exemplaire and k.nbre_prete - g.nbre_remb in (0, 1)) h where e.code_exemplaire = h.code_exemplaire and e.en_stock <> h.diff_op;

Exercice 3: On veut maintenir les informations de la table OPERATION_BIBLIOTHEQUE à partir d’une autre table qui s’appelle LIVRES_ETUDIANTS qui se trouve dans un autre schéma qui s’appelle BO : Pour la création de la base de données BO vous pouvez créer le schéma BO puis lancer le script SQL suivant dedans : - Script de création de la table LIVRES_ETUDIANTS : C:\BIBLIO\BD\ INSERT_LIVRES_ETUDIANT.sql Ecrire les instructions SQL suivantes pour : - Ajouter la colonne NUM_CARTE_ETUDIANT de type NUMBER dans la table OPERATION_BIBLIOTHEQUE - Sauvegarder les informations de la table OPERATION_BIBLIOTHEQUE dans une table OPERATION_BIBLIOTHEQUE_COPY - Supprimer les données de la table OPERATION_BIBLIOTHEQUE - Copier les opérations de prêtes des exemplaires de la table LIVRES_ETUDIANTS utilisant le ROWNUM de la requête comme ID_OPERATION - Idem pour les opérations de remboursements /*RQ1*/ alter table operation_bibliotheque add num_carte_etudiant number; /*RQ2*/ create table operation_bibliotheque_copy as select * from operation_bibliotheque; /*RQ3*/ delete operation_bibliotheque; /*RQ4 */ insert into operation_bibliotheque (id_operation, code_exemplaire, type_operation, date_operation, num_carte_etudiant) select rownum, l.code_exemplaire, 'PRETER', l.date_prete, l.num_carte_etudiant from bo.livres_etudiants l;


Page 3

/*RQ5 le résultat de RQ4 montre que le nombre d’opérations de prêtes égale à 68*/ insert into operation_bibliotheque (id_operation, code_exemplaire, type_operation, date_operation, num_carte_etudiant) select rownum + 68, l.code_exemplaire, 'REMBOURSER', l.date_remboursement, l.num_carte_etudiant from bo.livres_etudiants l where l.date_remboursement is not null;

Exercice 4: Après la maintenance des informations de la table OPERATION_BIBLIOTHEQUE on doit s’assurer que les valeurs du champ EN_STOCK de la table EXEMPLAIRES sont justes: Ecrire les instructions SQL suivantes pour : - Afficher le code de l’exemplaire et sa valeur EN_STOCK existante, et la valeur réelle qu’on va appeler VAL_STOCK qui doit être égale à : ( 1- ( NBRE_PRETE - NBRE_REMB ) )

-

Modifier tous les données de la colonne EN_STOCK de la table EXEMPLAIRES à la valeur par défaut 1. Modifier les données de la colonne EN_STOCK des exemplaires qui ont eu des opérations de prêtes et de remboursements par leurs valeurs réelles.

/*** MISE A JOUR DE LA TABLE EXEMLAIRES ***/ /*RQ1*/ select e.code_exemplaire,e.en_stock, j.val_stock from exemlaires e, (select h.code_exemplaire, h.nbre_prete, g.nbre_remb, 1 - (h.nbre_prete - g.nbre_remb) val_stock from (select o.code_exemplaire, count(*) nbre_prete from operation_bibliotheque o where o.type_operation = 'PRETER' group by o.code_exemplaire) h, (select o.code_exemplaire, count(*) nbre_remb from operation_bibliotheque o where o.type_operation = 'REMBOURSER' group by o.code_exemplaire) g where h.code_exemplaire = g.code_exemplaire and h.nbre_prete - g.nbre_remb in (0, 1)) j where e.code_exemplaire = j.code_exemplaire and j.val_stock <> e.en_stock /*RQ2*/ update exemlaires e set e.en_stock =1;


Page 4

/*RQ3*/ update exemlaires e set e.en_stock = (select 1 - (k.nbre_prete - g.nbre_remb) from (select o.code_exemplaire, count(*) nbre_prete from operation_bibliotheque o where o.type_operation = 'PRETER' group by o.code_exemplaire) k, (select from where group


where g.code_exemplaire = k.code_exemplaire and k.code_exemplaire = e.code_exemplaire);


Page 5

TD_SQL_ISAMM_M1_2010

Recommend Documents