Bases de données TP No 2 SQL_PLUS d`Oracle Page 1 sur 3 1

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