TP ORACLE : manipulation des données et requetes
Rédigé par GC Team, Publié le 26 Avril 2010, Mise à jour le Lundi, 26 Avril 2010 05:00![](/images/thumb/122/default.png)
Ce TP a pour but de mettre en application ce que nous avons déjà vu au cours des TD précédents, c'est à dire la manipulation d'une base de données grâce au langage SQL (Structured Query Langage).
Nous verrons ainsi comment :
- on crée une base au moyen du langage de définition des données
- on met à jour les données (insertion, modification, suppression) avec le langage de définition des données
- on interroge les données avec le langage de manipulation des données
- on met en place des contrôles portant sur l'accés aux données grâce au langage de contrôle des données
Nous travaillerons sur le SGBD ORACLE et nous pourrons ainsi voir que SQL compris par le SGBD ORACLE est plus proche de la norme AINSI que ne l'est celui utilisé dans le SGBD ACCESS.
Pour pouvoir effectuer toutes ces opérations, nous allons taper les instructions au clavier. La saisie se fera dans sqlplus, logiciel qui présente une interface assez peu conviviale. Nous verrons ainsi l'intérêt de saisir les instructions dans un fichier texte au moyen d'un éditeur (WordPad, de préférence lorsque l'on se trouve sous le système d'exploitation Windows). Pour lancer sqlplus, il suffit de double cliquer sur l'icône présente sur le bureau Windows. Il vous sera indiqué en TP le login ainsi que le mot de passe nécessaires pour accéder au SGBD.
Tout au long de ce TP, nous nous appuierons sur un élément du SGBD qui a une très grande importance et au travers duquel nous pourrons vérifier de façon aisée et rapide si les instructions que nous avons soumises ont bien été exécutées par le SGBD. Cet élément présent au niveau de tout SGBD, quel qu'il soit, est le dictionnaire des données.
I - Dictionnaire des données
Le dictionnaire de données est un ensemble de tables dans lesquelles sont stockées les descriptions des objets de la base. Les tables de ce dictionnaire peuvent être consultées au moyen du langage SQL.
Des vues de ces tables permettent à l'utilisateur de voir les objets qui lui appartiennent ou sur lesquels il a des droits. L'administrateur a pour sa part accés à toutes les vues.
Quelques vues et tables du dictionnaire de données :
USER_TABLES: tables et vues créées par l'utilisateur
USER_CATALOG (ou CAT): tables et vues sur lesquelles l'utilisateur a des droits à l'exception des tables et vues du dictionnaire de données
USER_TAB_COLUMNS (ou COLS): colonne de chaque table ou vue créée par l'utilisateur courant
USER_CONSTRAINTS: définition des contraintes pour les tables des utilisateurs
USER_CONS_COLUMNS: colonnes qui interviennent dans les définitions des contraintes
Exemple de consultation du dictionnaire de données :
Si vous voulez lister les caractéristiques des tables que vous avez créées en tant qu'utilisateur de la base (ayant les droits de création de tables, bien entendu), vous poserez la requête suivante au SGBD :
SELECT * FROM USER_TABLES;
Question 1 : quels sont les attributs de la vue USER_TABLES ?
Question 2 : combien de tables sont présentes ?
Si vous souhaitez lister uniquement les noms des tables que vous avez créées, vous poserez la requête suivant
SELECT TABLE_NAME FROM USER_TABLES;
Question 3 : quels sont les noms des tables qui ont été créées ?
Question 4 : comment procèderiez-vous pour obtenir la liste des contraintes que vous avez imposées sur les tables créées ?
Question 5 : à votre avis, comment obtiendrez-vous la liste des noms des contraintes que vous avez imposées sur les tables créées ?
II Description des tables de la base de données
Pour obtenir la description en terme d'attributs de chacune des tables, vous pouvez utiliser la commande sqlplus suivante :
DESCRIBE nom_table
ou bien
DESC nom_table
Question 6 : donner le schéma relationnel de la relation COMMANDES ?
III Création d'un nouveau schéma de base de données
Nous allons maintenant manipuler la base de données sur les sociétés pharmaceutiques sur laquelle nous avons travaillé en TD (TD 5).
Pour créer les différentes tables de la base de données, nous nous servons d'instructions dont la syntaxe est la suivante :
CREATE TABLE nom_table
(
att1 type_att1,
att2 type_att2,
..... ,
atti type_atti,
..... ,
attn val_attn
);
Il est également possible de préciser les contraintes que l'on veut imposer. Reportez-vous au texte du TD 5 pour la syntaxe utilisée dans les divers cas de contraintes.
Le script de création a été saisi. Vous le trouverez dans le répertoire indiqué au cours de la séance de TP.
Editez ce script. Copiez-en le contenu et collez-le dans sqlplus.
Question 7 : quel type d'affichage observez- vous dans l'inferface sqlplus ?
Question 8 : combien de tables sont désormais présentes ?
Question 9 : lister le nom et la date de dernière modification de chacune des nouvelles tables apparues.
Question 10 : donner la description de la relation SOIGNE.
Question 11 : donner les noms ainsi que le type des contraintes posées sur ces nouvelles tables.
IV Insertion de données dans la base de données
Question 12 : quelle requête SQL poseriez-vous pour connaître le contenu de la table EMPLOYE ? Que constatez-vous ?
Pour insérer des données dans une table
INSERT INTO nom_table VALUES (val_att1, val_att2, ......, val_atti, ......., val_attn);
Un script d'insertion d'un certain nombre de données a été saisi. Il vous sera indiqué durant la séance où vous pourrez le trouver sur le disque dur de l'ordinateur. Procédez comme précédemment par copier/coller dans l'interface sqlplus.
Question 13 : quel type d'affichage observez- vous dans l'inferface sqlplus ?
Question 14 : quel est désormais le contenu de la table EMPLOYE ?
Question 15 : quelle requête poseriez-vous pour connaître le nombre d'employés qui sont répertoriées dans la base de données ?
Question 16 : insérez l'employé Toto qui est né le 09/12/66, a été embauché 01/01/01 par la société Sanofi Synthélabo pour un salaire de 850 euros et avec pour responsable direct le PDG de la société.
Question 17 : insérez le médicament Solutricine qui a pour principe actif la tyrothricine et est fabriqué par Aventis. La solutricine est distribuée sous forme de boîte de 72 comprimés à sucer. La tyrothricine soigne les maux de gorge peu intenses mais également les aphtes (petites plaies de la bouche).
V Mise à jour des données
Pour faire des mises à jour des données, il faut écrire des instructions du type :
UPDATE nom_table SET atti = new_val WHERE attj = une_valeur;
Question 18 : les employés qui ne sont pas des PDG sont augmentés chacun de 10%. Ecrivez l'instruction permet de faire cette modification des données de la base.
VI Suppression de données
La suppression de données se fait au moyen d'instructions dont la syntaxe est :
DELETE FROM nom_table WHERE attj = une_valeur;
Question 19 : supprimez l'employé qui est né le 09/12/66 et a été embauché 01/01/01 par la société Sanofi Synthélabo.
VI Mise à jour du schéma
Il est possible de modifier les schémas relationnels que l'on a créés. Ainsi il est possible d'ajouter de nouvelles tables, d'ajouter des attributs ou d'en modifier la définition, d'ajouter des contraintes ou d'en modifier, d'ajouter des index, etc.
Pour ajouter de nouvelles tables, il suffit d'utiliser des instructions suivant la syntaxe vue en III.
Pour supprimer une table, la syntaxe est :
DROP TABLE nom_table;
La syntaxe générale pour opérer des modifications sur le schéma relationnel est la suivante :
ALTER TABLE nom_table ACTION description;
avec ACTION : ADD ou MODIFY ou RENAME OR DROP
Question 20 : ajoutez les contraintes indiquées dans le TD 5 c'est-à-dire :
- Les salaires des employés sont supérieur ou égal au SMIC (on admet que tout employé travaille à temps complet et donc touche au moins le SMIC).
- Les secteurs auxquels appartiennent les ARCs sont pris parmi l'ensemble suivant : cancérologie, rhumatologie, cardiologie, dermatologie.
- Un employé doit avoir plus de 18 ans et moins de 65 ans.
Question 21 : Ajoutez également la contrainte qui permet d'imposer que le codeSociete de la table ARC fait référence à une société de la table SOCIETE.
Question 22 : certaines contraintes n'ont pas été nommées. Cela pose des problèmes de manipulation des contraintes et de compréhension des messages d'erreur produits par le SGBD, en particulier.
Essayez de renommer une des contraintes à laquelle il n'a pas été attribué un nom autre que celui donné en interne par le SGBD. Par exemple, renommer la contrainte de clé primaire sur la table société.
- Pour cela, il vous faut connaître le nom que le système a affecté à cette contrainte. Comment faites-vous pour récupérer ce nom ?
- Essayez ensuite de supprimer la contrainte. Que constatez-vous ?
- Désactivez les contraintes qui posent problème. La syntaxe de désactivation des contraintes est :
activation : alter table nom_table enable CONSTRAINT nom_contrainte;
desactivation : alter table nom_table disable CONSTRAINT nom_contrainte;
Modifiez la table SOCIETE de façon à recréer la clé primaire mais cette fois-ci en lui affectant
VII Interrogation de données
Quesrion 23 : Nous avons vu dans le TD 5 quelques requêtes que l'on pourrait poser à la base.
Essayez de poser les requêtes demandées :
- Donner les noms et matricules des employés de la société de code Siret 5.
- Donner les noms et matricules des employés de la société Bayer Pharma.
- Donner les noms et matricules des employés des sociétés Bayer Pharma, Pierre Fabre, Biogalénique et Sanofi Synthelabo.
- Lister les ARCs (noms et matricules) spécialisés en cardiologie.
- Lister les ARCs (noms et matricules) qui ont été embauchés entre 1990 et 1992.
- Donner la liste des pathologies triées par ordre alphabétique.
- Donner les sociétés dont le siège social se trouve à Paris.
- Donner le nombre de médicaments décrits dans la base de données.
- Donner pour chaque société le nombre de molécules actives mises au point.
10. Donner les sociétés qui ont mis au point au moins deux molécules actives.
11. Quelle est la société qui a le plus de principes actifs à son actif ?
12. Quelles sont les molécules actives qui ont un rôle thérapeutique dans l'hypertension artérielle ?
13. Combien de molécules actives ont un rôle dans l'hypertension artérielle?
14. Quels sont les médicaments qui sont impliqués dans le traitement de l'hypertension artérielle ?
15. Quelles sont les molécules qui ont un rôle thérapeutique dans au moins deux pathologies ?
16. Quelles sont les molécules qui agissent à la fois sur la grippe et sur le syndrome de Kawasaki ?
17. Quelles sont les pathologies qui peuvent être soulagées par plusieurs principes actifs ?
18. Quelles sont les molecules qui n'agissent que sur la grippe ?
19. Quelles sont les molécules qui agissent sur la grippe mais qui n'agissent pas sur l'hypertension artérielle ?
20. Quels sont les principes actifs qui agissent dans toutes les pathologies ?
Question 24 : Donnez la moyenne, le minimum et le maximum des salaires des PDG des sociétés