create database PMU;
use PMU;
drop database PMU;

create table CHEVAL (
chevNum char(3) not null primary key,
chevNom varchar(25),
chevAge numeric(3),
chevPoids dec(5,2),
chevPropritaire char(3) not null,
chevJockey char(4) not null
);

create table JOCKEY(
jockeyNum char(4) not null primary key,
jockeyNom varchar(20),
jockeyPoids dec(4,2),
jockeyAge numeric(3),
jockeyVille varchar(15),
jockeyTel varchar(20)
);

create table VACCIN (
vaccCode char(3) not null primary key,
vaccNom varchar(20),
vaccUtilité varchar(20),
vaccDose varchar(5)
);

create table COURSE (
courseNum char(5) not null primary key,
courseNom varchar(20),
courseLieu varchar(20),
coursePrix1 char(10),
coursePrix2 char(10),
coursePrix3 char(10)
);

create table PROPRIETAIRE (
propNum char(3) not null primary key,
propNom varchar(20),
propPays varchar(20),
propAge numeric(3),
propTel varchar(20),
propVille varchar(15),
propAdresse varchar(25),
propCodePostal varchar(5)
);

create table PARTICIPATION(
partCheval char(3) not null ,
partCourse char(5) not null,
partDate date
);

create table VACCINATION(
vaccinationCheval char(3) not null,
vaccinationVaccin char(3) not null,
vaccinationDate date
);

alter table CHEVAL add constraint FK_chevPropritaire Foreign key (chevPropritaire) references PROPRIETAIRE(propNum) on delete cascade;
alter table CHEVAL add constraint FK_chevJockey Foreign key (chevJockey) references JOCKEY(jockeyNum) on delete cascade;

alter table PARTICIPATION add constraint FK_partCheval foreign key (partCheval) references CHEVAL(chevNum) on delete cascade;
alter table PARTICIPATION add constraint FK_partCourse foreign key (partCourse) references COURSE(courseNum) on delete cascade;

alter table VACCINATION add constraint FK_vaccinationCheval foreign key (vaccinationCheval) references CHEVAL(chevNum) on delete cascade;
alter table VACCINATION add constraint FK_vaccinationVaccin foreign key (vaccinationVaccin) references VACCIN(vaccCode) on delete cascade;

insert into JOCKEY values ('RA78','Bertrand',68.35,24,'Central Park','06-12-47-85-42');
insert into JOCKEY values ('UE89','Patrick',87.55,27,'Paris','06-14-86-74-75');
insert into JOCKEY values ('RU12','Marise',72.85,31,'Moscou','07-54-18-75-94');
insert into JOCKEY values ('DE45','Terruck',64.35,19,'Munich','06-47-86-95-27');
insert into JOCKEY values ('ES07','Partrase',69.05,25,'Barcelone','07-41-98-76-58');

insert into PROPRIETAIRE values ('M85','Chourose','France',45,'06-85-84-65-17','Amilly','21 rue des moines','45200');
insert into PROPRIETAIRE values ('S54','Laterise','Mali',25,'06-42-71-85-63','Tombouctou','35 rue des arbres','223');
insert into PROPRIETAIRE values ('P09','Schuzele','Allemagne',35,'06-98-53-41-45','Munich','58 von Burgauerstraße','81929');

insert into VACCIN values ('HN3','Kolip','grippe equine','2mg');
insert into VACCIN values ('L8M','Rhin','  rhinopneumonie','5mg');
insert into VACCIN values ('M56' ,'antirabique','contre la rage ','2.5mg');

insert into COURSE values('ELB45','steeple-chase Paris','Paris','ML1','FV4','BK8');
insert into COURSE values('LMA81','national Liverpool','Liverpool','BK8','JH1','ML1');
insert into COURSE values ('JMV87','d`Amerique','Houston','ML1','BG1','FV4');
insert into COURSE values ('KIL85','larc de triomphe','Paris','JH1','BG1','FV4');
insert into COURSE values ('NJU28',' du Jockey-Club','Kenya','FV4','BK8','BG1');

