Système de Gestion des Bases de Données (SGBD) - TP3 : SQL

Ce TP a pour objectif de familiariser les étudiants avec le langage SQL (Langage d’Interrogation des Données) à travers des requêtes appliquées sur une base de données fictive nommée Resto.tn. Les étudiants apprendront à écrire des requêtes pour extraire, trier, filtrer et agréger des données relatives aux restaurants, plats, clients, commandes et livreurs. Ce TP nécessite un accès à un Système de Gestion de Bases de Données (SGBD) supportant SQL et la base Resto.tn préalablement créée.

Objectifs

• Écrire des requêtes SQL simples et complexes pour interroger une base de données.
• Utiliser des clauses SELECT, WHERE, ORDER BY, GROUP BY, HAVING, et des fonctions d’agrégation.
• Maîtriser les jointures et sous-requêtes pour extraire des données liées.
• Appliquer des filtres sur les chaînes de caractères et les dates.
• Produire des résultats ordonnés et formatés selon des critères précis.

Prérequis et configuration

• Connaissance de base du modèle relationnel et des concepts SQL.
• Accès à un SGBD compatible SQL (MySQL, PostgreSQL, Oracle, etc.).
• Base de données Resto.tn installée et accessible.
• Outil d’exécution des requêtes SQL (interface graphique ou terminal).

Partie 1 : Requêtes simples et filtrage

Dans cette première partie, vous allez écrire des requêtes SQL pour afficher des informations sur les restaurants, plats, clients, livreurs et commandes. Chaque requête cible un besoin précis d’extraction ou de tri.

1. Afficher toutes les informations concernant tous les restaurants

Cette requête permet de visualiser l’intégralité des données stockées dans la table des restaurants.

SELECT * FROM restaurants;

Le résultat doit afficher toutes les colonnes et toutes les lignes de la table restaurants.

2. Afficher la liste des restaurants de chaque ville, ordonner par ordre décroissant des villes

Cette requête trie les restaurants par ville, du Z vers A.

SELECT * FROM restaurants ORDER BY ville DESC;

Le résultat doit montrer les restaurants groupés par ville, en commençant par la ville dont le nom est le plus élevé alphabétiquement.

3. Afficher les id des plats commandés au moins une fois

On extrait les identifiants des plats présents dans les commandes.

SELECT DISTINCT id_plat FROM commandes;

Le résultat liste les identifiants uniques des plats déjà commandés.

4. Afficher le nom des restaurants dont le rating n’a pas été calculé

On filtre les restaurants où la colonne rating est NULL ou vide.

SELECT nom FROM restaurants WHERE rating IS NULL;

Le résultat doit contenir uniquement les noms des restaurants sans rating.

5. Afficher la liste des plats disponibles par ordre décroissant du prix

On sélectionne les plats disponibles et on les trie du plus cher au moins cher.

SELECT * FROM plats WHERE disponibilite = 'oui' ORDER BY prix DESC;

Le résultat montre les plats disponibles avec leurs détails, triés par prix décroissant.

6. Afficher les restaurants de spécialité tunisienne situés à Tunis

Filtrer les restaurants dont la spécialité est tunisienne et la ville est Tunis.

SELECT * FROM restaurants WHERE specialite = 'tunisienne' AND ville = 'Tunis';

Le résultat doit afficher uniquement ces restaurants.

7. Afficher les noms en majuscules, prénoms en minuscules, villes avec première lettre en majuscule de tous les clients, ordonner par ville

On applique des fonctions de manipulation de chaînes et on trie par ville.

SELECT UPPER(nom) AS nom, LOWER(prenom) AS prenom, CONCAT(UPPER(LEFT(ville,1)), LOWER(SUBSTRING(ville,2))) AS ville FROM clients ORDER BY ville;

Le résultat affiche les noms en majuscules, prénoms en minuscules et villes formatées, triés par ville.

8. Afficher la liste des clients dont le nom commence par ‘b’ et le prénom se termine par ‘d’ ou contient ‘a’

