Travaux PratiquesBD

Bases de données
Page 1
Travaux Pratiques
TP°2 : Manipulation et développement des BDR
(SQL)
Exercice 1 :
On considère une base de données relative à une école. Dans cette base, les élèves sont
affectés à des classes. De même, les professeurs enseignent suivant un certain nombre
d'heures par semaine. Le modèle conceptuel dans le formalisme entité-association est
donné ci-dessous :
Avec
numE : numéro d'élève (unique)
nomE : nom d'élève. Non nul
nomC : nom de classe (unique)
numP : numéro de professeur (unique)
nomP : nom de professeur. Non nul
nbh : nombre d'heures effectuées par un professeur
dans une classe. Le nombre d’heures doit être positif
et inférieur à 72. Par défaut, égal à 0
Ce MCD est traduit en un schéma relationnel en appliquant les règles usuelles :
ELEVE (#numE, nomE, #nomC)
CLASSE (#nomC)
PROFESSEUR (#numP, nomP)
SERVICE (#numP, #nomC, nbh)
1 Créer les quatre tables,
2 Utilisant des requêtes SQL :
a) Insérer des enregistrements dans les tables comme montré sur la figure en bas,
b) Afficher la liste des élèves de la classe 'CM1'
c) Modifier le nom du professeur identifié par le numP=6 à « DUVALLE »
d) Modifier le nom et la classe de l’élève dont numE=11 en respectivement
« FIARD » et « CE1 »
e) Supprimer l’élève dont numE=22
f) Supprimer tous les élèves de la classe « CE2 »
g) Donner la liste des élèves qui ont 'DUBALAI' comme professeur en utilisant la
jointure
h) Donner le nombre d'heures effectuées par le professeur 'DUBALAI' en utilisant la
jointure

Bases de données
Page 2
Exercice 2 :
On considère une base de données constituée des deux tables suivantes :
Table : Employes
no
<pk>
NUMBER(7)
not null
nom
VARCHAR2(25)
not null
prenom
VARCHAR2(25)
null
Profession
VARCHAR2(25)
null
dt_embauche
DATE
null
salaire
NUMBER(11,2)
null
commission
NUMBER(11,2)
null
no_departement
<fk>
NUMBER(7)
not null
Table : Departements
no
<pk>
NUMBER(7)
not null
depart_nom
VARCHAR2(25)
not null
directeur
<fk>
NUMBER(7)
not null
ville
VARCHAR2(25)
not null
Remplissez les tables comme suit :
Donner les requêtes pour :
1 Faire le produit cartésien entre Employés et Départements

Bases de données
Page 3
2 Faire la jointure entre Employés et Départements
3 Donnez les noms des employés en majuscule et les noms de leur département en
minuscule. Utiliser les fonctions UPPER et LOWER
4 Donnez la moyenne des salaires des employés. Utiliser la fonction MEAN
5 Donnez le salaire minimal des employés. Utiliser la fonction MIN
6 Donnez le salaire maximal des employés. Utiliser la fonction MAX
7 Augmenter les salaires des employés par 10%
8 Supprimer les employés dont le nom contient « J » et qui travaillant à Boston
Exercice 3:
On considère une base de données pour la gestion d’un entrepôt constituée des tables
suivantes :
Table : e_emp
no
<pk>
NUMBER(7)
not null
nom
VARCHAR2(25)
not null
prenom
VARCHAR2(25)
null
utilisateur
VARCHAR2(8)
null
dt_entree
DATE
null
commentaires
VARCHAR2(255)
null
superieur_no
<fk>
NUMBER(7)
null
titre
VARCHAR2(25)
null
service_no
<fk>
NUMBER(7)
null
salaire
NUMBER(11,2)
null
pct_commission
NUMBER(4,2)
null
Table : e_service
no
<pk>
NUMBER(7)
not null
nom
VARCHAR2(25)
not null
continent_no
<fk>
NUMBER(7)
null
Table : e_continent
no
<pk>
NUMBER(7)
not null
nom
VARCHAR2(50)
not null
Table : e_produit
no
<pk>
NUMBER(7)
not null
nom
VARCHAR2 (50)
not null
description
VARCHAR2 (255)
null
prix_conseille
NUMBER(10,2)
null
Table : e_ligne
ligne_no
<pk>
NUMBER (7)
not null
produit_no
<fk>
NUMBER (7)
not null
prix
NUMBER(10,2)
null
quantite
NUMBER(9)
null
qte_livree
NUMBER(9)
null
commande_no
NUMBER(7)
not null

Bases de données
Page 4
Donner l’équivalent en SQL pour ;
1 Créer les tables ci-dessus
2 Donner la liste des services dont le numéro est supérieur à 45.
3 Donner la liste des employés dont le nom commence par "M".
4 Donner la liste des employés n’ayant pas de commission.
5 Donner la liste des employés qui ont les fonctions suivantes : Magasinier, Chef
d’entrepôt.
6 Donner la liste des employés gagnant entre 1400 et 2000 et ayant intégré la société
après le 20 janvier 1992.
7 Donner la liste des employés dont la commission est supérieure à 200, classés dans
l’ordre croissant des commissions.
8 Donner la liste des employés triés par titre et pour chaque titre triés par salaire
décroissant.
9 Donner la liste des employés et des services dans lesquels ils exercent.
10 Donner la liste des numéros de produits qui n’ont jamais été commandés.
11 Donner la liste des employés travaillant en Europe et ayant le même titre que SMITH.
12 Donner la liste des employés (nom, prénom, date d’entrée, salaire et numéro de
service) travaillant dans un service qui contient au moins un représentant Commercial.
13 Afficher les informations du service ayant le plus d’employés : son numéro, son nom,
le nom du continent où il se trouve et le nombre d’employés y travaillant.