Exercice 1 : Requêtes SQL

Exercice : Base de données d'une librairie en ligne

Modèle relationnel

Voici le modèle relationnel de la base de données avec les clés primaires soulignées et les clés étrangères en italique :

    CLIENTS (ID_Client, Nom, Prenom, Email, DateInscription)
    
    LIVRES (ISBN, Titre, Auteur, Prix, Stock, ID_Categorie)
    
    CATEGORIES (ID_Categorie, NomCategorie)
    
    COMMANDES (ID_Commande, ID_Client, DateCommande, Statut)
    
    DETAILS_COMMANDE (ID_Commande, ISBN, Quantite, PrixUnitaire)

Légende :

Souligné : Clé primaire
Italique : Clé étrangère

Notes sur les relations :

Un LIVRE appartient à une seule CATEGORIE.
Une COMMANDE est passée par un seul CLIENT.
Un DETAIL_COMMANDE fait référence à une COMMANDE et à un LIVRE.

Requêtes à réaliser

Affichez tous les livres dont le prix est supérieur à 15€.
Listez les noms et prénoms des clients inscrits en 2023.
Affichez le titre des livres et le nom de leur catégorie.
Comptez le nombre de livres par catégorie.
Trouvez les 5 livres les plus chers.
Listez les clients qui ont passé une commande en 2024.
Calculez le montant total de chaque commande.
Affichez les livres qui n'ont jamais été commandés.
Trouvez le client qui a dépensé le plus d'argent.
Listez les catégories qui ont plus de 5 livres avec un stock inférieur à 10.

Correction

    SELECT Titre, Prix
    FROM LIVRES
    WHERE Prix > 15;

    SELECT Nom, Prenom
    FROM CLIENTS
    WHERE YEAR(DateInscription) = 2023;

    SELECT L.Titre, C.NomCategorie
    FROM LIVRES L
    JOIN CATEGORIES C ON L.ID_Categorie = C.ID_Categorie;

    SELECT C.NomCategorie, COUNT(*) AS NombreLivres
    FROM LIVRES L
    JOIN CATEGORIES C ON L.ID_Categorie = C.ID_Categorie
    GROUP BY C.ID_Categorie, C.NomCategorie;

    SELECT Titre, Prix
    FROM LIVRES
    ORDER BY Prix DESC
    LIMIT 5;

    SELECT DISTINCT C.Nom, C.Prenom
    FROM CLIENTS C
    JOIN COMMANDES CO ON C.ID_Client = CO.ID_Client
    WHERE YEAR(CO.DateCommande) = 2024;

    SELECT CO.ID_Commande, SUM(DC.Quantite * DC.PrixUnitaire) AS MontantTotal
    FROM COMMANDES CO
    JOIN DETAILS_COMMANDE DC ON CO.ID_Commande = DC.ID_Commande
    GROUP BY CO.ID_Commande;

    SELECT L.Titre
    FROM LIVRES L
    LEFT JOIN DETAILS_COMMANDE DC ON L.ISBN = DC.ISBN
    WHERE DC.ISBN IS NULL;

    SELECT C.Nom, C.Prenom, SUM(DC.Quantite * DC.PrixUnitaire) AS MontantTotal
    FROM CLIENTS C
    JOIN COMMANDES CO ON C.ID_Client = CO.ID_Client
    JOIN DETAILS_COMMANDE DC ON CO.ID_Commande = DC.ID_Commande
    GROUP BY C.ID_Client, C.Nom, C.Prenom
    ORDER BY MontantTotal DESC
    LIMIT 1;

    SELECT C.NomCategorie, COUNT(*) AS NombreLivres
    FROM CATEGORIES C
    JOIN LIVRES L ON C.ID_Categorie = L.ID_Categorie
    WHERE L.Stock < 10
    GROUP BY C.ID_Categorie, C.NomCategorie
    HAVING COUNT(*) > 5;

Correction avec syntaxe WHERE

SELECT Titre, Prix
FROM LIVRES
WHERE Prix > 15;

SELECT Nom, Prenom
FROM CLIENTS
WHERE YEAR(DateInscription) = 2023;

SELECT L.Titre, C.NomCategorie
FROM LIVRES L, CATEGORIES C
WHERE L.ID_Categorie = C.ID_Categorie;

SELECT C.NomCategorie, COUNT(*) AS NombreLivres
FROM LIVRES L, CATEGORIES C
WHERE L.ID_Categorie = C.ID_Categorie
GROUP BY C.ID_Categorie, C.NomCategorie;

SELECT Titre, Prix
FROM LIVRES
ORDER BY Prix DESC
LIMIT 5;

SELECT DISTINCT C.Nom, C.Prenom
FROM CLIENTS C, COMMANDES CO
WHERE C.ID_Client = CO.ID_Client
  AND YEAR(CO.DateCommande) = 2024;

SELECT CO.ID_Commande, SUM(DC.Quantite * DC.PrixUnitaire) AS MontantTotal
FROM COMMANDES CO, DETAILS_COMMANDE DC
WHERE CO.ID_Commande = DC.ID_Commande
GROUP BY CO.ID_Commande;

SELECT L.Titre
FROM LIVRES L
WHERE L.ISBN NOT IN (SELECT ISBN FROM DETAILS_COMMANDE);

SELECT C.Nom, C.Prenom, SUM(DC.Quantite * DC.PrixUnitaire) AS MontantTotal
FROM CLIENTS C, COMMANDES CO, DETAILS_COMMANDE DC
WHERE C.ID_Client = CO.ID_Client
  AND CO.ID_Commande = DC.ID_Commande
GROUP BY C.ID_Client, C.Nom, C.Prenom
ORDER BY MontantTotal DESC
LIMIT 1;

SELECT C.NomCategorie, COUNT(*) AS NombreLivres
FROM CATEGORIES C, LIVRES L
WHERE C.ID_Categorie = L.ID_Categorie
  AND L.Stock < 10
GROUP BY C.ID_Categorie, C.NomCategorie
HAVING COUNT(*) > 5;