Filtrer selon les conditions sur nom et prénom avec LIKE.

SELECT * FROM clients WHERE nom LIKE 'b%' AND (prenom LIKE '%d' OR prenom LIKE '%a%');

Le résultat doit contenir uniquement ces clients.

9. Afficher la liste des livreurs embauchés depuis 8 mois

On filtre les livreurs selon la date d’embauche en comparant avec la date actuelle.

SELECT * FROM livreurs WHERE DATEDIFF(CURRENT_DATE, date_embauche) >= 240;

Le résultat liste les livreurs embauchés depuis au moins 8 mois (environ 240 jours).

10. Afficher toutes les commandes passées pendant le troisième trimestre de l’année dernière

Filtrer les commandes dont la date est comprise entre le 1er juillet et le 30 septembre de l’année précédente.

SELECT * FROM commandes WHERE date_commande BETWEEN DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR), '%Y-07-01') AND DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR), '%Y-09-30');

Le résultat affiche les commandes du troisième trimestre de l’année dernière.

11. Afficher les plats sans gluten dont le prix est entre 10 et 30 dinars, ordonnés par disponibilité

Filtrer sur la composition et le prix, trier pour afficher d’abord les plats disponibles.

SELECT * FROM plats WHERE gluten = 'non' AND prix BETWEEN 10 AND 30 ORDER BY disponibilite DESC;

Le résultat liste les plats sans gluten dans la fourchette de prix, disponibles en premier.

12. Afficher les commandes livrées en moins de 30 minutes, avec id commande, id livreur et temps de livraison, triées par temps décroissant

Filtrer sur le temps de livraison, trier par ce temps.

SELECT id_commande, id_livreur, temps_livraison FROM commandes WHERE temps_livraison < 30 ORDER BY temps_livraison DESC;

Le résultat montre les commandes rapides, triées du plus long au plus court temps sous 30 minutes.

13. Afficher prix du plat le plus cher, le moins cher et prix moyen arrondi des plats pour :

a. Tous les plats

SELECT MAX(prix) AS prix_max, MIN(prix) AS prix_min, ROUND(AVG(prix), 2) AS prix_moyen FROM plats;

b. Les plats sans gluten

SELECT MAX(prix) AS prix_max, MIN(prix) AS prix_min, ROUND(AVG(prix), 2) AS prix_moyen FROM plats WHERE gluten = 'non';

c. Les plats du restaurant ‘R1’

SELECT MAX(prix) AS prix_max, MIN(prix) AS prix_min, ROUND(AVG(prix), 2) AS prix_moyen FROM plats WHERE id_restaurant = 'R1';

Chaque requête affiche les trois valeurs agrégées.

14. Afficher une liste numérotée des plats selon un ordre décroissant des prix :

a. Afficher numéro, nom du plat et prix

SELECT ROW_NUMBER() OVER (ORDER BY prix DESC) AS num, nom, prix FROM plats;

b. Afficher numéro et tous les champs relatifs au plat

SELECT ROW_NUMBER() OVER (ORDER BY prix DESC) AS num, plats.* FROM plats;

Le résultat doit numéroter les plats du plus cher au moins cher.

15. Affiner la liste précédente selon la composition des plats (avec ou sans gluten)

Ajouter une condition sur la colonne gluten pour filtrer la liste.

SELECT ROW_NUMBER() OVER (ORDER BY prix DESC) AS num, plats.* FROM plats WHERE gluten = 'oui';

Le résultat affiche uniquement les plats avec gluten, numérotés par prix décroissant.

16. Afficher un classement des restaurants selon le rating, avec toutes les informations

SELECT * FROM restaurants ORDER BY rating DESC;

Le résultat affiche les restaurants du mieux noté au moins bien noté.

17. Affiner l’affichage précédent avec un classement des restaurants les plus notés selon les spécialités

On trie d’abord par spécialité puis par rating décroissant.

SELECT * FROM restaurants ORDER BY specialite, rating DESC;

Le résultat regroupe les restaurants par spécialité, puis par rating.

