Bases de données 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 Page 1 Bases de données Exercice 2 : On considère une base de données constituée des deux tables suivantes : no nom prenom Profession dt_embauche salaire commission no_departement no depart_nom directeur ville <pk> <fk> <pk> <fk> Table : Employes NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(25) DATE NUMBER(11,2) NUMBER(11,2) NUMBER(7) not null not null null null null null null not null Table : Departements NUMBER(7) VARCHAR2(25) NUMBER(7) VARCHAR2(25) not null not null not null not null Remplissez les tables comme suit : Donner les requêtes pour : 1 Faire le produit cartésien entre Employés et Départements Page 2 Bases de données 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 : no nom prenom utilisateur dt_entree commentaires superieur_no titre service_no salaire pct_commission no nom continent_no <pk> <fk> <fk> <pk> <fk> no nom <pk> no nom description prix_conseille <pk> ligne_no produit_no prix quantite qte_livree commande_no <pk> <fk> Table : e_emp NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(8) DATE VARCHAR2(255) NUMBER(7) VARCHAR2(25) NUMBER(7) NUMBER(11,2) NUMBER(4,2) not null not null null null null null null null null null null Table : e_service NUMBER(7) VARCHAR2(25) NUMBER(7) not null not null null Table : e_continent NUMBER(7) VARCHAR2(50) not null not null Table : e_produit NUMBER(7) VARCHAR2 (50) VARCHAR2 (255) NUMBER(10,2) Table : e_ligne NUMBER (7) NUMBER (7) NUMBER(10,2) NUMBER(9) NUMBER(9) NUMBER(7) not null not null null null not null not null null null null not null Page 3 Bases de données 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. Page 4
