27 septembre 2024 Interrogation des bases des données
TD 3
Requêtes
Exercice 1. Les commandes d’un bar
Soit le modèle relationnel suivant :
SERVEUR (idServeur, nom, prénom)
COMMANDE (idCommande, numTable, date, heure, #idServeur)
BOISSON (idBoisson, libellé, prix, type)
CONTENIR (#idCommande, #idBoisson, quantité)
Voici le contenu de la base de données correspondante :
idBoisson libellé prix type
1 Café Expresso 2.00 Café
2 Café déca 1.50 Café
3 Limonade 2.50 Soda
4 Cola 3.00 Soda
5 Café au lait 2.00 Café
6 Café viennois 2.50 Café
7 Thé au citron 1.00 Thé
8 Bloody Mary 5.00 Cocktail
9 Mojito 4.00 Cocktail
10 Menthe à l’eau 1.50 Autre
Table 1 – table Boisson
idServeur nom prenom
1 Bonbeur Jean
2 Zétofré Mélanie
3 Aba Bart
4 Dubonnet Jean
5 Kiébon Luis
Table 2 – table Serveur
idCommande numTable date heure idServeur
1 4 2015-01-31 13 :00 :00 2
2 3 2015-01-31 10 :30 :00 3
3 5 2015-01-31 11 :15 :00 2
4 4 2015-03-07 13 :05 :00 1
5 4 2015-03-07 16 :00 :00 3
6 7 0000-00-00 NULL 3
Table 3 – table Commande
Licence Informatique, 2ème année - Y. Sam 1/4
27 septembre 2024 Interrogation des bases des données
idCommande idBoisson quantité
1 1 2
1 3 1
2 2 1
3 2 1
3 3 2
3 6 3
4 5 1
5 3 1
5 7 1
6 4 3
Table 4 – table Contenir
1. Requêtes sur une seule table à la fois
Ecrire les requêtes qui répondront aux questions ci-dessous
(a) Liste des numéros de table qui ont eues au moins une commande (attention, il y a plusieurs com-
mandes pour une même table). Quel serait le résultat ?
(b) Nombre de tables sur lesquelles ont été servies au moins une commande. Résultat ?
(c) Prix moyen des boissons que propose le bar.
(d) Libellés des boissons par ordre alphabétique.
(e) Prix de la boisson la moins chère et de la boisson la plus chère servies dans ce bar. Résultat ?
(f) Toutes les infos sur les boissons de type "café" dont le prix est inférieur à 2 euros. Résultat ?
(g) Nombre total de commandes prises le 31/01/2015. Résultat ?
(h) Liste des boissons (libellé) qui coûtent entre 2 et 5 euros dont le nom commence par T ou C et qui
ne sont pas des cocktails, sodas ou autres. Résultat ?
2. Requêtes sur plusieurs tables
Ecrire les requêtes qui répondront aux questions ci-dessous.
IMPORTANT !
JOIN = opérateur de jointure normalisé Sql2 à utiliser le plus souvent possible !
Pourquoi ? Car les jointures écrites dans la partie WHERE ne permettent pas de faire la distinction entre la
sélection de lignes (filtrage de certaines lignes) et la jointure.
— Lorsqu’on met une condition, ex : WHERE prix >= 2, on filtre : on élimine certaines lignes !
— Lorsqu’on écrit, ex : WHERE P.idProduit= T.idProduit , on joint 2 tables P et T mais le SGBD
croit qu’il s’agit d’un filtrage donc il fait les calculs comme pour ceux d’un filtrage
Solution ? Avec JOIN, on sépare le filtrage de la jointure
— on isole chaque condition de jointures entre chaque couple de table.
— l’exécution est plus rapide car le SGBD utilise des optimisations pour faire les jointures JOIN.
Licence Informatique, 2ème année - Y. Sam 2/4
27 septembre 2024 Interrogation des bases des données
Rappel : Jointure interne
On peut utiliser la jointure interne pour traduire la jointure naturelle !
— INNER JOIN est la jointure par défaut si on ne précise pas le type de jointure !
SELECT . . .
FROM t a b l e 1 INNER JOIN t a b l e 2
ON t a b l e 1 . i d T a b l e 1 = t a b l e 2 . i d T a b l e 2
(a) Liste des commandes avec le nom du serveur qui a pris chaque commande (idCommande, numTable,
nom, prénom du serveur). Résultat ?
(b) Liste des serveurs (nom, prénom par ordre alphabétique) qui ont servi la table 4.
Rappel : Jointure externe
Utile pour obtenir toutes les informations d’une table, même si des lignes de cette table ne sont pas renseignées
dans les différentes tables jointes.
SELECT . . .
FROM t a b l e G a u c h e [ LEFT | RIGHT | FULL ] OUTER JOIN t a b l e D r o i t e
ON c o n d i t i o n _ j o i n t u r e
On recherche toutes les valeurs satisfaisant la condition de jointure précisée, puis on rajoute toutes les lignes
de la table jointe (selon LEFT, RIGHT) ou des tables jointes (FULL)
— LEFT : si on veut afficher toutes les lignes de la tableGauche (à gauche de JOIN) même s’il n’y a pas
de valeur correspondante dans la tableDroite
— RIGHT : si on veut afficher toutes les lignes de la tableDroite (à droite de JOIN) même s’il n’y a pas
de valeur correspondante dans la tableGauche
— FULL : si on veut afficher toutes les lignes des tableGauche et tableDroite même s’il n’y a pas de
valeur correspondante.
Différence jointure externe / jointure interne
L’hypothèse du monde clos : on considére que l’absence d’information, n’est pas une information.
La jointure externe permet de contrer l’hypothèse du monde clos en considérant qu’en cas d’absence de jointure
entre une table et l’autre, on ne supprime par pour autant l’information.
(c) Liste de tous les serveurs et des commandes qu’ils ont prises ou pas (nom, prénom, idcommande).
Résultat ? Quel aurait été le résultat avec INNER JOIN ?
(d) Liste des boissons (libellé et quantité) commandées dans la commande 3. INNER JOIN ? OUTER
JOIN ? Résultat ?
(e) Liste de toutes les boissons dont le prix est supérieur ou égal à 3 euros avec leurs numéros de
commande s’il y en a un (libellé, idcommande). INNER JOIN ? OUTER JOIN ? Résultat ?
3. Groupons un peu !
(a) Nombre de commandes par jour (date, nombre de commandes). Résultats ?
(b) Prix moyen (type, prix moyen) de chaque type de boissons. Résultats ?
(c) Nombre de commandes servies par tables (numéro de table et nombre de commandes) en affichant
les tables les plus utilisées en premier. Résultats ?
Licence Informatique, 2ème année - Y. Sam 3/4
27 septembre 2024 Interrogation des bases des données
(d) Numéro des tables où ont été servies plus de 2 commandes (numéro de table et nombre de com-
mandes). Résultats ?
(e) Pour chaque commande, donnez le nombre de verres de boissons commandées (idCommande, nombre
de verres). Résultats ?
(f) Calculez le montant de chaque commande (idCommande, numTable, montant). Résultat obtenu ?
(g) Liste de tous les serveurs et nombre de commandes qu’ils ont prises (nom, nbre commande). Résultat ?
(h) Liste de toutes les boissons avec les quantités totales vendues (libellé, qté totale). Résultat ? Que
faudrait-il ajouter à cette requête pour avoir les boissons vendues dont la quantité vendue est infé-
rieure à 2 ?
4. D’autres requêtes !
(a) Donnez le prix et le libellé de la boisson la moins chère servie dans ce bar. Résultats ?
(b) Quel est le libellé des boissons qui n’ont jamais été commandées ? Résultat ?
5. Droits d’accès
(a) Ecrire la requête permettant à l’utilisateur toto de faire toutes les requêtes d’interrogation sur les
tables serveur et boisson
(b) Ecrire la requête donnant tous les droits sur la base de données bar à l’utilisateur titi
Licence Informatique, 2ème année - Y. Sam 4/4