18. Calculer le prix moyen des plats de chaque restaurant

Utiliser GROUP BY sur id_restaurant et calculer la moyenne.

SELECT id_restaurant, ROUND(AVG(prix), 2) AS prix_moyen FROM plats GROUP BY id_restaurant;

Le résultat donne le prix moyen des plats par restaurant.

Partie 2 : Sous-interrogations

Cette partie introduit les sous-requêtes pour des extractions plus complexes.

19. Afficher la liste des restaurants où tous les plats sont non disponibles

On sélectionne les restaurants pour lesquels aucun plat n’est disponible.

SELECT * FROM restaurants WHERE id_restaurant NOT IN (SELECT DISTINCT id_restaurant FROM plats WHERE disponibilite = 'oui');

Le résultat liste les restaurants sans aucun plat disponible.

20. Extraire la liste des plats des restaurants « Chili’s » et « JOE CHAMPS », ordonner par restaurant

SELECT plats.* FROM plats JOIN restaurants ON plats.id_restaurant = restaurants.id_restaurant WHERE restaurants.nom IN ('Chili''s', 'JOE CHAMPS') ORDER BY restaurants.nom;

Le résultat affiche les plats de ces deux restaurants, triés par nom de restaurant.

21. Afficher la liste des plats avec gluten disponibles à Tunis ou à Sousse

SELECT plats.* FROM plats JOIN restaurants ON plats.id_restaurant = restaurants.id_restaurant WHERE plats.gluten = 'oui' AND plats.disponibilite = 'oui' AND restaurants.ville IN ('Tunis', 'Sousse');

Le résultat montre les plats avec gluten disponibles dans ces deux villes.

22. Afficher les références des plats des commandes de la question 8

Réutiliser la condition de la question 8 pour filtrer les clients et extraire les plats commandés.

SELECT DISTINCT id_plat FROM commandes WHERE id_client IN (SELECT id_client FROM clients WHERE nom LIKE 'b%' AND (prenom LIKE '%d' OR prenom LIKE '%a%'));

Le résultat liste les plats commandés par ces clients.

23. Calculer le prix moyen des plats du restaurant Chili’s

SELECT ROUND(AVG(prix), 2) AS prix_moyen FROM plats WHERE id_restaurant = (SELECT id_restaurant FROM restaurants WHERE nom = 'Chili''s');

Le résultat affiche le prix moyen arrondi des plats de Chili’s.

24. Afficher le nom des restaurants qui offrent des plats à moins de 15 dinars

SELECT DISTINCT restaurants.nom FROM restaurants JOIN plats ON restaurants.id_restaurant = plats.id_restaurant WHERE plats.prix < 15;

Le résultat liste les restaurants proposant des plats à moins de 15 dinars.

Partie 3 : Jointures

Cette partie met en pratique les jointures pour combiner les données de plusieurs tables.

25. Calculer le prix du plat le plus cher des restaurants italiens

SELECT MAX(plats.prix) AS prix_max FROM plats JOIN restaurants ON plats.id_restaurant = restaurants.id_restaurant WHERE restaurants.specialite = 'italienne';

Le résultat donne le prix maximal des plats italiens.

26. Calculer le prix du plat le plus cher pour chaque spécialité, afficher prix et spécialité, ordonné par prix décroissant

SELECT restaurants.specialite, MAX(plats.prix) AS prix_max FROM plats JOIN restaurants ON plats.id_restaurant = restaurants.id_restaurant GROUP BY restaurants.specialite ORDER BY prix_max DESC;

Le résultat affiche chaque spécialité avec le prix maximal de ses plats, trié du plus cher au moins cher.

27. Afficher le nombre de commandes effectuées par chaque client, avec nom et prénom

a. Affiner pour n’afficher que les clients avec plus d’une commande

SELECT clients.nom, clients.prenom, COUNT(commandes.id_commande) AS nb_commandes FROM clients JOIN commandes ON clients.id_client = commandes.id_client GROUP BY clients.id_client HAVING COUNT(commandes.id_commande) > 1;

