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 p.nom AS patient, m.nom AS medecin, c.date_consult
FROM Consultation c
JOIN Patient p ON c.id_patient = p.id_patient
JOIN Medecin m ON c.id_medecin = m.id_medecin;

SELECT p.id_patient, COUNT(id_consult) AS nb
FROM Patient p 
JOIN Consultation c ON c.id_patient=p.id_patient
GROUP BY id_patient