Bases de Données
TP n°2 Langage SQL sous Oracle : Langage de Manipulation des Données
Interrogation des Données
Enseignant : Mme Ben Saïd Salma
Exercice 1
Soit le schéma relationnel de la base de données « pilotes-avions-vols ».
Pilote (numP, nomP, prenomP, ville, salaire)
Avion (numAv, typeAv, capacite, localisation)
Vol (numVol, # numP, # numAv, villeDep, villeAr)
Travail à faire
1- Créer une séquence sur le numéro du pilote commençant par 101 avec un pas de 2.
2- Créer une séquence sur le numéro de l’avion commençant par 1000 avec un pas de 2.
3- Créer une séquence sur le numéro du vol commençant par 712 avec un pas de 1.
4- Insérer des données suivantes dans les tables :
Pilote : (sq_Nump.Nextval, ‘Dalton’,’Jack’,’Californie’,6000)
(sq_Nump.Nextval, ‘Dalton’,’Micke’,’New York’,6200)
(sq_Nump.Nextval, ‘Driss’,’Sami’,’Paris’,6500)
(sq_Nump.Nextval, ‘Durand’,’Richard’,’Marseille’,6600)
(sq_Nump.Nextval, ‘Marchant’,’Bertrand’,’Paris’,6100)
Avion: (sq_NumAv.Nextval, ‘AirBus A320’,150,’Paris’)
(sq_NumAv.Nextval, ‘AirBus A340’,295,’Paris’)
(sq_NumAv.Nextval, ‘Boeing 737’,135,’New York’)
(sq_NumAv.Nextval, ‘Boeing 767’,350,’Californie’)
(sq_NumAv.Nextval, ‘Boeing 787’,250,’Californie’)
Insérer 4 tuples dans la table Vol.
5- Les pilotes qui touchent exactement 6000 € ont une augmentation de salaire de 10%, mettre
à jour la table correspondante.
6- Supprimer le vol numéro 712.
7- Ecrire les requêtes SQL permettant d'afficher :
a) Tous les vols, triés par ordre croissant du nom et décroissant du prénom.
b) Nom, prénom et salaire des pilotes dont le salaire est entre 6200 € et 6500 €.
c) Caractéristiques des avions localisés à Paris.
d) Caractéristiques (NumAv, TypeAv, Capacite, Localisation) des avions localisés dans
la même ville que le pilote 'sami'.
e) Caractéristiques (NumVol, VilleDep, VilleAr, TypeAv, Nomp) du vol numéro 714.
f) Numéro et type des avions affectés à des vols.
g) Nombre total de vols, utiliser un alias de colonne pour l’affichage.
h) Capacité moyenne des avions de type Boeing.
i) La liste des avions non affectés à des vols, de trois manières : en utilisant une requête
non synchrone, une requête synchrone et une soustraction.
j) La liste des prénoms et des noms des pilotes dans une même colonne d’affichage
(attribuer un nom à cette colonne), en commençant par le prénom avec le premier
caractère en majuscule puis un espace puis le nom tout en majuscule. (opérateur de
concaténation || , INITCAP(chaine), UPPER(chaine))
k) Le pilote qui a effectué le plus grand nombre de vols.
Page 1 /3
Exercice 2
Soit le schéma relationnel de la base de données «Etudiant_Enseignant_Matiere_Evaluation» :
Etudiant (numEtu, nomEtu, prenomEtu, age, genre)
Enseignant (numEns, nomEns, prenomEns, grade)
Matiere (numMat, nomMat, coefficient, #numEns)
Evaluation (#numEtu, #numMat, note)
Travail à faire
1- Insérer des données exploitables dans chacune des tables.
2- Ecrire les requêtes SQL permettant d'afficher :
a) la liste de tous les étudiants triés par ordre croissant du nom et croissant du prénom.
b) la liste des couples de noms matières qui ont le même coefficient et afficher le
coefficient.
c) La liste de tous les enseignants sans exceptions et les noms des matières qu’ils
enseignent. (en tenant compte des enseignants non affectés à des matières).
d) la liste des matières enseignées par enseignant.
e) le nombre d'enseignants par grade
f) la moyenne, par étudiant, arrondie à deux chiffres après la virgule [ROUND(…, 2)] .
Définir un alias de colonne.
g) La proportion des étudiants ayant une moyenne supérieure à 10.
Exercice 3
Soit le schéma relationnel suivant :
Employe ( idEmp, nom, d_Nais, adresse, grade, salaire, #idSup, #numDept)
Departement (numDept, nomDept, # idResp)
Travail à faire
1- Insérer des données dans chacune des tables.
2- Ecrire les requêtes permettant d'afficher :
a) le nom et le grade des employés du département 31 qui ont un grade que l'on ne
trouve pas dans le département 32.
b) le nom, le salaire et le numéro de département des employés qui gagnent plus qu'au
moins un employé du département 31, classés par numéro de département et salaire.
c) le nom, le salaire et le numéro de département des employés qui gagnent plus que
tous les employés du département 31, classés par numéro de département et salaire.
d) le nom de chaque employé et le nom de son supérieur. Ceux qui n'ont pas de chef
doivent quand même être affichés.
Page 2 /3
Exercice 4
Soit le schéma relationnel suivant :
Machine (numMachine, designation, dat_service, prix)
DemandePR (numDemande, dat_demande, observation, #code_machine)
PièceRech ( numPiece, designation, prix)
LigneDemande(# numDmde, #numPiece, quantite_demandee)
Travail à faire
1. Insérer des données dans chacune des tables.
2. Ecrire des requêtes SQL permettant de :
a) Créer une vue relative à toutes les informations d’entretien d’une machine suivant cet
affichage :
machineNum demandeNum datDde pieceNum pieceDesg qteDm
… … … … … …
b) Afficher, les couples de machines (leurs numéros) qui ont été mises en service le même jour
et qui ont nécessité un entretien à la même date, et afficher ces dates.
c) Afficher les machines qui n’ont nécessité aucun entretien, de deux manières différentes dont
l’une en utilisant l’opérateur EXISTS ou NOT EXISTS.
d) Afficher les machines qui ont nécessité un nombre de pièces de rechanges différentes
supérieur à 3 pour tous les entretiens qui lui ont été effectués.
e) Calculer la proportion de machines entretenues durant le dernier mois, par rapport au
nombre total de machines entretenues.
f) Mettre à jour la requête de la question a) de façon à afficher toutes machines sans exception
et toutes les informations relatives à leurs entretiens (les machines qui n’ont jamais été
entretenues doivent être affichées).
Page 3 /3