Swih

Untitled

Dec 30th, 2020
1,175
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create database PMU;
  2. use PMU;
  3. drop database PMU;
  4.  
  5. create table CHEVAL (
  6. chevNum char(3) not null primary key,
  7. chevNom varchar(25),
  8. chevAge numeric(3),
  9. chevPoids dec(5,2),
  10. chevPropritaire char(3) not null,
  11. chevJockey char(4) not null
  12. );
  13.  
  14. create table JOCKEY(
  15. jockeyNum char(4) not null primary key,
  16. jockeyNom varchar(20),
  17. jockeyPoids dec(4,2),
  18. jockeyAge numeric(3),
  19. jockeyVille varchar(15),
  20. jockeyTel varchar(20)
  21. );
  22.  
  23. create table VACCIN (
  24. vaccCode char(3) not null primary key,
  25. vaccNom varchar(20),
  26. vaccUtilité varchar(20),
  27. vaccDose varchar(5)
  28. );
  29.  
  30. create table COURSE (
  31. courseNum char(5) not null primary key,
  32. courseNom varchar(20),
  33. courseLieu varchar(20),
  34. coursePrix1 char(10),
  35. coursePrix2 char(10),
  36. coursePrix3 char(10)
  37. );
  38.  
  39. create table PROPRIETAIRE (
  40. propNum char(3) not null primary key,
  41. propNom varchar(20),
  42. propPays varchar(20),
  43. propAge numeric(3),
  44. propTel varchar(20),
  45. propVille varchar(15),
  46. propAdresse varchar(25),
  47. propCodePostal varchar(5)
  48. );
  49.  
  50. create table PARTICIPATION(
  51. partCheval char(3) not null ,  
  52. partCourse char(5) not null,
  53. partDate date
  54. );
  55.  
  56. create table VACCINATION(
  57. vaccinationCheval char(3) not null,
  58. vaccinationVaccin char(3) not null,
  59. vaccinationDate date
  60. );
  61.  
  62. alter table CHEVAL add constraint FK_chevPropritaire Foreign key (chevPropritaire) references PROPRIETAIRE(propNum) on delete cascade;
  63. alter table CHEVAL add constraint FK_chevJockey Foreign key (chevJockey) references JOCKEY(jockeyNum) on delete cascade;
  64.  
  65. alter table PARTICIPATION add constraint FK_partCheval foreign key (partCheval) references CHEVAL(chevNum) on delete cascade;
  66. alter table PARTICIPATION add constraint FK_partCourse foreign key (partCourse) references COURSE(courseNum) on delete cascade;
  67.  
  68. alter table VACCINATION add constraint FK_vaccinationCheval foreign key (vaccinationCheval) references CHEVAL(chevNum) on delete cascade;
  69. alter table VACCINATION add constraint FK_vaccinationVaccin foreign key (vaccinationVaccin) references VACCIN(vaccCode) on delete cascade;
  70.  
  71. insert into JOCKEY values ('RA78','Bertrand',68.35,24,'Central Park','06-12-47-85-42');
  72. insert into JOCKEY values ('UE89','Patrick',87.55,27,'Paris','06-14-86-74-75');
  73. insert into JOCKEY values ('RU12','Marise',72.85,31,'Moscou','07-54-18-75-94');
  74. insert into JOCKEY values ('DE45','Terruck',64.35,19,'Munich','06-47-86-95-27');
  75. insert into JOCKEY values ('ES07','Partrase',69.05,25,'Barcelone','07-41-98-76-58');
  76.  
  77. insert into PROPRIETAIRE values ('M85','Chourose','France',45,'06-85-84-65-17','Amilly','21 rue des moines','45200');
  78. insert into PROPRIETAIRE values ('S54','Laterise','Mali',25,'06-42-71-85-63','Tombouctou','35 rue des arbres','223');
  79. insert into PROPRIETAIRE values ('P09','Schuzele','Allemagne',35,'06-98-53-41-45','Munich','58 von Burgauerstraße','81929');
  80.  
  81. insert into VACCIN values ('HN3','Kolip','grippe equine','2mg');
  82. insert into VACCIN values ('L8M','Rhin','  rhinopneumonie','5mg');
  83. insert into VACCIN values ('M56' ,'antirabique','contre la rage ','2.5mg');
  84.  
  85. insert into COURSE values('ELB45','steeple-chase Paris','Paris','ML1','FV4','BK8');
  86. insert into COURSE values('LMA81','national Liverpool','Liverpool','BK8','JH1','ML1');
  87. insert into COURSE values ('JMV87','d`Amerique','Houston','ML1','BG1','FV4');
  88. insert into COURSE values ('KIL85','larc de triomphe','Paris','JH1','BG1','FV4');
  89. insert into COURSE values ('NJU28',' du Jockey-Club','Kenya','FV4','BK8','BG1');
  90.  
  91. insert into CHEVAL values ('ML1','Icarus',5,420,'M85','ES07');
  92. insert into CHEVAL values ('BK8','Titeuf',8,720,'P09','DE45');
  93. insert into CHEVAL values ('FV4','Pikachu',9,800,'M85','RA78');
  94. insert into CHEVAL values ('JH1','Lopez',7,450,'S54','RU12');
  95. insert into CHEVAL values ('BG1','Galoe',6,500,'P09','UE89');
  96.  
  97. insert into PARTICIPATION values ('ML1','ELB45','2021-09-20');
  98. insert into PARTICIPATION values ('FV4','ELB45','2021-09-20');
  99. insert into PARTICIPATION values ('BK8','ELB45','2021-09-20');
  100. insert into PARTICIPATION values ('BK8','LMA81','2018-10-09');
  101. insert into PARTICIPATION values ('JH1','LMA81','2018-10-09');
  102. insert into PARTICIPATION values ('ML1','LMA81','2018-10-09');
  103. insert into PARTICIPATION values ('ML1','JMV87','2017-10-09');
  104. insert into PARTICIPATION values ('BG1','JMV87','2017-10-09');
  105. insert into PARTICIPATION values ('FV4','JMV87','2017-10-09');
  106. insert into PARTICIPATION values ('BK8','JMV87','2017-10-09');
  107. insert into PARTICIPATION values ('FV4','KIL85','2020-07-07');
  108. insert into PARTICIPATION values ('BK8','KIL85','2020-07-07');
  109. insert into PARTICIPATION values ('BG1','KIL85','2020-07-07');
  110. insert into PARTICIPATION values ('FV4','KIL85','2020-07-07');
  111.  
  112. insert into VACCINATION values ('ML1','HN3','2017-12-09');
  113. insert into VACCINATION values ('FV4','HN3','2018-12-17');
  114. insert into VACCINATION values ('BK8','L8M','2019-04-16');
  115. insert into VACCINATION values ('JH1','L8M','2020-10-11');
  116. insert into VACCINATION values ('BG1','M56','2020-04-20');
  117.  
  118. select * from jockey;
  119. select * from PROPRIETAIRE;
  120. select * from cheval;
  121.  
  122. /*1*/select jockeyNom ,propNom ,chevNom, chevAge,chevPoids from CHEVAL c, PROPRIETAIRE p , JOCKEY j where c.chevPropritaire = p.propNum and c.chevJockey=j.jockeyNum ;
  123. /*2*/select courseNom,courseLieu,partDate from COURSE c, PARTICIPATION p where year(partDate)=2018 and c.courseNum=p.partCourse;
  124. /*3*/select * from PROPRIETAIRE where propPays = 'France';
  125. /*4*/select count(chevPropritaire) as Nombre_cheveaux,propNom from PROPRIETAIRE p, CHEVAL c where c.chevPropritaire=p.propNum group by chevPropritaire ;
  126. /*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;
  127. /*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;
  128. /*7*/select chevNom, count(coursePrix1) as Premier_prix_gagné from CHEVAL c , COURSE o where c.chevNum=o.coursePrix1 Group by chevNom;
  129. /*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;
  130. /*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;
  131. /*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';
RAW Paste Data