Bases de données TP No 2 SQL_PLUS d’Oracle Université de Provence Licence d’Informatique Troisième année Il est conseillé, pour ce TP, de travailler le plus possible par l’intermédiaire de plusieurs fichiers contenant les commandes SQL_PLUS. Il est conseillé de nommer ces fichiers de façon significative, avec une extension .sql. Ces fichiers permettront de travailler selon vos besoins en dehors des séances de TP dans les salles en accès libre. Attention : n’oubliez pas de vous connecter à capucine via la commande ssh capucine afin de pouvoir utiliser SQL_PLUS. 1) Création de tables Créer les tables correspondant aux relations données ci-dessous (la détermination des types de données adéquats est à votre charge, mais, on considère que les heures de départ et d’arrivées sont sans les minutes). On n’oubliera pas de créer les clés primaires et les clés étrangères (respectivement via les contraintes PRIMARY KEY (cidessous en caractères soulignés) et FOREIGN KEY … REFERENCES (ci-dessous avec un caractère #). Ville(numville,nomville,codepostal) Train(numtrain,#villedepart,#villearrivee,heuredepart,heurearrivee,jour) Passager(numpassager,nompass,age,#reftrain) 2) Création de données Créer un ensemble de données correspondant au schéma précédent (une dizaine de villes, une quinzaine de trains, et une trentaine de passagers). Pour créer les valeurs de la colonne jour, il faut utiliser la fonction TO_DATE(’val’,’fmt’) où fmt indique le format d’entrée de la date tel que considéré dans val. 3) Modification de tables et de données On étend la relation Passager en lui rajoutant une colonne : Passager(numpassager,nompass,age,#reftrain,categorie) Répercuter cette modification sur la table Passager correspondante (commande ALTER TABLE). Répercuter cette modification sur les données (commande UPDATE … SET) en considérant les étapes suivantes : − Déterminer et exécuter une commande SQL_PLUS permettant de compléter automatiquement tous les passagers de moins de 18 ans et ceux de plus de 60 ans en leur attribuant la catégorie 2. − De même, déterminer et exécuter une commande SQL_PLUS permettant de compléter automatiquement tous les passagers dont le nom commence par ‘R’ ou ‘C’ en leur attribuant la catégorie 1 (cf fonctions SQL sur les chaînes). − Pour tous les passagers restants (ie dont la valeur est NULL), déterminer et exécuter une commande SQL_PLUS leur attribuant la catégorie 3. 4) Ajout de tables − Ajouter les tables correspondantes aux relations ci-dessous dans la base de données : outre les contraintes de clés, certaines contraintes dites d’intégrité peuvent être écrites via la clause CONSTRAINT (par exemple, la distance entre deux villes ne peut pas être négative). Spécifier certaines contraintes sur ces tables ; modifier les tables précédentes pour leur ajouter de telles contraintes d’intégrité. Tarif(#train,categorie,prix) Distance(#ville1,#ville2,nbkm) − − Insérer dans la base de données quelques nouveaux tuples pour instancier ces deux tables. Observer le comportement de SQL_PLUS lorsque des tuples violant les contraintes d’intégrité sont définis. Ajouter la table correspondant à la relation Categorie(numcat,intitule) dans la base de données, et enfin trois tuples de cette table pour les catégories 1, 2 et 3. Comment répercuter intelligemment cet ajout de table sur les définitions des tables Passager et Tarif ? Page 1 sur 3 5) Premières requêtes Ecrire des requêtes SQL simples pour avoir l’affichage, sous l’éditeur, des interrogations suivantes : 1. Lister tous les trains. 2. Lister les noms de villes dont le code postal commence par 13. 3. Liste tous les trains partant avant midi. 4. Lister toutes les distances entre villes. 5. Lister toutes les distances entre villes de plus de 60 km. 6. Lister le numéro et la durée de tous les trains qui roulent pendant plus de 5h. 7. Lister le jour de circulation des trains partant avant 10h. 8. Lister l’intitulé de toutes les catégories. 6) Annexes – Aide sur la manipulation des dates Les dates sont manipulables en tant qu’éléments d’un ordre total. Une date est la donnée du jour, mois, année, heures, minutes et secondes. L’affichage et la lecture des dates passent par deux fonctions de conversion incontournables, TO_CHAR et TO_DATE, pour lesquelles le format de dates doit être précisé sous forme d’une chaîne de caractères entre quotes. La fonction SYSDATE, sans argument, renvoie la date et l’heure courante. Cette fonction ne peut pas être utilisée dans une contrainte de type CHECK. Les formats de date (à utiliser dans les fonctions de conversion) Le format de date par défaut est celui spécifié explicitement par le paramètre NLS_DATE_FORMAT, ou implicitement par le paramètre NLS_TERRITORY. Ces paramètres peuvent être modifiés en utilisant la commande ALTER SESSION. Un format de date est défini par la conjonction de plusieurs éléments au sein d’une chaîne de caractères. La longueur totale d’un format de date est de 22 caractères. Les éléments de format sont nombreux. En voici quelques uns. Elément / , . ; : ″texte″ BC B.C. AD A.D. DAY DDD HH HH24 MM MONTH RM SS WW . YYYY SYYYY Signification Ponctuations et textes qui sont reproduits tels quels dans le résultat Elément AM A.M. D Indique l’ère Nom du jour, sur 9 caractères (remplissage des espaces) Jour dans l’année (de 1 à 366) Heure du jour (de 1 à 12) Heure du jour (de 1 à 23) Mois (de 01 à 12) Nom du mois, sur 9 caractères (remplissage des espaces) Mois romain (de I à XII) Secondes (de 0 à 59) Semaine de l’année (de 1 à 53). La première semaine commence au premier jour de l’année et se termine 7 jours après. Ne pas utiliser avec la fonction TO_DATE. L’année sur quatre caractères. L’indicateur S permet de préfixer les dates avant J. Christ par le signe ‘-‘. Signification Indicateur de méridien Jour de la semaine (de 1 à 7) DD Jour dans le mois (de 01 à 31) DY HH12 MI MON PM P.M. RR Abréviation du nom du jour Heure du jour (de 1 à 12) Minutes (de 0 à 59) Abréviation du nom du mois Indicateur de méridien SSSSS Y,YYY YYY YY Y Page 2 sur 3 Arrondi de l’année sur deux caractères au siècle le plus proche, selon si l’année dans le siècle est <50 ou non. Secondes après minuit (de 0 à 86399) Année avec une virgule après le millénaire Les trois (deux, un) derniers caractères de l’année. Si l’on veut des minuscules dans les formats de dates « épelées », il suffit que l’élément indicateur soit en minuscules : par exemple, pour un lundi, l’indicateur ‘DAY’ produit ‘LUNDI’, l’indicateur ‘Day’ produit ‘Lundi’, et l’indicateur ‘day’ produit ‘lundi’. Il est possible d’ajouter des suffixes aux éléments de formats : TH, SP et SPTH. Par exemple, DDTH produit pour le quatrième jour ‘4TH’, DDSP produit ‘FOUR’, et SPTH produit ‘FOURTH’. Avec ces suffixes, le résultat est toujours en anglais. Par exemple, TO_CHAR(SYSDATE,‘″Nous sommes le ″Day DD Month″ en ″Y,YYY.’) produira une chaîne de caractère représentant la date courante avec le format spécifié suivant : Nous sommes le Lundi 22 Octobre en 2,001. Page 3 sur 3