API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 5th, 2018
2,979
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.21 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE "team", "participant", "championship" , "participant-team" , "rating" CASCADE;
  2.  
  3. CREATE TABLE "team" (
  4. "id" int NOT NULL,
  5. "Name" varchar(100) NOT NULL,
  6. "Status(official/unofficial)" BOOLEAN NOT NULL,
  7. "First-year_students(yes/no)" BOOLEAN NOT NULL,
  8. "School_team(yes/no)" BOOLEAN NOT NULL,
  9. "Room" int NOT NULL,
  10. "PC_num" int NOT NULL,
  11. "Problems_solved" int NOT NULL,
  12. "Time" int NOT NULL,
  13. "Place" int NOT NULL,
  14. CONSTRAINT team_pk PRIMARY KEY ("id")
  15. ) WITH (
  16. OIDS=FALSE
  17. );
  18.  
  19.  
  20.  
  21. CREATE TABLE "participant" (
  22. "id" int NOT NULL,
  23. "Name" char(100) NOT NULL,
  24. --"Team_id" int NOT NULL,
  25. "E-mail" char(100) NOT NULL,
  26. "Phone_number" char(100) NOT NULL,
  27. "University" char(100) NOT NULL,
  28. "Year_of_study" int NOT NULL,
  29. "From_School" BOOLEAN NOT NULL,
  30. CONSTRAINT participant_pk PRIMARY KEY ("id")
  31. ) WITH (
  32. OIDS=FALSE
  33. );
  34.  
  35.  
  36.  
  37. CREATE TABLE "championship" (
  38. "id" int NOT NULL,
  39. "Team_count" int NOT NULL,
  40. "First_place_id" int NOT NULL,
  41. "Second_place_id" int NOT NULL,
  42. "Third_place_id" int NOT NULL,
  43. "Best_1_year_team_id" int NOT NULL,
  44. "Best_school_team_id" int NOT NULL,
  45. CONSTRAINT championship_pk PRIMARY KEY ("id")
  46. ) WITH (
  47. OIDS=FALSE
  48. );
  49.  
  50.  
  51.  
  52. CREATE TABLE "participant-team" (
  53. "Participant_id" int NOT NULL,
  54. "Team_id" int NOT NULL
  55. ) WITH (
  56. OIDS=FALSE
  57. );
  58.  
  59.  
  60.  
  61. CREATE TABLE "rating" (
  62. "Championship_id" int NOT NULL,
  63. "Team_id" int NOT NULL,
  64. "Place" int NOT NULL
  65. ) WITH (
  66. OIDS=FALSE
  67. );
  68.  
  69.  
  70.  
  71.  
  72.  
  73. ALTER TABLE "championship" ADD CONSTRAINT "championship_fk0" FOREIGN KEY ("First_place_id") REFERENCES "team"("id");
  74. ALTER TABLE "championship" ADD CONSTRAINT "championship_fk1" FOREIGN KEY ("Second_place_id") REFERENCES "team"("id");
  75. ALTER TABLE "championship" ADD CONSTRAINT "championship_fk2" FOREIGN KEY ("Third_place_id") REFERENCES "team"("id");
  76. ALTER TABLE "championship" ADD CONSTRAINT "championship_fk3" FOREIGN KEY ("Best_1_year_team_id") REFERENCES "team"("id");
  77. ALTER TABLE "championship" ADD CONSTRAINT "championship_fk4" FOREIGN KEY ("Best_school_team_id") REFERENCES "team"("id");
  78.  
  79. ALTER TABLE "participant-team" ADD CONSTRAINT "participant-team_fk0" FOREIGN KEY ("Participant_id") REFERENCES "participant"("id");
  80. ALTER TABLE "participant-team" ADD CONSTRAINT "participant-team_fk1" FOREIGN KEY ("Team_id") REFERENCES "team"("id");
  81.  
  82. ALTER TABLE "rating" ADD CONSTRAINT "rating_fk0" FOREIGN KEY ("Championship_id") REFERENCES "championship"("id");
  83. ALTER TABLE "rating" ADD CONSTRAINT "rating_fk1" FOREIGN KEY ("Team_id") REFERENCES "team"("id");
  84. -- Заполняем Табличку участников
  85. INSERT INTO "participant" VALUES (1, 'Максимов', 'lovepoland@yandex.ru', '89217297364', 'SPbSU', 3, false);
  86. INSERT INTO "participant" VALUES (2, 'Анисимов', 'mynewmail1@yandex.ru', '89217297365', 'SPbSU', 1, false);
  87. INSERT INTO "participant" VALUES (3, 'Алексеев', 'mynewmail2@yandex.ru', '89217297366', 'SPbSU', 3, false);
  88. INSERT INTO "participant" VALUES (4, 'Алексин', 'mynewmail3@yandex.ru', '89217297367', 'ITMO', 4, false);
  89. INSERT INTO "participant" VALUES (5, 'Петров', 'mynewmail4@yandex.ru', '89217297368', 'SPbSU', 2, false);
  90. INSERT INTO "participant" VALUES (6, 'Смирнов', 'mynewmail5@yandex.ru', '89217297369', 'SPbPU', 1, false);
  91. INSERT INTO "participant" VALUES (7, 'Смирнова', 'mynewmail6@yandex.ru', '892172973610', 'SPbSU', 2, false);
  92. INSERT INTO "participant" VALUES (8, 'Максимова', 'mynewmail7@yandex.ru', '89217297311', 'SPbSU', 2, false);
  93. INSERT INTO "participant" VALUES (9, 'Жмышенко', 'mynewmail8@yandex.ru', '89217297312', 'SPbSU', 2, false);
  94. INSERT INTO "participant" VALUES (10, 'Марцинкевич', 'mynewmail9@yandex.ru', '89217297313', '239', -, true);
  95. INSERT INTO "participant" VALUES (11, 'Андреев', 'mynewmail10@yandex.ru', '89217297314', 'SPbSU', 5, false);
  96. INSERT INTO "participant" VALUES (12, 'Бушлатов', 'mynewmail11@yandex.ru', '89217297315', 'SPbSU', 3, false);
  97. INSERT INTO "participant" VALUES (13, 'Айтматов', 'mynewmail12@yandex.r', '89217297316', 'SPbSU', 1, false);
  98. INSERT INTO "participant" VALUES (14, 'Максимович', 'mynewmail13@yandex.r', '89217297317', 'SPbSU', 3, false);
  99. INSERT INTO "participant" VALUES (15, 'Анисимович', 'mynewmail14@yandex.ru', '89217297318', 'SPbSU', 1, false);
  100. INSERT INTO "participant" VALUES (16, 'Алексеевич', 'mynewmail15@yandex.ru', '89217297319', '30', -, true);
  101. INSERT INTO "participant" VALUES (17, 'Алексинич', 'mynewmail16@yandex.ru', '89217297320', 'ITMO', 4, false);
  102. INSERT INTO "participant" VALUES (18, 'Петровитч', 'mynewmail17@yandex.ru', '89217297321', 'SPbSU', 2, false);
  103. INSERT INTO "participant" VALUES (19, 'Смирнович', 'mynewmail18@yandex.ru', '89217297322', 'SPbPU', 1, false);
  104. INSERT INTO "participant" VALUES (20, 'Смирноватич', 'mynewmail19@yandex.ru', '89217297323', 'SPbSU', 1, false);
  105. INSERT INTO "participant" VALUES (21, 'Максимоватич', 'mynewmail20@yandex.ru', '89217297324', 'SPbSU', 2, false);
  106. INSERT INTO "participant" VALUES (22, 'Шляпик', 'mynewmail21@yandex.ru', '89217297325', 'HSE', 2, false);
  107. INSERT INTO "participant" VALUES (23, 'Борович', 'mynewmail22@yandex.ru', '89217297326', '239', -, true);
  108. INSERT INTO "participant" VALUES (24, 'Андрейчук', 'mynewmail23@yandex.ru', '89217297327', 'ITMO', 5, false);
  109. INSERT INTO "participant" VALUES (25, 'Семенов', 'mynewmail24@yandex.ru', '89217297328', 'SPbSU', 3, false);
  110. INSERT INTO "participant" VALUES (26, 'Пратусевич', 'mynewmail25@yandex.r', '8921297329', '239', -, true);
  111. INSERT INTO "participant" VALUES (27, 'Детров', 'mynewmail26@yandex.r', '89217297330', 'SPbSU', 3, false);
  112. INSERT INTO "participant" VALUES (28, 'Рауль', 'mynewmail27@yandex.ru', '89217297331', 'MSU', 1, false);
  113. INSERT INTO "participant" VALUES (29, 'Маркес', 'mynewmail28@yandex.ru', '89217297332', '30', -, true);
  114. INSERT INTO "participant" VALUES (30, 'Гарсия', 'mynewmail29@yandex.ru', '89217297333', 'ITMO', 4, false);
  115. INSERT INTO "participant" VALUES (31, 'Родригез', 'mynewmail30@yandex.ru', '89217297334', 'SPbSU', 2, false);
  116. INSERT INTO "participant" VALUES (32, 'Лермонтов', 'mynewmail31@yandex.ru', '89217297335', 'PTU', 1, false);
  117. INSERT INTO "participant" VALUES (33, 'Пелевин', 'mynewmail32@yandex.ru', '89217297336', 'SPbSU', 1, false);
  118. INSERT INTO "participant" VALUES (34, 'Пушкин', 'mynewmail33@yandex.ru', '89217297337', 'MIPT', 2, false);
  119. INSERT INTO "participant" VALUES (35, 'Правдюков', 'mynewmail34@yandex.ru', '89217297338', 'HSE', 2, false);
  120. INSERT INTO "participant" VALUES (36, 'Боров', 'mynewmail35@yandex.ru', '89217297339', '239', -, true);
  121. INSERT INTO "participant" VALUES (37, 'Андрейчик', 'mynewmail36@yandex.ru', '89217297340', 'ITMO', 5, false);
  122. INSERT INTO "participant" VALUES (38, 'Семенова', 'mynewmail37@yandex.ru', '89217297341', 'SPbSU', 3, false);
  123. INSERT INTO "participant" VALUES (39, 'Пратусов', 'mynewmail38@yandex.r', '8921297342', '239', -, true);
  124. INSERT INTO "participant" VALUES (40, 'Александров', 'mynewmail9@yandex.ru', '89217297343', '239', -, true);
  125. INSERT INTO "participant" VALUES (41, 'Петрова', 'mynewmail40@yandex.ru', '89217297344', 'SPbSU', 5, false);
  126. INSERT INTO "participant" VALUES (42, 'Сергеев', 'mynewmail41@yandex.ru', '89217297345', 'SPbSU', 3, false);
  127. INSERT INTO "participant" VALUES (43, 'Денискин', 'mynewmail42@yandex.r', '89217297346', 'SPbSU', 1, false);
  128. INSERT INTO "participant" VALUES (44, 'Альбертовачич', 'mynewmail43@yandex.r', '89217297347', 'JMIH AIRLAINS', 1, false);
  129. INSERT INTO "participant" VALUES (45, 'Аникин', 'mynewmail44@yandex.r', '89217297348', 'SPbSU', 1, false);
  130. --
  131. -- Заполним команды
  132. -- room psnum solved time place
  133. INSERT INTO "team" VALUES (1, 'Dream Team', true , true , false , 1 , 1 , 10 , 60 , 2);
  134. INSERT INTO "team" VALUES (2, 'Best Team', true , true , false , 1 , 2 , 9 , 60 , 3);
  135. INSERT INTO "team" VALUES (3, 'Good Team', true , true , false , 1 , 3 , 8 , 60 , 4);
  136. INSERT INTO "team" VALUES (4, 'Bad Team', true , true , false , 1 , 4 , 7 , 60 , 5);
  137. INSERT INTO "team" VALUES (5, '239 Team', true , true , false , 1 , 5 , 6 , 60 , 6);
  138. INSERT INTO "team" VALUES (6, 'ITMO + HSE Team', true , true , false , 1 , 6 , 5 , 60 , 7);
  139. INSERT INTO "team" VALUES (7, 'SBP Team', true , true , false , 2 , 1 , 4 , 60 , 8);
  140. INSERT INTO "team" VALUES (8, 'UFA Team', true , true , false , 2 , 2 , 3 , 60 , 9);
  141. INSERT INTO "team" VALUES (9, 'DERYABINO Team', true , true , false , 2 , 3 , 2 , 60 , 10);
  142. INSERT INTO "team" VALUES (10, 'TAMBOV Team', true , true , false , 2 , 4 , 1 , 60 , 11);
  143. INSERT INTO "team" VALUES (11, 'SAMARA Team', true , true , false , 2 , 5 , 1 , 61 , 12);
  144. INSERT INTO "team" VALUES (12, 'SARATOV Team', true , true , false , 2 , 6 , 1 , 62 , 13);
  145. INSERT INTO "team" VALUES (13, 'KIIV Team', true , true , false , 2 , 7 , 11 , 1200 , 1);
  146. INSERT INTO "team" VALUES (14, 'LVIV Team', true , true , false , 2 , 8 , 0 , 00 , 14);
  147. INSERT INTO "team" VALUES (15, 'MINSK Team', true , true , false , 3 , 1 , 0 , 0 , 15);
  148. -- Заполним Чемпионаты
  149. INSERT INTO "championship" VALUES (1, 15, 13 , 1 , 2 , 13 , 7);
  150. -- связи и между командой и чемпионатом
  151. INSERT INTO "rating" VALUES (1 , 1 , 2);
  152. INSERT INTO "rating" VALUES (1 , 2 , 3);
  153. INSERT INTO "rating" VALUES (1 , 3 , 4);
  154. INSERT INTO "rating" VALUES (1 , 4 , 5);
  155. INSERT INTO "rating" VALUES (1 , 5 , 6);
  156. INSERT INTO "rating" VALUES (1 , 6 , 7);
  157. INSERT INTO "rating" VALUES (1 , 7 , 8);
  158. INSERT INTO "rating" VALUES (1 , 8 , 9);
  159. INSERT INTO "rating" VALUES (1 , 9 , 10);
  160. INSERT INTO "rating" VALUES (1 , 10 , 11);
  161. INSERT INTO "rating" VALUES (1 , 11 , 12);
  162. INSERT INTO "rating" VALUES (1 , 12 , 13);
  163. INSERT INTO "rating" VALUES (1 , 13 , 1);
  164. INSERT INTO "rating" VALUES (1 , 14 , 14);
  165. INSERT INTO "rating" VALUES (1 , 15 , 15);
  166. -- связи между участникамии командой
  167. INSERT INTO "participant-team" VALUES (30 , 7 );
  168. INSERT INTO "participant-team" VALUES (40 , 7 );
  169. INSERT INTO "participant-team" VALUES (36 , 7 );
  170. INSERT INTO "participant-team" VALUES (43 , 13 );
  171. INSERT INTO "participant-team" VALUES (44 , 13 );
  172. INSERT INTO "participant-team" VALUES (45 , 13 );
  173. INSERT INTO "participant-team" VALUES (1 , 1 );
  174. INSERT INTO "participant-team" VALUES (2 , 1 );
  175. INSERT INTO "participant-team" VALUES (3 , 1 );
  176. INSERT INTO "participant-team" VALUES (4 , 2 );
  177. INSERT INTO "participant-team" VALUES (5 , 2);
  178. INSERT INTO "participant-team" VALUES (6 , 2 );
  179. INSERT INTO "participant-team" VALUES (7 , 3 );
  180. INSERT INTO "participant-team" VALUES (8 , 3 );
  181. INSERT INTO "participant-team" VALUES (9 , 3 );
  182. INSERT INTO "participant-team" VALUES (10 , 4 );
  183. INSERT INTO "participant-team" VALUES (11 , 4 );
  184. INSERT INTO "participant-team" VALUES (12 , 4 );
  185. INSERT INTO "participant-team" VALUES (13 , 5 );
  186. INSERT INTO "participant-team" VALUES (14 , 5 );
  187. INSERT INTO "participant-team" VALUES (15 , 5);
  188. INSERT INTO "participant-team" VALUES (16 , 6 );
  189. INSERT INTO "participant-team" VALUES (17 , 6 );
  190. INSERT INTO "participant-team" VALUES (18 , 6 );
  191. INSERT INTO "participant-team" VALUES (19 , 8 );
  192. INSERT INTO "participant-team" VALUES (20 , 8 );
  193. INSERT INTO "participant-team" VALUES (21 , 8 );
  194. INSERT INTO "participant-team" VALUES (22 , 9 );
  195. INSERT INTO "participant-team" VALUES (23 , 9 );
  196. INSERT INTO "participant-team" VALUES (24 , 9 );
  197. INSERT INTO "participant-team" VALUES (25 , 10);
  198. INSERT INTO "participant-team" VALUES (26 , 10 );
  199. INSERT INTO "participant-team" VALUES (27 , 10 );
  200. INSERT INTO "participant-team" VALUES (28 , 11 );
  201. INSERT INTO "participant-team" VALUES (29 , 11 );
  202. INSERT INTO "participant-team" VALUES (31 , 11 );
  203. INSERT INTO "participant-team" VALUES (32 , 12 );
  204. INSERT INTO "participant-team" VALUES (33 , 12 );
  205. INSERT INTO "participant-team" VALUES (34 , 12 );
  206. INSERT INTO "participant-team" VALUES (35 , 14 );
  207. INSERT INTO "participant-team" VALUES (37 , 14 );
  208. INSERT INTO "participant-team" VALUES (38 , 14 );
  209. INSERT INTO "participant-team" VALUES (39 , 15 );
  210. INSERT INTO "participant-team" VALUES (41 , 15 );
  211. INSERT INTO "participant-team" VALUES (42 , 15 );
  212. -- все заполнили
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!