Université de Lorraine
FST, Département informatique
TD 2 : Requêtes en algèbre relationnelle et en SQL
Exercice 1 : Un système informatique a été mis en place pour gérer les commandes
des clients d'un magasin de type grande surface. Le schéma de la base de données
comporte quatre tables dont les schémas sont :
CLIENT ( num_client : entier long, nom : chaîne de 50 caractères,
prenom : chaîne de 50 caractères, code_postal : chaîne de 5
chiffres, telephone : chaîne de 10 chiffres)
COMMANDE (num_commande : entier long, date : date,
num_client : entier long, montant : réel,
état : {en_préparation, confirmée, livrée, payée})
ARTICLE ( code_article : entier long,
désignation : chaîne de 100 caractères,
rayon : chaîne de 100 caractères,
sous_rayon : chaîne de 100 caractères,
prix : réel )
LIGNE_COMMANDE (code_article : entier long,
num_commande : entier long,
quantité : entier)
I) Proposer une clé pour chaque relation. Identifier les clés étrangères.
II) Exprimer les requêtes suivantes en algèbre relationnelle (quand cela est
possible) puis en SQL :
1- Liste des articles du rayon "produits frais" de prix supérieur à 5 €
2- Désignation des articles et quantité de la commande en préparation pour
le client "dupont"
3- Clients n’ayant pas passé de commande les deux derniers mois.
4- Clients n’ayant pas commandé le produit référencé 141044252
5- Nombre d’articles du rayon "produits frais"
6- Coût moyen des articles du rayon "épicerie sucrée"
7- Numéro de téléphone des clients ayant commandé pour un total supérieur
à 10 000 €
8- Nom et adresse des clients ayant payé plus de 10 000 € au total
9- Calcul du montant d'une commande lors de sa confirmation (on a vérifié
que tous les produits étaient disponibles)
10- Calcul du chiffre d'affaires par produit
11- Classer les produits par ordre décroissant de leur chiffre d'affaires. Peut-
on n'afficher que les 100 meilleurs produits ?
12- Sélection des produits faisant plus de 1000 € de chiffre d'affaires.
13- Calculer les ventes par mois et par rayon.
14- Clients ayant commandé tous les articles du sous-rayon "céréales" du
rayon "épicerie sucrée" ?
Un extrait de la table ARTICLE
REFERENCE LIBELLE RAYON SOUS PRIX
RAYON TTC(€)
41044252 Barres céréales abricot x6 Epicerie Céréales 2.6
190g - TAILLEFINE DE Sucrée
LU
90 67492191 Tarama au saumon 100 g - Produits frais Produits de la 1.2
ODYSSEE mer
95 67493006 Batonnets saveur crabe x Produits frais Produits de la 1.3
8 150 g - ODYSSEE mer
67493010 Batonnets poisson saveur Produits frais Produits de la 1.5
crabe 200 g mer
67493025 Le Rapé de surimi 200 g - Produits frais Produits de la 2.2
ODYSSEE mer
99110101 Purée de tomates 700 g - Epicerie Salée Mayonnaises, 0.99
MUTTI ketchup,
sauce tomate
99110102 Purée de tomates basilic Epicerie Salée Mayonnaises, 1
720 g - MUTTI ketchup,
sauce tomate
99404002 Œufs Plein air x 12 calibre Produits frais Œufs 2.8
moyen 53/63 - VAL D
EURRE
Exercice 2 : Préparation au TP
Etant donné le schéma des tables HR (Human Resources) données ci-dessous et qui
seront utilisées en TP, écrire les requêtes SQL répondant aux questions suivantes :
a) Afficher les noms des employés dont le numéro du supérieur hiérarchique
(manager) est 114.
b) Nom et prénom du chef (manager) de chaque département.
c) Nombre de départements par pays (nom de pays et nombre de départements
pour ce pays).
d) Afficher les noms des employés dont le nom contient deux fois la lettre ‘a’.
Schémas des tables de HR (Document à avoir en TP)
Dans la version Oracle installée, il existe un utilisateur nommé HR propriétaire
d’un ensemble de tables accessibles à tout le monde dont les schémas sont les
suivants :
Table EMPLOYEES
Nom NULL ? Type
------------------ ------- -------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
Table COUNTRIES
Nom NULL ? Type
------------------ ------- -------------------
COUNTRY_ID NOT NULL CHAR(2)
COUNTRY_NAME VARCHAR2(40)
REGION_ID NUMBER
Table DEPARTMENTS
Nom NULL ? Type
------------------ ------- -------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
Table JOBS
Nom NULL ? Type
------------------ ------- -------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
Table JOB_HISTORY
Nom NULL ? Type
------------------ ------- -------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
START_DATE NOT NULL DATE
END_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)
Table LOCATIONS
Nom NULL ? Type
------------------ ------- -------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
Table REGIONS
Nom NULL ? Type
------------------ ------- -------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)
