-- Création de la base et des tables

CREATE DATABASE IF NOT EXISTS safouane
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE safouane;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Livres;
DROP TABLE IF EXISTS Auteurs;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE Auteurs (
    auteur_id INT AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100),
    PRIMARY KEY (auteur_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE Livres (
    livre_id INT AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(255) NOT NULL,
    annee_publication INT,
    auteur_id INT,
    resume TEXT,
    FOREIGN KEY (auteur_id) REFERENCES Auteurs(auteur_id)
      ON DELETE SET NULL
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- Insertion des auteurs et livres

INSERT INTO Auteurs (nom, prenom) VALUES
('Hugo', 'Victor'),
('Camus', 'Albert'),
('Rowling', 'J.K.'),
('Orwell', 'George'),
('Austen', 'Jane'),
('Tolstoy', 'Leo'),
('Proust', 'Marcel'),
('de Beauvoir', 'Simone'),
('Asimov', 'Isaac'),
('Shelley', 'Mary'),
('Zola', 'Émile'),
('Flaubert', 'Gustave'),
('Stendhal', ''),
('Verne', 'Jules'),
('Dumas', 'Alexandre'),
('Christie', 'Agatha'),
('Conan Doyle', 'Arthur'),
('King', 'Stephen'),
('Murakami', 'Haruki'),
('Garcia Marquez', 'Gabriel'),
('Woolf', 'Virginia'),
('Kafka', 'Franz'),
('Dostoevsky', 'Fyodor'),
('Balzac', 'Honoré de'),
('Dickens', 'Charles');

INSERT INTO Livres (titre, annee_publication, auteur_id, resume) VALUES
('Les Misérables', 1862, 1, 'Jean Valjean cherche la rédemption.'),
('Notre-Dame de Paris', 1831, 1, 'Quasimodo et Esmeralda.'),
('L’Étranger', 1942, 2, 'Un homme indifférent face à la société.'),
('Germinal', 1885, 11, 'La vie des mineurs et leur révolte.'),
('Les Trois Mousquetaires', 1844, 15, 'Aventures d’Artagnan et ses amis.');

SELECT COUNT(*) AS nb_auteurs FROM Auteurs;
SELECT COUNT(*) AS nb_livres FROM Livres;

-- EXERCICE 1 : INSERTION

INSERT INTO Auteurs (nom, prenom) VALUES ('Dumas', 'Alexandre');
INSERT INTO Livres (titre, annee_publication, auteur_id, resume)
VALUES ('Les Trois Mousquetaires', 1844, 15, 'Roman d’aventure.');

SELECT * FROM Auteurs;
SELECT * FROM Livres;

-- EXERCICE 2 : SELECTION

SELECT titre, annee_publication
FROM Livres
WHERE annee_publication < 1900
ORDER BY annee_publication;

SELECT *
FROM Auteurs
WHERE prenom = 'Victor';

SELECT titre, annee_publication
FROM Livres
WHERE titre LIKE '%Paris%';

-- EXERCICE 3 : SUPPRESSION

DELETE FROM Livres
WHERE titre = 'Notre-Dame de Paris';

SELECT * FROM Livres
WHERE titre LIKE '%Paris%';

SELECT COUNT(*) AS nb_livres_restants FROM Livres;

-- EXERCICE 4 : JOINTURES

SELECT 
    Livres.titre,
    Livres.annee_publication,
    Auteurs.nom,
    Auteurs.prenom
FROM Livres
JOIN Auteurs
    ON Livres.auteur_id = Auteurs.auteur_id
ORDER BY Livres.annee_publication;

SELECT 
    Livres.titre,
    Auteurs.nom
FROM Livres
JOIN Auteurs
    ON Livres.auteur_id = Auteurs.auteur_id;

SELECT 
    Livres.titre,
    Livres.annee_publication,
    Auteurs.nom
FROM Livres
JOIN Auteurs
    ON Livres.auteur_id = Auteurs.auteur_id
WHERE Livres.annee_publication > 1900
ORDER BY Livres.annee_publication;