TRAVAUX PRATIQUES : SQL SOUS ORACLE - exos pl sql oracle

Travaux pratiques : SQL sous Oracle

Attention :

Ce TP de manipulation se compose de deux parties.

La première partie s’effectue sur papier et à l’écran en environ 1h30. Elle concerne la définition en SQL de tables relationnelles, et des manipulations possibles sur le schéma de données.

Dans la seconde partie, Il vous sera founit, au bout d'1h30 de travail, des fichiers à télécharger afin d'homogénéiser les groupes. Ces fichiers contiennent les définitions des tables et des données insérées. Cela fera office de corrigé (c'est pour cela que nous vous demandons une première copie au bout d'1h30). Cette seconde partie concerne l'interrogation de ces données. Vous aurez un ensemble de requêtes écritent en français qu'il faudra traduire en SQL.

Le rapport est un ensemble commenté des questions de chaques parties. C'est à dire que nous voulons les requêtes à définir, ainsi qu'un commentaire pour chacun (même succint). Si des questions de cours sont demandées, veuillez reporter la question, et votre réponse commentée.

Pour vous aider, vous trouverez la syntaxe pour SQL sur internet. Toute commande SQL se termine par un ';'. Par exemple, vous pourrez consulter la syntaxe sur : http://wwwlsi.supelec.fr/www/yb/poly_bd/sql/poly_59.html.

PRELIMINAIRE  :

Connectez-vous sur jungle et tapez : jungle$  . /usr/local/oracle/etc/oracle-env (attention au <point> <espace> au début)

Démarrez SQL*Plus par la commande : jungle$  sqlplus / (attention sqlplus <espace> <slash>). Vous êtes prêts à travailler.

PARTIE 1 : Définition de données

Le langage SQL offre un certain nombre de commandes dites de définition qui permettent de définir et gérer une base de données. Cette première partie a pour objectif de présenter et manipuler cet ensemble de commandes. Pour cela, un ensemble de directives vous est proposé.

Dans le cadre de notre TP, nous considérerons une base données gérant l'aéroport Charles De Gaulle. 

Cette Base de Données contient:

•la liste du personnel (pilote/hotesse/stewart)

•la liste des destinations : villes d'europe, avec distance et décalage horaire

•la liste des compagnie de vol

•la liste des avions (type/compagnie/vitesse/capacité/équipage nécessaire)

•la liste des vols (avion/destination/jour) : pour plus de facilité, nous n'avons considérons que 3 jours de vols. Soit 40 vols différents

•la liste des réservations (vol/nom/prix) : le nombre de réservations possible en 40 vols différents en fonction de la capacité de chaque avion donnant des chiffres énormes (de l'ordre de 6000 reservations), nous ne considérerons que 1124 réservations réparties dans les vols (soit 1/6°)

•La liste des équipages par vol : la répartition de l'équipage à été faite équitablement en fonction de la capacité d'équipage de chaque avion. (min 2 pilotes - 3 pour une capacité de 11 -, le reste en stewart et hotesse)

.
Voici le schéma correspondant de votre base :

Personnel(PersonnelId, Nom, Naissance, HeureDeVol, Type)

Destination(DestinationId, Ville, Pays, Distance)

Compagnie(CompagnieId, Nom)

Avion(AvionId, CompagnieId, Nom, Vitesse, Equipage)

Vol(VolId, AvionId, DestinationId, Depart (date))

Reservation(ReservationId, VolId, Nom, Prix, Reduction)

Equipage(VolId, PersonnelId)

Tableau 1 : Base Aeroport

1.Création des relations   :   

a)  Créer quatre des septs relations de la base ci-dessus :

Il vous est demandé de créer les relations suivantes :

•Personnel

•Equipages

•Avion

•Vol

•Destination

Les types disponibles sont :   number(n), char(n), date, number(m,n)

Il ne vous est pas demandé de mettre les clés primaires, les indexes, ni les clés étrangères.

b) Vérifiez vos créations :

