API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 19th, 2018
129
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.40 KB | None | 0 0
raw download clone embed print report
  1. //create nsmirnova.sql
  2.  
  3.  
  4.  
  5. CREATE TABLE team (
  6. id int NOT NULL,
  7. name varchar(100) NOT NULL,
  8. "status(official/unofficial)" BOOLEAN NOT NULL,
  9. "first-year_students(yes/no)" BOOLEAN NOT NULL,
  10. "school_team(yes/no)" BOOLEAN NOT NULL,
  11. "room" int NOT NULL,
  12. "pc_num" int NOT NULL,
  13. CHECK ("pc_num">0)
  14. "problems_solved" int NOT NULL,
  15. "penalty" int NOT NULL,
  16. "place" int NOT NULL,
  17. CONSTRAINT team_pk PRIMARY KEY ("id")
  18. ) WITH (
  19. OIDS=FALSE
  20. );
  21.  
  22.  
  23.  
  24. CREATE TABLE "participant" (
  25. "id" int NOT NULL,
  26. "name" varchar(100) NOT NULL,
  27. "e-mail" varchar(100) NOT NULL UNIQUE,
  28. "phone_number" varchar(100) NOT NULL UNIQUE,
  29. "university" varchar(100) NOT NULL,
  30. "year_of_study" int NOT NULL,
  31. "from_school" BOOLEAN NOT NULL,
  32. CONSTRAINT participant_pk PRIMARY KEY ("id")
  33. ) WITH (
  34. OIDS=FALSE
  35. );
  36.  
  37.  
  38.  
  39. CREATE TABLE "championship" (
  40. "id" int NOT NULL,
  41. "team_count" int NOT NULL,
  42. "best_1_year_team_id" int NOT NULL,
  43. "best_school_team_id" int NOT NULL,
  44. CONSTRAINT championship_pk PRIMARY KEY ("id")
  45. ) WITH (
  46. OIDS=FALSE
  47. );
  48.  
  49.  
  50.  
  51. CREATE TABLE "participant-team" (
  52. "id" int NOT NULL,
  53. "participant_id" int NOT NULL,
  54. "team_id" int NOT NULL,
  55. CONSTRAINT participantteam_pk PRIMARY KEY ("id")
  56. ) WITH (
  57. OIDS=FALSE
  58. );
  59.  
  60.  
  61.  
  62. CREATE TABLE "rating" (
  63. "id" int NOT NULL,
  64. "championship_id" int NOT NULL,
  65. "team_id" int NOT NULL,
  66. "place" int NOT NULL,
  67. CONSTRAINT rating_pk PRIMARY KEY ("id")
  68. ) WITH (
  69. OIDS=FALSE
  70. );
  71.  
  72.  
  73.  
  74.  
  75. ALTER TABLE "championship" ADD CONSTRAINT "championship_fk0" FOREIGN KEY ("best_1_year_team_id") REFERENCES "team"("id");
  76. ALTER TABLE "championship" ADD CONSTRAINT "championship_fk1" FOREIGN KEY ("best_school_team_id") REFERENCES "team"("id");
  77.  
  78. ALTER TABLE "participant-team" ADD CONSTRAINT "participant-team_fk0" FOREIGN KEY ("participant_id") REFERENCES "participant"("id");
  79. ALTER TABLE "participant-team" ADD CONSTRAINT "participant-team_fk1" FOREIGN KEY ("team_id") REFERENCES "team"("id");
  80.  
  81. ALTER TABLE "rating" ADD CONSTRAINT "rating_fk0" FOREIGN KEY ("championship_id") REFERENCES "championship"("id");
  82. ALTER TABLE "rating" ADD CONSTRAINT "rating_fk1" FOREIGN KEY ("team_id") REFERENCES "team"("id");
  83.  
  84.  
  85.  
  86. INSERT INTO "participant" VALUES (1, 'Maximov', 'lovepoland@yandex.ru', '89217297364', 'SPbSU', 3, false);
  87. INSERT INTO "participant" VALUES (2, 'Smirnova', 'mynewmail1@yandex.ru', '89217297365', 'SPbSU', 1, false);
  88. INSERT INTO "participant" VALUES (3, 'Alexeev', 'mynewmail2@yandex.ru', '89217297366', 'SPbSU', 3, false);
  89. INSERT INTO "participant" VALUES (4, 'Alexin', 'mynewmail3@yandex.ru', '89217297367', 'ITMO', 4, false);
  90. INSERT INTO "participant" VALUES (5, 'Petrov', 'mynewmail4@yandex.ru', '89217297368', 'SPbSU', 2, false);
  91. INSERT INTO "participant" VALUES (6, 'Smirnov', 'mynewmail5@yandex.ru', '89217297369', 'SPbPU', 1, false);
  92. INSERT INTO "participant" VALUES (7, 'Belichenko', 'mynewmail6@yandex.ru', '892172973610', 'SPbSU', 2, false);
  93. INSERT INTO "participant" VALUES (8, 'Maximova', 'mynewmail7@yandex.ru', '89217297311', 'SPbSU', 2, false);
  94. INSERT INTO "participant" VALUES (9, 'Zhmyshenko', 'mynewmail8@yandex.ru', '89217297312', 'SPbSU', 2, false);
  95. INSERT INTO "participant" VALUES (10, 'Ivanov', 'mynewmail9@yandex.ru', '89217297313', '239', 0, true);
  96. INSERT INTO "participant" VALUES (11, 'Andreev', 'mynewmail10@yandex.ru', '89217297314', 'SPbSU', 5, false);
  97. INSERT INTO "participant" VALUES (12, 'Bushlatov', 'mynewmail11@yandex.ru', '89217297315', 'SPbSU', 3, false);
  98. INSERT INTO "participant" VALUES (13, 'Gubkin', 'mynewmail12@yandex.r', '89217297316', 'SPbSU', 1, false);
  99. INSERT INTO "participant" VALUES (14, 'Maximovich', 'mynewmail13@yandex.r', '89217297317', 'SPbSU', 3, false);
  100. INSERT INTO "participant" VALUES (15, 'Anisimovich', 'mynewmail14@yandex.ru', '89217297318', 'SPbSU', 1, false);
  101. INSERT INTO "participant" VALUES (16, 'Alexievich', 'mynewmail15@yandex.ru', '89217297319', '30', 0, true);
  102. INSERT INTO "participant" VALUES (17, 'Alexinich', 'mynewmail16@yandex.ru', '89217297320', 'ITMO', 4, false);
  103. INSERT INTO "participant" VALUES (18, 'Petrovich', 'mynewmail17@yandex.ru', '89217297321', 'SPbSU', 2, false);
  104. INSERT INTO "participant" VALUES (19, 'Smirnovich', 'mynewmail18@yandex.ru', '89217297322', 'SPbPU', 1, false);
  105. INSERT INTO "participant" VALUES (20, 'Bibikin', 'mynewmail19@yandex.ru', '89217297323', 'SPbSU', 1, false);
  106. INSERT INTO "participant" VALUES (21, 'Belikov', 'mynewmail20@yandex.ru', '89217297324', 'SPbSU', 2, false);
  107. INSERT INTO "participant" VALUES (22, 'Shlyapik', 'mynewmail21@yandex.ru', '89217297325', 'HSE', 2, false);
  108. INSERT INTO "participant" VALUES (23, 'Borovoy', 'mynewmail22@yandex.ru', '89217297326', '239', 0, true);
  109. INSERT INTO "participant" VALUES (24, 'Detrov', 'mynewmail23@yandex.ru', '89217297327', 'ITMO', 5, false);
  110. INSERT INTO "participant" VALUES (25, 'Semenov', 'mynewmail24@yandex.ru', '89217297328', 'SPbSU', 3, false);
  111. INSERT INTO "participant" VALUES (26, 'Pratusevich', 'mynewmail25@yandex.r', '8921297329', '239', 0, true);
  112. INSERT INTO "participant" VALUES (27, 'Detrova', 'mynewmail26@yandex.r', '89217297330', 'SPbSU', 3, false);
  113. INSERT INTO "participant" VALUES (28, 'Raul', 'mynewmail27@yandex.ru', '89217297331', 'MSU', 1, false);
  114. INSERT INTO "participant" VALUES (29, 'Markes', 'mynewmail28@yandex.ru', '89217297332', '30', 0, true);
  115. INSERT INTO "participant" VALUES (30, 'Markov', 'mynewmail29@yandex.ru', '89217297333', 'ITMO', 4, false);
  116. INSERT INTO "participant" VALUES (31, 'Spirin', 'mynewmail30@yandex.ru', '89217297334', 'SPbSU', 2, false);
  117. INSERT INTO "participant" VALUES (32, 'Lermontov', 'mynewmail31@yandex.ru', '89217297335', 'PTU', 1, false);
  118. INSERT INTO "participant" VALUES (33, 'Pelevin', 'mynewmail32@yandex.ru', '89217297336', 'SPbSU', 1, false);
  119. INSERT INTO "participant" VALUES (34, 'Pushkin', 'mynewmail33@yandex.ru', '89217297337', 'MIPT', 2, false);
  120. INSERT INTO "participant" VALUES (35, 'Plyushkin', 'mynewmail34@yandex.ru', '89217297338', 'HSE', 2, false);
  121. INSERT INTO "participant" VALUES (36, 'Pyshkin', 'mynewmail35@yandex.ru', '89217297339', '239', 0, true);
  122. INSERT INTO "participant" VALUES (37, 'Blinchikov', 'mynewmail36@yandex.ru', '89217297340', 'ITMO', 5, false);
  123. INSERT INTO "participant" VALUES (38, 'Ponchikov', 'mynewmail37@yandex.ru', '89217297341', 'SPbSU', 3, false);
  124. INSERT INTO "participant" VALUES (39, 'Oladushkin', 'mynewmail38@yandex.r', '8921297342', '239', 0, true);
  125. INSERT INTO "participant" VALUES (40, 'Pankeykin', 'mynewmail9@yandex.ru', '89217297343', '239', 0, true);
  126. INSERT INTO "participant" VALUES (41, 'Pirozhkov', 'mynewmail40@yandex.ru', '89217297344', 'SPbSU', 5, false);
  127. INSERT INTO "participant" VALUES (42, 'Karamelkin', 'mynewmail41@yandex.ru', '89217297345', 'SPbSU', 3, false);
  128. INSERT INTO "participant" VALUES (43, 'Deniskin', 'mynewmail42@yandex.r', '89217297346', 'SPbSU', 1, false);
  129. INSERT INTO "participant" VALUES (44, 'Albertych', 'mynewmail43@yandex.r', '89217297347', 'ITMO', 1, false);
  130. INSERT INTO "participant" VALUES (45, 'Anekin', 'mynewmail44@yandex.r', '89217297348', 'SPbSU', 1, false);
  131.  
  132.  
  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.  
  150. INSERT INTO "championship" VALUES (1, 15, 13 , 7);
  151.  
  152.  
  153. INSERT INTO "rating" VALUES (1, 1 , 1 , 2);
  154. INSERT INTO "rating" VALUES (2, 1 , 2 , 3);
  155. INSERT INTO "rating" VALUES (3, 1 , 3 , 4);
  156. INSERT INTO "rating" VALUES (4, 1 , 4 , 5);
  157. INSERT INTO "rating" VALUES (5, 1 , 5 , 6);
  158. INSERT INTO "rating" VALUES (6, 1 , 6 , 7);
  159. INSERT INTO "rating" VALUES (7, 1 , 7 , 8);
  160. INSERT INTO "rating" VALUES (8, 1 , 8 , 9);
  161. INSERT INTO "rating" VALUES (9, 1 , 9 , 10);
  162. INSERT INTO "rating" VALUES (10, 1 , 10 , 11);
  163. INSERT INTO "rating" VALUES (11, 1 , 11 , 12);
  164. INSERT INTO "rating" VALUES (12, 1 , 12 , 13);
  165. INSERT INTO "rating" VALUES (13, 1 , 13 , 1);
  166. INSERT INTO "rating" VALUES (14, 1 , 14 , 14);
  167. INSERT INTO "rating" VALUES (15, 1 , 15 , 15);
  168.  
  169.  
  170.  
  171. INSERT INTO "participant-team" VALUES (1, 30, 7);
  172. INSERT INTO "participant-team" VALUES (2, 40, 7);
  173. INSERT INTO "participant-team" VALUES (3, 36, 7);
  174. INSERT INTO "participant-team" VALUES (4, 43, 13);
  175. INSERT INTO "participant-team" VALUES (5, 44, 13);
  176. INSERT INTO "participant-team" VALUES (6, 45, 13);
  177. INSERT INTO "participant-team" VALUES (7, 1, 1);
  178. INSERT INTO "participant-team" VALUES (8, 2, 1);
  179. INSERT INTO "participant-team" VALUES (9, 3, 1);
  180. INSERT INTO "participant-team" VALUES (10, 4, 2);
  181. INSERT INTO "participant-team" VALUES (11, 5, 2);
  182. INSERT INTO "participant-team" VALUES (12, 6, 2);
  183. INSERT INTO "participant-team" VALUES (13, 7, 3);
  184. INSERT INTO "participant-team" VALUES (14, 8, 3);
  185. INSERT INTO "participant-team" VALUES (15, 9, 3);
  186. INSERT INTO "participant-team" VALUES (16, 10, 4);
  187. INSERT INTO "participant-team" VALUES (17, 11, 4);
  188. INSERT INTO "participant-team" VALUES (18, 12, 4);
  189. INSERT INTO "participant-team" VALUES (19, 13, 5);
  190. INSERT INTO "participant-team" VALUES (20, 14, 5);
  191. INSERT INTO "participant-team" VALUES (21, 15, 5);
  192. INSERT INTO "participant-team" VALUES (22, 16, 6);
  193. INSERT INTO "participant-team" VALUES (23, 17, 6);
  194. INSERT INTO "participant-team" VALUES (24, 18, 6);
  195. INSERT INTO "participant-team" VALUES (25, 19, 8);
  196. INSERT INTO "participant-team" VALUES (26, 20, 8);
  197. INSERT INTO "participant-team" VALUES (27, 21, 8);
  198. INSERT INTO "participant-team" VALUES (28, 22, 9);
  199. INSERT INTO "participant-team" VALUES (29, 23, 9);
  200. INSERT INTO "participant-team" VALUES (30, 24, 9);
  201. INSERT INTO "participant-team" VALUES (31, 25, 10);
  202. INSERT INTO "participant-team" VALUES (32, 26, 10);
  203. INSERT INTO "participant-team" VALUES (33, 27, 10);
  204. INSERT INTO "participant-team" VALUES (34, 28, 11);
  205. INSERT INTO "participant-team" VALUES (35, 29, 11);
  206. INSERT INTO "participant-team" VALUES (36, 31, 11);
  207. INSERT INTO "participant-team" VALUES (37, 32, 12);
  208. INSERT INTO "participant-team" VALUES (38, 33, 12);
  209. INSERT INTO "participant-team" VALUES (39, 34, 12);
  210. INSERT INTO "participant-team" VALUES (40, 35, 14);
  211. INSERT INTO "participant-team" VALUES (41, 37, 14);
  212. INSERT INTO "participant-team" VALUES (42, 38, 14);
  213. INSERT INTO "participant-team" VALUES (43, 39, 15);
  214. INSERT INTO "participant-team" VALUES (44, 41, 15);
  215. INSERT INTO "participant-team" VALUES (45, 42, 15);
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!