Projet — Bibliothèque

Ce qu'on va construire

On crée la base de données d'une bibliothèque. Elle contient des livres, des membres, et des emprunts. On va tout faire de A à Z : créer les tables, insérer des données, puis écrire des requêtes de plus en plus complexes.

Étape 1 — Créer les tables

Copie ce bloc sur sqliteonline.com et exécute-le.

-- Table des livres
CREATE TABLE livres (
  id     INTEGER PRIMARY KEY,
  titre  TEXT NOT NULL,
  auteur TEXT NOT NULL,
  genre  TEXT,
  annee  INTEGER
);

-- Table des membres
CREATE TABLE membres (
  id    INTEGER PRIMARY KEY,
  nom   TEXT NOT NULL,
  email TEXT UNIQUE,
  ville TEXT
);

-- Table des emprunts (relie livres et membres)
CREATE TABLE emprunts (
  id          INTEGER PRIMARY KEY,
  livre_id    INTEGER,
  membre_id   INTEGER,
  date_emprunt TEXT,
  date_retour TEXT,
  FOREIGN KEY (livre_id)  REFERENCES livres(id),
  FOREIGN KEY (membre_id) REFERENCES membres(id)
);

Étape 2 — Insérer des données

-- Livres
INSERT INTO livres (id, titre, auteur, genre, annee) VALUES
  (1, 'Les Misérables',         'Victor Hugo',    'Roman',     1862),
  (2, 'Le Petit Prince',         'Saint-Exupéry', 'Conte',     1943),
  (3, 'Dune',                    'Frank Herbert', 'SF',        1965),
  (4, 'Le Comte de Monte-Cristo', 'Alexandre Dumas','Aventure',  1844),
  (5, 'Neuromancer',             'William Gibson','SF',        1984);

-- Membres
INSERT INTO membres (id, nom, email, ville) VALUES
  (1, 'Alice Martin',  'alice@biblio.fr',  'Paris'),
  (2, 'Bruno Leblanc', 'bruno@biblio.fr',  'Lyon'),
  (3, 'Clara Dubois',  'clara@biblio.fr',  'Paris'),
  (4, 'David Moreau',  'david@biblio.fr',  'Nantes');

-- Emprunts
INSERT INTO emprunts (id, livre_id, membre_id, date_emprunt, date_retour) VALUES
  (1, 1, 1, '2024-01-10', '2024-01-24'),
  (2, 3, 1, '2024-02-05', NULL),
  (3, 2, 2, '2024-01-15', '2024-01-29'),
  (4, 5, 3, '2024-02-10', NULL),
  (5, 4, 2, '2024-03-01', '2024-03-15');

Étape 3 — Requêtes progressives

Niveau 1 — SELECT basique

-- 1. Afficher tous les livres
SELECT * FROM livres;

-- 2. Afficher uniquement les titres et auteurs
SELECT titre, auteur FROM livres;

-- 3. Livres de SF uniquement
SELECT titre, auteur
FROM livres
WHERE genre = 'SF';

Niveau 2 — Filtres et tris

-- 4. Livres publiés après 1900, triés par année
SELECT titre, annee
FROM livres
WHERE annee > 1900
ORDER BY annee ASC;

-- 5. Membres de Paris
SELECT nom, email
FROM membres
WHERE ville = 'Paris';

Niveau 3 — Agrégats

-- 6. Combien de livres par genre ?
SELECT genre, COUNT(*) AS nb_livres
FROM livres
GROUP BY genre;

-- 7. Nombre total d'emprunts
SELECT COUNT(*) AS total_emprunts
FROM emprunts;

Niveau 4 — Jointures

-- 8. Qui a emprunté quoi ? (nom du membre + titre du livre)
SELECT
  m.nom AS membre,
  l.titre,
  e.date_emprunt
FROM emprunts AS e
INNER JOIN membres AS m ON e.membre_id = m.id
INNER JOIN livres AS l  ON e.livre_id  = l.id;

-- 9. Emprunts non rendus (date_retour est NULL)
SELECT
  m.nom AS membre,
  l.titre,
  e.date_emprunt
FROM emprunts AS e
INNER JOIN membres AS m ON e.membre_id = m.id
INNER JOIN livres AS l  ON e.livre_id  = l.id
WHERE e.date_retour IS NULL;

-- 10. Membres qui n'ont jamais emprunté de livre
SELECT m.nom
FROM membres AS m
LEFT JOIN emprunts AS e ON m.id = e.membre_id
WHERE e.id IS NULL;

Défi bonus