DESC <nom_table>

2.Modification du schéma de la base de données

a)  Modifier le type des attributs

:

Il vous est demandé de modifier les types précédemment définis : Vitesse devient number(6) , Nom devient char(10)

Peut-on  changer un type en diminuant sa taille ?

b)  Ajouter un attribut à une relation :

En particulier, il vous est demandé:

(i)d'ajouter un attribut Capacite  pour les avions;

(ii)d'ajouter l'attribut Decalage pour les destinations (correspondant au décalage horaire avec la france.

c) Problème des  Valeurs Nulles :  not null/ null

Un attribut d'une table déclaré "not null" doit nécessairement être renseigné lors de l'insertion d'un tuple. Il vous est demandé d'identifier les attributs répondant à ce critère et de modifier votre schéma en conséquence. Utiliser la commande ALTER TABLE.
 

3.DEFINITION DES CLéS  :

1.Clés primaires :

Chacune des relations de votre base de données doit contenir des informations définies de façon unique. A cette fin, les clés primaires doivent être définies.

Définissez les clés primaires des relations de la question 1.a.

2.Clés étrangères :

Des attributs d'une relation font parfois référence à une valeur particulière d'une autre relation, nous appelons ces attributs des clés étrangères. (ex : l'identifiant d'un vol contient l'identifiant de l'avion qui est une clé étrangère pour la relation vol, vers la relation Avion). Précisez pour l'ensemble des relations de la question 1.a. les clés étrangères possibles.

4.Insertion de données

Il vous est demandé d'insérer un jeu de données cohérent dans vos relations (un ou deux tuples par relation)

Exemple de données insérées dans la relation Avion: (1, 1, 'A320', 150, 864, 7);

Est-il possible d'ajouter un attribut à une relation comprenant déjà des données? Si oui quelles sont les conditions à respecter?

Est-il possible de changer la taille d'un attribut d'une relation contenant des données? Si oui, quelles sont les conditions à respecter?

5.Mise à jour de Relations

Il vous est demandé de :

·mettre en majuscule les noms des Avions (fonction UPPER)

·mettre en minuscule les noms des villes Destination de plus de 1000 kms de distance (fonction LOWER)

·ajouter 1 heure de décalage aux Destination non francaises.

5. Suppression de données

Il vous est demandé de :

(i)supprimer les avions dont l'équipage est de 5 personnes;

(ii)supprimer les vols dont la date est inférieur au 1 janvier 2005.

6. Chargement massif des données

Afin d'éviter l'insertion "un à la fois" des tuples dans les relations, nous allons utiliser la commande de chargement à partir d'un fichier.

Pour ce faire, créer un fichier nomfichier.ctl qui contient les définitions suivantes:

LOAD DATA

INFILE *

APPEND                                              

INTO TABLE  NomTable

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’       [ATTENTION au " et au '  après le BY, c'est ‘ puis “ puis ‘]

(NomAtt1, NomAtt2, ..., NomAttn)

BEGINDATA

1,"AAAA",......,"BBBB "

2,"CCCC", .....,"DDDD"

Attention, vous ne pouvez pas mettre de valeurs nulles dans ce type de fichier. Tous les champs doivent être renseignés.

Puis taper la commande suivante :  sqlldr userid=/ control = fichier.ctl log = fichier.log

par ex : sqlldr userid=/ control=aeroport.ctl log=aeroport.log

c) Visualiser le résultat du chargement en consultant le fichier  .log

par ex:   > more  aeroport.log

d) Vérifier sous SQL*Plus, que vos données ont bien été insérées dans la table désirée.

sqlplus /

select * from Avion;

e) Procéder à un chargement plus massif de toutes vos tables.
______________________________________________________________________

ANNEXE

SQL*PLUS est l'interface de base d'Oracle. Elle est composée des commandes de SQL et de SQL*PLUS. Les commandes SQL permettent de définir et manipuler les données. Les commandes SQL*PLus permettent de formater les résultats, établir des options, éditer et stocker les commandes SQL, …