Le résultat liste les clients ayant passé plusieurs commandes.

28. Afficher les clients qui ont effectué le plus de commandes (référence à la question 27)

WITH commandes_par_client AS (
  SELECT clients.id_client, clients.nom, clients.prenom, COUNT(commandes.id_commande) AS nb_commandes
  FROM clients
  JOIN commandes ON clients.id_client = commandes.id_client
  GROUP BY clients.id_client
)
SELECT nom, prenom, nb_commandes FROM commandes_par_client WHERE nb_commandes = (SELECT MAX(nb_commandes) FROM commandes_par_client);

Le résultat affiche les clients avec le nombre maximal de commandes.

29. Vérifier la colonne total des commandes en affichant l’ancienne valeur et un recalcul

SELECT id_commande, total, SUM(prix * quantite) AS total_recalcule FROM commandes JOIN details_commandes ON commandes.id_commande = details_commandes.id_commande JOIN plats ON details_commandes.id_plat = plats.id_plat GROUP BY id_commande;

Le résultat compare le total enregistré et le total recalculé pour chaque commande.

30. Afficher un extrait relatif à toutes les commandes de la base Resto.tn

Le détail de l’extrait n’étant pas fourni, cette étape nécessite la définition précise des colonnes à afficher. Veuillez vous référer à la documentation ou au professeur pour cette requête.

31. Calculer le prix moyen des plats de chaque restaurant, en respectant un affichage spécifique

Sans exemple d’affichage, utilisez la requête suivante :

SELECT restaurants.nom, ROUND(AVG(plats.prix), 2) AS prix_moyen FROM plats JOIN restaurants ON plats.id_restaurant = restaurants.id_restaurant GROUP BY restaurants.nom;

Le résultat affiche le nom du restaurant et le prix moyen de ses plats.

32. Afficher la liste des restaurants italiens qui proposent les plats les plus chers de leur spécialité

Cette requête combine filtrage et sous-requête :

SELECT DISTINCT r.nom FROM restaurants r
JOIN plats p ON r.id_restaurant = p.id_restaurant
WHERE r.specialite = 'italienne' AND p.prix = (
  SELECT MAX(plats.prix) FROM plats JOIN restaurants ON plats.id_restaurant = restaurants.id_restaurant WHERE restaurants.specialite = r.specialite
);

Le résultat liste les restaurants italiens proposant les plats les plus chers dans leur spécialité.

33. Afficher le mois et l’année durant lesquels le plus de commandes ont été passées

SELECT YEAR(date_commande) AS annee, MONTH(date_commande) AS mois, COUNT(id_commande) AS nb_commandes FROM commandes GROUP BY annee, mois ORDER BY nb_commandes DESC LIMIT 1;

Le résultat indique le mois et l’année avec le plus grand nombre de commandes.

Résultats attendus

Les résultats doivent correspondre aux critères de filtrage et de tri indiqués dans chaque étape. Par exemple :

• Les listes doivent être ordonnées correctement (alphabétiquement ou numériquement).
• Les agrégations doivent afficher des valeurs arrondies à deux décimales.
• Les filtres sur chaînes de caractères doivent respecter la casse et les motifs indiqués.
• Les jointures doivent combiner les données des tables concernées sans perte d’information.

Pièges courants

• Oublier les guillemets simples autour des chaînes dans les clauses WHERE (ex. 'Tunis').
• Confondre les majuscules et minuscules dans les filtres sur chaînes selon le SGBD.
• Ne pas utiliser DISTINCT lorsque nécessaire pour éviter les doublons.
• Omettre les conditions de jointure, ce qui produit un produit cartésien non désiré.
• Mal calculer les dates, notamment pour les intervalles et les sous-requêtes temporelles.
• Ne pas utiliser les fonctions d’agrégation avec GROUP BY correctement.
• Ne pas gérer les valeurs NULL dans les filtres, notamment pour le rating.
• Confondre les alias et noms de colonnes dans les requêtes complexes.