CREATE TABLE Proprietaire(
id_proprietaire INT PRIMARY KEY AUTO_INCREMENT,
nom Varchar (15),
prenom Varchar(15),
ville Varchar(15)
)Engine=Innodb; 

CREATE TABLE Animal(
id_animal INT PRIMARY KEY AUTO_INCREMENT,
nom Varchar(15),
espece Varchar(15),
date_naissance DATE,
id_proprietaire INT,
FOREIGN KEY (id_proprietaire)
REFERENCES Proprietaire (id_proprietaire)
)Engine=Innodb;

CREATE TABLE Veterinaire (
  id_veterinaire INT PRIMARY KEY AUTO_INCREMENT,
  nom            VARCHAR(50),
  specialite     VARCHAR(50)
) ENGINE = InnoDB;

CREATE TABLE Consultation (
  id_consultation   INT PRIMARY KEY AUTO_INCREMENT,
  date_consultation DATE,
  motif             VARCHAR(100),
  cout              DECIMAL(6,2),
  id_animal         INT,
  id_veterinaire    INT,
  FOREIGN KEY (id_animal) REFERENCES Animal(id_animal),
  FOREIGN KEY (id_veterinaire) REFERENCES Veterinaire(id_veterinaire)
) ENGINE = InnoDB;


INSERT INTO Proprietaire (nom, prenom, ville) VALUES
  ('Dupont', 'Marie', 'Lyon'),
  ('Bernard', 'Julien', 'Villeurbanne'),
  ('Morel', 'Sophie', 'Lyon');

INSERT INTO Veterinaire (nom, specialite) VALUES
  ('Lefevre', 'Generaliste'),
  ('Dubois', 'Chirurgie'),
  ('Renard', 'Dermatologie');

INSERT INTO Animal (nom, espece, date_naissance, id_proprietaire) VALUES
  ('Rex', 'Chien', '2020-03-01', 1),
  ('Mimi', 'Chat', '2019-07-12', 1),
  ('Fido', 'Chien', '2021-01-15', 2),
  ('Nemo', 'Poisson', '2022-05-01', 3);

INSERT INTO Consultation (date_consultation, motif, cout, id_animal, id_veterinaire) VALUES
  ('2026-01-10', 'Vaccin', 45.00, 1, 1),
  ('2026-02-05', 'Consultation generale', 35.00, 1, 1),
  ('2026-01-20', 'Vaccin', 45.00, 2, 1),
  ('2026-03-01', 'Chirurgie', 250.00, 3, 2);

SELECT v.id_veterinaire, v.nom, COUNT(c.id_consultation) AS nb
FROM Veterinaire v 
LEFT JOIN Consultation c ON v.id_veterinaire=c.id_veterinaire
GROUP BY v.id_veterinaire, v.nom;

SELECT a.id_animal, a.nom, COUNT(c.id_consultation) AS cout
FROM Animal a 
LEFT JOIN Consultation c ON a.id_animal=c.id_animal
GROUP BY a.id_animal, a.nom;

SELECT v.id_veterinaire, v.nom, COUNT(c.id_consultation) AS nb
FROM Veterinaire v
LEFT JOIN Consultation c ON v.id_veterinaire=c.id_veterinaire
GROUP BY v.id_veterinaire, v.nom
HAVING COUNT(c.id_consultation)>1