--exo1 quest1-- 
CREATE TABLE Adherent (
    id_adherent VARCHAR (30) PRIMARY KEY,
    nom VARCHAR(30),
    prenom VARCHAR (30),
    email VARCHAR (30) NOT NULL UNIQUE,
    date_inscription DATE
    );

--exo1 quest2--
CREATE TABLE Livre (
    id_livre VARCHAR (50) PRIMARY KEY,
    titre VARCHAR (50),
    auteur VARCHAR (50),
    isbn VARCHAR (50) UNIQUE,
    disponible BOOLEAN (1)
);

-- =====================================================================
--  EXERCICE 2 - Faire évoluer une structure (cabinet médical)
-- =====================================================================

CREATE TABLE Patient (
    id_patient      INT          PRIMARY KEY,
    nom             VARCHAR(30)  NOT NULL,
    prenom          VARCHAR(30)  NOT NULL,
    date_naissance  DATE
);

INSERT INTO Patient (id_patient, nom, prenom, date_naissance) VALUES
(1, 'Martin',               'Louise',  '1958-04-17'),
(2, 'Bernard',              'Paul',    '1972-11-02'),
(3, 'Dubois',               'Emma',    '1985-06-23'),
(4, 'Thomas',               'Jules',   '1991-01-30'),
(5, 'Robert',               'Inès',    '2003-09-14'),
(6, 'Le Goff-Kerjean',      'Yann',    '1966-08-05');


ALTER TABLE Patient 
    ADD COLUMN telephone VARCHAR(15);

ALTER TABLE Patient
    MODIFY COLUMN nom VARCHAR (80);

ALTER TABLE Patient
    ADD COLUMN numero_secu VARCHAR (30) NOT NULL;

-- =====================================================================
--  EXERCICE 3 - Insérer, modifier, supprimer des données
-- =====================================================================

CREATE TABLE Etudiant (
    id_etudiant  INT           PRIMARY KEY,
    nom          VARCHAR(50)   NOT NULL,
    prenom       VARCHAR(50)   NOT NULL,
    moyenne      DECIMAL(4,2)  CHECK (moyenne BETWEEN 0 AND 20)
);

INSERT INTO Etudiant (id_etudiant, nom, prenom, moyenne) VALUES
(90, 'LAMBERT',  'Sarah',   12.75),
(91, 'FONTAINE', 'Lucas',    7.50),
(92, 'CHEVALIER','Manon',   16.20),
(93, 'ROUSSEAU', 'Théo',     8.00),
(94, 'BLANC',    'Camille',  5.25),
(95, 'GUERIN',   'Noah',    10.40),
(96, 'MULLER',   'Jade',     7.99),
(97, 'HENRY',    'Louis',   13.10),
(98, 'PERRIN',   'Zoé',      3.80),
(99, 'MOREL',    'Adam',    11.00);

--pas fait--

-- =====================================================================
--  EXERCICE 4 - Interroger une table
-- =====================================================================
CREATE TABLE Patient (
    id_patient      INT          PRIMARY KEY,
    nom             VARCHAR(80)  NOT NULL,
    prenom          VARCHAR(50)  NOT NULL,
    ville           VARCHAR(50),
    date_naissance  DATE
);

INSERT INTO Patient (id_patient, nom, prenom, ville, date_naissance) VALUES
( 1, 'Moreau',    'Julie',    'Lyon',          '1984-03-12'),
( 2, 'Bernard',   'Marc',     'Paris',         '1970-07-25'),
( 3, 'Martin',    'Sophie',   'Lyon',          '1992-11-08'),
( 4, 'Dumas',     'Antoine',  'Lyon',          '1965-01-19'),
( 5, 'Faure',     'Léa',      'Villeurbanne',  '2001-05-30'),
( 6, 'Mercier',   'Hugo',     'Marseille',     '2015-09-02'),
( 7, 'Arnaud',    'Clara',    'Lyon',          '2019-12-14'),
( 8, 'Leroy',     'Maxime',   'Grenoble',      '1958-06-21'),
( 9, 'Michel',    'Nina',     'Lyon',          '2008-02-27'),
(10, 'Girard',    'Paul',     'Bordeaux',      '1999-10-10'),
(11, 'Masson',    'Emma',     'Paris',         '2021-04-03'),
(12, 'Blanc',     'Lucas',    'Lyon',          '1977-08-16'),
(13, 'Roche',     'Manon',    'Saint-Étienne', '2012-07-07');

SELECT * FROM Patient WHERE Lyon
    ORDER BY nom ASC;

SELECT * FROM Patient
    ORDER BY M%;

SELECT * FROM Patient
    ORDER BY  date_naissance DESC 
    LIMIT 3;

-- =====================================================================
--  EXERCICE 5 - Jointures entre tables
-- =====================================================================
CREATE TABLE Patient (
    id_patient  INT          PRIMARY KEY,
    nom         VARCHAR(80)  NOT NULL
);

CREATE TABLE Medecin (
    id_medecin  INT          PRIMARY KEY,
    nom         VARCHAR(80)  NOT NULL,
    specialite  VARCHAR(50)  NOT NULL
);

CREATE TABLE Consultation (
    id_consult    INT   PRIMARY KEY,
    id_patient    INT   NOT NULL,
    id_medecin    INT   NOT NULL,
    date_consult  DATE  NOT NULL,
    FOREIGN KEY (id_patient) REFERENCES Patient(id_patient),
    FOREIGN KEY (id_medecin) REFERENCES Medecin(id_medecin)
);

INSERT INTO Patient (id_patient, nom) VALUES
(1, 'Martin'),
(2, 'Bernard'),
(3, 'Dubois'),
(4, 'Petit'),
(5, 'Durand'),
(6, 'Leroy'),
(7, 'Lemoine'),
(8, 'Caron');

INSERT INTO Medecin (id_medecin, nom, specialite) VALUES
(1, 'Dr Rousseau', 'Médecine générale'),
(2, 'Dr Fabre',    'Cardiologie'),
(3, 'Dr Lucas',    'Dermatologie'),
(4, 'Dr Vidal',    'Pédiatrie');

INSERT INTO Consultation (id_consult, id_patient, id_medecin, date_consult) VALUES
( 1, 1, 1, '2026-01-08'),
( 2, 1, 2, '2026-02-15'),
( 3, 2, 1, '2026-01-22'),
( 4, 3, 3, '2026-03-04'),
( 5, 1, 1, '2026-04-11'),
( 6, 4, 2, '2026-04-18'),
( 7, 5, 1, '2026-05-02'),
( 8, 2, 3, '2026-05-27'),
( 9, 6, 1, '2026-06-09'),
(10, 1, 3, '2026-06-30');

SELECT id_consultation, p.nom AS Patient, m.nom AS Medecin
FROM Consultation
INNER JOIN Patient.p ON c.id.patient = p.id.patient
INNER JOIN Medecin.m ON c.id.medecin = m.id.medecin;