insert into CHEVAL values ('ML1','Icarus',5,420,'M85','ES07');
insert into CHEVAL values ('BK8','Titeuf',8,720,'P09','DE45');
insert into CHEVAL values ('FV4','Pikachu',9,800,'M85','RA78');
insert into CHEVAL values ('JH1','Lopez',7,450,'S54','RU12');
insert into CHEVAL values ('BG1','Galoe',6,500,'P09','UE89');

insert into PARTICIPATION values ('ML1','ELB45','2021-09-20');
insert into PARTICIPATION values ('FV4','ELB45','2021-09-20');
insert into PARTICIPATION values ('BK8','ELB45','2021-09-20');
insert into PARTICIPATION values ('BK8','LMA81','2018-10-09');
insert into PARTICIPATION values ('JH1','LMA81','2018-10-09');
insert into PARTICIPATION values ('ML1','LMA81','2018-10-09');
insert into PARTICIPATION values ('ML1','JMV87','2017-10-09');
insert into PARTICIPATION values ('BG1','JMV87','2017-10-09');
insert into PARTICIPATION values ('FV4','JMV87','2017-10-09');
insert into PARTICIPATION values ('BK8','JMV87','2017-10-09');
insert into PARTICIPATION values ('FV4','KIL85','2020-07-07');
insert into PARTICIPATION values ('BK8','KIL85','2020-07-07');
insert into PARTICIPATION values ('BG1','KIL85','2020-07-07');
insert into PARTICIPATION values ('FV4','KIL85','2020-07-07');

insert into VACCINATION values ('ML1','HN3','2017-12-09');
insert into VACCINATION values ('FV4','HN3','2018-12-17');
insert into VACCINATION values ('BK8','L8M','2019-04-16');
insert into VACCINATION values ('JH1','L8M','2020-10-11');
insert into VACCINATION values ('BG1','M56','2020-04-20');

select * from jockey;
select * from PROPRIETAIRE;
select * from cheval;

/*1*/select jockeyNom ,propNom ,chevNom, chevAge,chevPoids from CHEVAL c, PROPRIETAIRE p , JOCKEY j where c.chevPropritaire = p.propNum and c.chevJockey=j.jockeyNum ;
/*2*/select courseNom,courseLieu,partDate from COURSE c, PARTICIPATION p where year(partDate)=2018 and c.courseNum=p.partCourse;
/*3*/select * from PROPRIETAIRE where propPays = 'France';
/*4*/select count(chevPropritaire) as Nombre_cheveaux,propNom from PROPRIETAIRE p, CHEVAL c where c.chevPropritaire=p.propNum group by chevPropritaire ;
/*5*/select chevNum,chevNom,propNom,count(partCheval) as nombre_course from CHEVAL c, PROPRIETAIRE p, PARTICIPATION a where c.chevPropritaire=p.propNum and c.chevNum=a.partCheval group by partCheval;
/*6*/select chevNom,vaccNom ,vaccUtilité ,vaccDose, vaccinationVaccin ,vaccinationDate from CHEVAL c, VACCIN v, VACCINATION a where  a.vaccinationCheval=c.chevNum and a.vaccinationVaccin=v.vaccCode;
/*7*/select chevNom, count(coursePrix1) as Premier_prix_gagné from CHEVAL c , COURSE o where c.chevNum=o.coursePrix1 Group by chevNom;
/*8*/select chevNum,chevNom from CHEVAL c , COURSE o , PARTICIPATION p where c.chevNum=p.partCheval and p.partCourse=o.courseNum and chevNum = coursePrix1 or chevNum = coursePrix2 Group by chevNom;
/*9*/select count(courseNum) as Nombre_course_2017,courseNom,courseLieu,partDate from COURSE c,PARTICIPATION p where year(partDate)=2017 and c.courseNum=p.partCourse;
/*10*/select chevNum,chevNom,propNom,propVille,vaccinationVaccin from CHEVAL c, PROPRIETAIRE p ,VACCINATION v where c.chevPropritaire= p.propNum and c.chevNum=v.vaccinationCheval and vaccinationVaccin='HN3';