← Cours BD01

🐘 Exercices BD01 — PostgreSQL Fondamentaux

5 exercices · Solutions masquées

Exercice 1 — Création d'un schéma bibliothèque

Créez un schéma library avec les tables authors (id, name, birthdate, country), books (id, title, isbn UNIQUE, price, stock, author_id FK) et loans (id, book_id FK, borrower_name, loan_date, return_date). Ajoutez des contraintes CHECK sur price (> 0) et stock (>= 0).

Voir la solution 
CREATE SCHEMA library;
SET search_path TO library, public;

CREATE TABLE authors (
  id        SERIAL PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  birthdate DATE,
  country   VARCHAR(50)
);

CREATE TABLE books (
  id        SERIAL PRIMARY KEY,
  title     VARCHAR(200) NOT NULL,
  isbn      VARCHAR(20) UNIQUE NOT NULL,
  price     NUMERIC(8,2) NOT NULL CHECK (price > 0),
  stock     INTEGER DEFAULT 0 CHECK (stock >= 0),
  author_id INTEGER REFERENCES authors(id) ON DELETE SET NULL
);

CREATE TABLE loans (
  id            SERIAL PRIMARY KEY,
  book_id       INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
  borrower_name VARCHAR(100) NOT NULL,
  loan_date     DATE DEFAULT CURRENT_DATE,
  return_date   DATE,
  CHECK (return_date IS NULL OR return_date >= loan_date)
);

Exercice 2 — Requêtes CRUD

Insérez 3 auteurs et 5 livres. Puis : (a) listez les livres dont le stock est inférieur à 5, (b) augmentez le prix de tous les livres de l'auteur 1 de 10%, (c) supprimez les livres avec isbn NULL en retournant leurs titres.

Voir la solution 
INSERT INTO authors (name, birthdate, country) VALUES
  ('Victor Hugo', '1802-02-26', 'France'),
  ('Albert Camus', '1913-11-07', 'France'),
  ('Gabriel García Márquez', '1927-03-06', 'Colombie');

INSERT INTO books (title, isbn, price, stock, author_id) VALUES
  ('Les Misérables', '978-0140444308', 14.99, 8, 1),
  ('Notre-Dame de Paris', '978-0140443530', 12.50, 2, 1),
  ('L''Étranger', '978-0140182507', 9.99, 0, 2),
  ('La Peste', '978-0140185477', 10.50, 15, 2),
  ('Cent ans de solitude', '978-0060883287', 13.99, 3, 3);

-- (a) Stock < 5
SELECT title, stock FROM books WHERE stock < 5 ORDER BY stock;

-- (b) Augmentation 10%
UPDATE books SET price = price * 1.1 WHERE author_id = 1;

-- (c) Supprimer livres sans isbn
DELETE FROM books WHERE isbn IS NULL RETURNING title;

Exercice 3 — Jointures et agrégats

Écrivez les requêtes suivantes : (a) liste de tous les livres avec le nom de leur auteur, (b) nombre de livres par auteur + auteurs sans livres, (c) les 3 auteurs ayant les livres les plus chers en moyenne.

Voir la solution 
-- (a) Livres avec auteur
SELECT b.title, b.price, a.name AS author
FROM books b
JOIN authors a ON b.author_id = a.id
ORDER BY a.name, b.title;

-- (b) Livres par auteur + auteurs sans livres
SELECT a.name, COUNT(b.id) AS nb_books
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.id, a.name
ORDER BY nb_books DESC;

-- (c) Top 3 auteurs par prix moyen
SELECT a.name, ROUND(AVG(b.price), 2) AS avg_price, COUNT(b.id) AS nb
FROM authors a
JOIN books b ON b.author_id = a.id
GROUP BY a.id, a.name
ORDER BY avg_price DESC
LIMIT 3;

Exercice 4 — Index et performance

Créez les index appropriés pour optimiser : (a) recherche de livres par author_id, (b) recherche par isbn, (c) liste des emprunts non retournés (return_date IS NULL) pour un borrower_name donné.

Voir la solution 
-- (a) Recherche par auteur
CREATE INDEX idx_books_author ON books(author_id);

-- (b) ISBN (déjà unique, mais explicite)
-- L'index UNIQUE sur isbn est créé automatiquement
-- Si on veut une recherche insensible à la casse :
CREATE UNIQUE INDEX idx_books_isbn_lower ON books(lower(isbn));

-- (c) Index partiel — emprunts actifs + borrower
CREATE INDEX idx_loans_active_borrower
  ON loans(borrower_name)
  WHERE return_date IS NULL;

-- Vérification avec EXPLAIN
EXPLAIN ANALYZE
SELECT * FROM loans WHERE borrower_name = 'Jean Dupont' AND return_date IS NULL;

Exercice 5 — UPSERT et opérations avancées

Implémentez un système de gestion de stock : (a) UPSERT d'un livre (insérer ou mettre à jour le prix et le stock si l'ISBN existe déjà), (b) décrémenter le stock lors d'un emprunt (vérifier que stock > 0), (c) trouver les livres empruntés en retard (return_date IS NULL et loan_date > 30 jours).

Voir la solution 
-- (a) UPSERT par ISBN
INSERT INTO books (title, isbn, price, stock, author_id)
VALUES ('Les Misérables', '978-0140444308', 16.99, 10, 1)
ON CONFLICT (isbn)
DO UPDATE SET
  price = EXCLUDED.price,
  stock = EXCLUDED.stock;

-- (b) Emprunt avec décrémentation de stock (transaction)
BEGIN;
  UPDATE books SET stock = stock - 1
  WHERE id = 1 AND stock > 0;

  -- Vérifier que la mise à jour a eu lieu
  DO $$
  BEGIN
    IF NOT FOUND THEN
      RAISE EXCEPTION 'Stock insuffisant ou livre introuvable';
    END IF;
  END $$;

  INSERT INTO loans (book_id, borrower_name)
  VALUES (1, 'Marie Martin');
COMMIT;

-- (c) Emprunts en retard
SELECT l.borrower_name, b.title, l.loan_date,
       CURRENT_DATE - l.loan_date AS days_overdue
FROM loans l
JOIN books b ON l.book_id = b.id
WHERE l.return_date IS NULL
  AND l.loan_date < CURRENT_DATE - INTERVAL '30 days'
ORDER BY days_overdue DESC;
← Cours BD01 Mini-projet BD01 →