CREATE TABLE Proprietaire (
  id_proprietaire INT PRIMARY KEY AUTO_INCREMENT,
  nom             VARCHAR(50),
  prenom          VARCHAR(50),
  ville           VARCHAR(50)
) ENGINE = InnoDB;

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

CREATE TABLE Animal (
  id_animal       INT PRIMARY KEY AUTO_INCREMENT,
  nom             VARCHAR(50),
  espece          VARCHAR(30),
  date_naissance  DATE,
  id_proprietaire INT,
  FOREIGN KEY (id_proprietaire) REFERENCES Proprietaire(id_proprietaire)
) 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;

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

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

SELECT v.id_veterinaire, SUM(c.cout) AS total
FROM Veterinaire v 
INNER JOIN Consultation c ON v.id_veterinaire=c.id_veterinaire
GROUP BY v.id_veterinaire
HAVING COUNT(c.id_consultation)>'1';

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 a.nom,p.nom AS proprietaire
FROM Animal a
JOIN Proprietaire p ON a.id_proprietaire=p.id_proprietaire;

SELECT c.id_consultation, a.nom AS Animal, v.nom AS Veterinaire 
FROM Consultation c 
JOIN Animal a ON c.id_animal=a.id_animal
JOIN Veterinaire v ON c.id_veterinaire=v.id_veterinaire;

SELECT p.nom AS proprietaire, a.nom AS animal
FROM Proprietaire p
INNER JOIN Animal a ON a.id_proprietaire=p.id_proprietaire;

SELECT a1.id_animal, a2.id_animal
FROM Animal a1
JOIN Animal a2 ON a1.id_proprietaire = a2.id_proprietaire
WHERE  a1.id_animal < a2.id_animal;

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

SELECT a.nom
FROM Animal a 
LEFT JOIN Consultation c ON c.id_animal=a.id_animal
WHERE c.id_consultation IS NULL;

SELECT v.nom, c.id_consultation
FROM Veterinaire v 
LEFT JOIN Consultation c ON v.id_veterinaire=c.id_veterinaire;

SELECT DISTINCT nom 
FROM Animal
WHERE id_animal IN(
    SELECT id_animal 
    FROM Consultation
    WHERE id_veterinaire IN( 
        SELECT id_veterinaire 
        FROM Veterinaire 
        WHERE nom='Lefevre'));

SELECT nom 
FROM Veterinaire v 
WHERE NOT EXISTS(
    SELECT*
    FROM Consultation c 
    WHERE c.id_veterinaire=v.id_veterinaire
);

SELECT p.nom 
FROM Proprietaire p 
WHERE EXISTS(
    SELECT*FROM Animal a 
    JOIN Consultation c ON a.id_animal=c.id_animal
    WHERE a.id_proprietaire=p.id_proprietaire
);

SELECT motif, cout 
FROM Consultation
WHERE cout >= ALL(
    SELECT cout 
    FROM Consultation);

SELECT nom 
FROM Animal 
WHERE espece='Chat'
UNION 
    SELECT nom
    FROM Animal
    WHERE id_animal IN(
        SELECT id_animal
        FROM Consultation
        WHERE cout>100);