Al AMINE

Sql - gestion de bibliotheque

5 min read

Sql - Gestion De Bibliotheque

Parfait ! Voici un autre exercice complet de SQL (SQLite), cette fois-ci sur une gestion de bibliothèque.

1. Modèle Logique des Données (MLD) en ER Diagram

Extrait de code

erDiagram
    LIVRE ||--o{ EMPRUNT : est_emprunte
    ADHERENT ||--o{ EMPRUNT : emprunte
    EMPRUNT {
        INTEGER id_emprunt PK
        INTEGER id_livre FK
        INTEGER id_adherent FK
        DATE date_emprunt
        DATE date_retour_prevue
        DATE date_retour_effective
    }
    AUTEUR ||--o{ LIVRE : a_ecrit
    LIVRE {
        INTEGER id_livre PK
        VARCHAR titre
        VARCHAR isbn
        INTEGER id_auteur FK
        INTEGER id_genre FK
        INTEGER nombre_exemplaires
    }
    GENRE ||--o{ LIVRE : est_de_genre
    ADHERENT {
        INTEGER id_adherent PK
        VARCHAR nom
        VARCHAR prenom
        VARCHAR adresse
        VARCHAR telephone
        DATE date_inscription
    }
    AUTEUR {
        INTEGER id_auteur PK
        VARCHAR nom_auteur
        VARCHAR prenom_auteur
    }
    GENRE {
        INTEGER id_genre PK
        VARCHAR nom_genre
    }

2. Création de la Base de Données et des Tables (À faire par vous)

Créez une nouvelle base de données nommée bibliotheque.db et exécutez les instructions SQL pour créer les tables basées sur le diagramme ER ci-dessus. N’oubliez pas les clés primaires et étrangères.

3. Insertion de Données (Instructions et Données)

Insérez les données suivantes dans les tables que vous avez créées.

Table ADHERENT:

id_adherentnomprenomadressetelephonedate_inscription
1TraoréFatouCité Keur Gorgui, Thiès77 123 45 672024-01-15
2DiopAliouQuartier Grand Standing, Mbour76 987 65 432024-03-20
3SowAminataCentre Ville, Saly Portudal78 555 11 222024-05-10
4MbayeMoussaSicap Liberté, Dakar70 777 88 992024-07-01

Table AUTEUR:

id_auteurnom_auteurprenom_auteur
1SembèneOusmane
2KaneCheikh Hamidou
3BaMariama
4SoyinkaWole

Table GENRE:

id_genrenom_genre
1Roman
2Essai
3Théâtre
4Poésie

Table LIVRE:

id_livretitreisbnid_auteurid_genrenombre_exemplaires
1Les Bouts de Bois de Dieu9782708701506113
2L’Aventure Ambiguë9782253033682212
3Une si longue lettre9782842140047314
4La Tragédie du Roi Christophe9782080703414132
5Le Soleil des Indépendances9782253014933413
6Anthologie de la nouvelle poésie nègre et malgache de langue française9782708700301441

Table EMPRUNT:

id_empruntid_livreid_adherentdate_empruntdate_retour_prevuedate_retour_effective
1112025-03-102025-03-312025-03-28
2222025-03-152025-04-05NULL
3312025-03-252025-04-152025-04-12
4132025-04-012025-04-22NULL
5422025-04-052025-04-26NULL

Instructions pour l’insertion (À faire par vous) :

Utilisez l’instruction INSERT INTO pour ajouter ces données dans les tables correspondantes.

4. Requêtes SQL (Beaucoup !) (À faire par vous)

Voici une autre longue liste de requêtes SQL pour votre base de données de bibliothèque.

Requêtes Simples:

  1. Affichez tous les adhérents.
  2. Affichez les noms et prénoms des auteurs.
  3. Affichez tous les livres.
  4. Affichez les titres et ISBN des livres.
  5. Affichez les livres du genre ‘Roman’.
  6. Affichez les adhérents inscrits après le 1er mars 2024.
  7. Affichez les emprunts en cours (date_retour_effective est NULL).
  8. Affichez les livres dont le nombre d’exemplaires est inférieur à 3.
  9. Affichez les genres de livres disponibles.
  10. Affichez les livres écrits par l’auteur dont l’id est 1.

Requêtes avec JOIN:

  1. Affichez les titres des livres et les noms de leurs auteurs.
  2. Affichez les noms des adhérents et les titres des livres qu’ils ont empruntés.
  3. Affichez les titres des livres et les noms de leurs genres.
  4. Affichez les détails des emprunts (id_emprunt, titre du livre, nom de l’adhérent).
  5. Affichez les noms des auteurs et le nombre de livres qu’ils ont écrits.
  6. Affichez les noms des genres et le nombre de livres dans chaque genre.
  7. Affichez les adhérents qui ont des emprunts en cours.
  8. Affichez les livres qui n’ont jamais été empruntés (en utilisant LEFT JOIN).
  9. Affichez les emprunts avec le nom de l’adhérent et le titre du livre, en incluant les dates d’emprunt et de retour prévue.
  10. Affichez les livres avec leur titre et le nom de l’auteur, triés par titre.

Requêtes avec fonctions d’agrégation et GROUP BY, HAVING:

  1. Comptez le nombre total d’adhérents.
  2. Comptez le nombre total de livres.
  3. Comptez le nombre d’emprunts effectués.
  4. Comptez le nombre de livres par auteur.
  5. Comptez le nombre de livres par genre.
  6. Trouvez le genre avec le plus de livres.
  7. Trouvez l’auteur avec le plus de livres.
  8. Comptez le nombre d’emprunts par adhérent.
  9. Affichez les adhérents qui ont emprunté plus d’un livre.
  10. Trouvez le livre le plus emprunté (nécessite une sous-requête ou une jointure et un regroupement).
  11. Calculez le nombre moyen d’exemplaires par livre.
  12. Trouvez le livre avec le moins d’exemplaires.

Requêtes avec LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN (si supporté):

  1. Affichez tous les livres et leurs emprunts correspondants (même les livres non empruntés).
  2. Affichez tous les adhérents et les livres qu’ils ont empruntés (même les adhérents sans emprunt).
  3. Affichez tous les auteurs et leurs livres (même les auteurs sans livre).
  4. Affichez tous les genres et les livres associés (même les genres sans livre).

Requêtes avec sous-requêtes:

  1. Affichez les livres du même genre que ‘Les Bouts de Bois de Dieu’.
  2. Affichez les adhérents qui ont emprunté au moins un livre écrit par ‘Sembène Ousmane’.
  3. Affichez les livres qui n’ont jamais été empruntés.
  4. Affichez les adhérents qui ont emprunté le livre avec le plus grand nombre d’exemplaires.
  5. Affichez les livres dont le nombre d’exemplaires est supérieur à la moyenne du nombre d’exemplaires de tous les livres.

Requêtes de modification de données:

  1. Ajoutez un nouvel exemplaire du livre ‘Les Bouts de Bois de Dieu’.
  2. Mettez à jour la date de retour effective de l’emprunt dont l’id est 2 à la date d’aujourd’hui.
  3. Modifiez le genre du livre ‘Anthologie de la nouvelle poésie nègre et malgache de langue française’ à ‘Poésie et Essai’ (nécessite potentiellement une nouvelle table de relation si un livre peut avoir plusieurs genres, sinon choisissez l’un des deux).
  4. Supprimez l’adhérent dont l’id est 4 (attention aux contraintes).
  5. Supprimez tous les emprunts qui ont une date de retour effective antérieure au 1er janvier 2025.

Requêtes avancées:

  1. Créez une vue qui affiche le titre du livre et le nom de l’adhérent pour tous les emprunts en cours.
  2. Sélectionnez toutes les informations de cette vue.
  3. Trouvez les livres qui ont été empruntés plus de deux fois.
  4. Identifiez les adhérents qui ont emprunté des livres de tous les genres disponibles (requête plus complexe).

J’espère que cet exercice vous plaira ! N’hésitez pas si vous avez des questions sur la structure ou l’insertion des données. À vous de jouer avec ces requêtes !

Graph View