Commandes SQL*PLUS

SET pause ON/OFF  :  permet d'effectuer un défilement page par page de l'écran.

SET LINESIZE 132  : permet d'augmenter le nombre de caractères par ligne

SET PAGESIZE 60 : permet d'augmenter le nombre de lignes par page

SET TIMING ON   : donne le temps de calcul de chaque commande SQL

La dernière commande SQL exécutée est stockée dans le buffer principal de l'environnement SQL*PLUS. Plusieurs commandes de manipulation de ce buffer sont possibles :

List             :  affiche le contenu du buffer.

List n      :  affiche la n-ième ligne du buffer.

List m n      :  affiche les lignes m à n du buffer.

Save <nom de fichier>  :  sauve le buffer dans un fichier.

Get <nom de fichier>    :  restitue le fichier dans le buffer.

Start <nom de fichier>  : exécute le contenu du fichier

Run  :  exécute le contenu du buffer.

Change /<chaine>/<new_chaine>/  :  remplace une chaîne du buffer par une autre.

Clear buffer : vide le contenu du buffer

Commandes SQL

COMMIT  :  permet d'enregistrer les relations dans la base.

ROLLBACK  :  permet de défaire toutes les opérations effectuées depuis le dernier commit.

Les Types de oracle

Char(n)  : chaine de n caractères (n≤240)

Number  : nombre

Number(w,d)  :  nombre de longeur w avec d chiffre décimaux

Long  :  longue chaine de caractère ≤65 535

Date :

Integer

Float 

PARTIE 2 :

Manipulation des données

Tout d'abord, téléchargez les relations et les données sur le site qui vous sera donné au tableau. Exécutez les fichiers afin d'obtenir la base de données adéquates.

Pour chacune des requêtes suivantes, écrivez la requête en SQL correspondante. Nous vous demandons de noter la requete SQL, d'expliquer la requête et de noter les résultats obtenues lors de l'éxécution.

1-Donnez la vitesse d'un A320.

2-Donnez les noms de tous les pilotes.

3-Déterminez tous les pays différents existant dans la base.

4-Donnez la date de naissance de MICHEL.

5-Donnez toutes les destinations vers lesquelles à volé MICHEL, classées par ordre alphabétique.

6-Donnez tous types d'avions effectuant la liaison avec Londres, classé du plus rapide au plus lent

7-Donnez la liste des hôtesses avec qui COHEN a volé, classée par age.

8-Combien de passagers différents HUBERT va-t-il transporter entre le 10 et le 13 août?

9-Donnez pour chaque hôtesse le nombre de passagers avec qui elle aura volé pendant la journée du 12 août.

10-Parmi tous les vols effectués le 10 août, donnez celui dont la moyenne d'age de l'équipage est la plus basse.

11-Donnez le nom de toutes les compagnies possédant au moins un appareil de même type que la compagnie d'identifiant 3.

12-Donnez pour chaque compagnie la liste des avions qu'elle possède, classée par ordre de capacité croissante.

13-Donnez la liste des personnes prenant un avion vers une destination ayant un décalage horaire de -1, et ne possédant pas de réduction.

14-Classez les destinations vers lesquelles a volé COHEN selon le nombre de gens qu'il a transporté la bas.

15-Donnez le nom du pilote ayant piloté ,entre le 10 et le 13 aout, l'avion le plus rapide.

16-Donnez toutes les hôtesses plus expérimentées (cad avec plus d’heures de vol) que SOPHIE.

17-Donnez le nom de toutes les personnes ayant une réservation sur un vol où l’employé le moins expérimenté (cad avec le moins d’heures de vol) participe.

18-Donnez pour chaque hôtesse la liste des pilotes avec lesquels elle a volé, classée par nombre d'heures de vol.

19-Donnez toutes les destinations vers lesquelles SOPHIE a voyagé en étant la personne de l’équipage la plus jeune.