Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE "team" (
- "id" int NOT NULL,
- "Name" varchar(100) NOT NULL,
- "Status(official/unofficial)" BOOLEAN NOT NULL,
- "First-year_students(yes/no)" BOOLEAN NOT NULL,
- "School_team(yes/no)" BOOLEAN NOT NULL,
- "Room" int NOT NULL,
- "PC_num" int NOT NULL,
- "Problems_solved" int NOT NULL,
- "Time" int NOT NULL,
- "Place" int NOT NULL,
- CONSTRAINT team_pk PRIMARY KEY ("id")
- ) WITH (
- OIDS=FALSE
- );
- CREATE TABLE "participant" (
- "id" int NOT NULL,
- "Name" char(100) NOT NULL,
- "Team_id" int NOT NULL,
- "E-mail" char(100) NOT NULL,
- "Phone_number" char(100) NOT NULL,
- "University" char(100) NOT NULL,
- "Year_of_study" int NOT NULL,
- "From_School" BOOLEAN NOT NULL,
- CONSTRAINT participant_pk PRIMARY KEY ("id")
- ) WITH (
- OIDS=FALSE
- );
- CREATE TABLE "championship" (
- "id" int NOT NULL,
- "Team_count" int NOT NULL,
- "First_place_id" int NOT NULL,
- "Second_place_id" int NOT NULL,
- "Third_place_id" int NOT NULL,
- "Best_1_year_team_id" int NOT NULL,
- "Best_school_team_id" int NOT NULL,
- CONSTRAINT championship_pk PRIMARY KEY ("id")
- ) WITH (
- OIDS=FALSE
- );
- CREATE TABLE " participant-team" (
- "Participant_id" int NOT NULL,
- "Team_id" int NOT NULL
- ) WITH (
- OIDS=FALSE
- );
- CREATE TABLE "rating" (
- "Championship_id" int NOT NULL,
- "Team_id" int NOT NULL,
- "Place" int NOT NULL
- ) WITH (
- OIDS=FALSE
- );
- ALTER TABLE "championship" ADD CONSTRAINT "championship_fk0" FOREIGN KEY ("First_place_id") REFERENCES "team"("id");
- ALTER TABLE "championship" ADD CONSTRAINT "championship_fk1" FOREIGN KEY ("Second_place_id") REFERENCES "team"("id");
- ALTER TABLE "championship" ADD CONSTRAINT "championship_fk2" FOREIGN KEY ("Third_place_id") REFERENCES "team"("id");
- ALTER TABLE "championship" ADD CONSTRAINT "championship_fk3" FOREIGN KEY ("Best_1_year_team_id") REFERENCES "team"("id");
- ALTER TABLE "championship" ADD CONSTRAINT "championship_fk4" FOREIGN KEY ("Best_school_team_id") REFERENCES "team"("id");
- ALTER TABLE " participant-team" ADD CONSTRAINT " participant-team_fk0" FOREIGN KEY ("Participant_id") REFERENCES "participant"("id");
- ALTER TABLE " participant-team" ADD CONSTRAINT " participant-team_fk1" FOREIGN KEY ("Team_id") REFERENCES "team"("id");
- ALTER TABLE "rating" ADD CONSTRAINT "rating_fk0" FOREIGN KEY ("Championship_id") REFERENCES "championship"("id");
- ALTER TABLE "rating" ADD CONSTRAINT "rating_fk1" FOREIGN KEY ("Team_id") REFERENCES "team"("id");
- -- Заполняем Табличку участников
- INSERT INTO "Participant" VALUES (1, 'Максимов', 'lovepoland@yandex.ru', '89217297364', 'SPbSU', 3, false);
- INSERT INTO "Participant" VALUES (2, 'Анисимов', 'mynewmail1@yandex.ru', '89217297365', 'SPbSU', 1, false);
- INSERT INTO "Participant" VALUES (3, 'Алексеев', 'mynewmail2@yandex.ru', '89217297366', 'SPbSU', 3, false);
- INSERT INTO "Participant" VALUES (4, 'Алексин', 'mynewmail3@yandex.ru', '89217297367', 'ITMO', 4, false);
- INSERT INTO "Participant" VALUES (5, 'Петров', 'mynewmail4@yandex.ru', '89217297368', 'SPbSU', 2, false);
- INSERT INTO "Participant" VALUES (6, 'Смирнов', 'mynewmail5@yandex.ru', '89217297369', 'SPbPU', 1, false);
- INSERT INTO "Participant" VALUES (7, 'Смирнова', 'mynewmail6@yandex.ru', '892172973610', 'SPbSU', 2, false);
- INSERT INTO "Participant" VALUES (8, 'Максимова', 'mynewmail7@yandex.ru', '89217297311', 'SPbSU', 2, false);
- INSERT INTO "Participant" VALUES (9, 'Жмышенко', 'mynewmail8@yandex.ru', '89217297312', 'SPbSU', 2, false);
- INSERT INTO "Participant" VALUES (10, 'Марцинкевич', 'mynewmail9@yandex.ru', '89217297313', '239', -, true);
- INSERT INTO "Participant" VALUES (11, 'Андреев', 'mynewmail10@yandex.ru', '89217297314', 'SPbSU', 5, false);
- INSERT INTO "Participant" VALUES (12, 'Бушлатов', 'mynewmail11@yandex.ru', '89217297315', 'SPbSU', 3, false);
- INSERT INTO "Participant" VALUES (13, 'Айтматов', 'mynewmail12@yandex.r', '89217297316', 'SPbSU', 1, false);
- INSERT INTO "Participant" VALUES (14, 'Максимович', 'mynewmail13@yandex.r', '89217297317', 'SPbSU', 3, false);
- INSERT INTO "Participant" VALUES (15, 'Анисимович', 'mynewmail14@yandex.ru', '89217297318', 'SPbSU', 1, false);
- INSERT INTO "Participant" VALUES (16, 'Алексеевич', 'mynewmail15@yandex.ru', '89217297319', '30', -, true);
- INSERT INTO "Participant" VALUES (17, 'Алексинич', 'mynewmail16@yandex.ru', '89217297320', 'ITMO', 4, false);
- INSERT INTO "Participant" VALUES (18, 'Петровитч', 'mynewmail17@yandex.ru', '89217297321', 'SPbSU', 2, false);
- INSERT INTO "Participant" VALUES (19, 'Смирнович', 'mynewmail18@yandex.ru', '89217297322', 'SPbPU', 1, false);
- INSERT INTO "Participant" VALUES (20, 'Смирноватич', 'mynewmail19@yandex.ru', '89217297323', 'SPbSU', 1, false);
- INSERT INTO "Participant" VALUES (21, 'Максимоватич', 'mynewmail20@yandex.ru', '89217297324', 'SPbSU', 2, false);
- INSERT INTO "Participant" VALUES (22, 'Шляпик', 'mynewmail21@yandex.ru', '89217297325', 'HSE', 2, false);
- INSERT INTO "Participant" VALUES (23, 'Борович', 'mynewmail22@yandex.ru', '89217297326', '239', -, true);
- INSERT INTO "Participant" VALUES (24, 'Андрейчук', 'mynewmail23@yandex.ru', '89217297327', 'ITMO', 5, false);
- INSERT INTO "Participant" VALUES (25, 'Семенов', 'mynewmail24@yandex.ru', '89217297328', 'SPbSU', 3, false);
- INSERT INTO "Participant" VALUES (26, 'Пратусевич', 'mynewmail25@yandex.r', '8921297329', '239', -, true);
- INSERT INTO "Participant" VALUES (27, 'Детров', 'mynewmail26@yandex.r', '89217297330', 'SPbSU', 3, false);
- INSERT INTO "Participant" VALUES (28, 'Рауль', 'mynewmail27@yandex.ru', '89217297331', 'MSU', 1, false);
- INSERT INTO "Participant" VALUES (29, 'Маркес', 'mynewmail28@yandex.ru', '89217297332', '30', -, true);
- INSERT INTO "Participant" VALUES (30, 'Гарсия', 'mynewmail29@yandex.ru', '89217297333', 'ITMO', 4, false);
- INSERT INTO "Participant" VALUES (31, 'Родригез', 'mynewmail30@yandex.ru', '89217297334', 'SPbSU', 2, false);
- INSERT INTO "Participant" VALUES (32, 'Лермонтов', 'mynewmail31@yandex.ru', '89217297335', 'PTU', 1, false);
- INSERT INTO "Participant" VALUES (33, 'Пелевин', 'mynewmail32@yandex.ru', '89217297336', 'SPbSU', 1, false);
- INSERT INTO "Participant" VALUES (34, 'Пушкин', 'mynewmail33@yandex.ru', '89217297337', 'MIPT', 2, false);
- INSERT INTO "Participant" VALUES (35, 'Правдюков', 'mynewmail34@yandex.ru', '89217297338', 'HSE', 2, false);
- INSERT INTO "Participant" VALUES (36, 'Боров', 'mynewmail35@yandex.ru', '89217297339', '239', -, true);
- INSERT INTO "Participant" VALUES (37, 'Андрейчик', 'mynewmail36@yandex.ru', '89217297340', 'ITMO', 5, false);
- INSERT INTO "Participant" VALUES (38, 'Семенова', 'mynewmail37@yandex.ru', '89217297341', 'SPbSU', 3, false);
- INSERT INTO "Participant" VALUES (39, 'Пратусов', 'mynewmail38@yandex.r', '8921297342', '239', -, true);
- INSERT INTO "Participant" VALUES (40, 'Александров', 'mynewmail9@yandex.ru', '89217297343', '239', -, true);
- INSERT INTO "Participant" VALUES (41, 'Петрова', 'mynewmail40@yandex.ru', '89217297344', 'SPbSU', 5, false);
- INSERT INTO "Participant" VALUES (42, 'Сергеев', 'mynewmail41@yandex.ru', '89217297345', 'SPbSU', 3, false);
- INSERT INTO "Participant" VALUES (43, 'Денискин', 'mynewmail42@yandex.r', '89217297346', 'SPbSU', 1, false);
- INSERT INTO "Participant" VALUES (44, 'Альбертовачич', 'mynewmail43@yandex.r', '89217297347', 'JMIH AIRLAINS', 1, false);
- INSERT INTO "Participant" VALUES (45, 'Аникин', 'mynewmail44@yandex.r', '89217297348', 'SPbSU', 1, false);
- --
- -- Заполним команды
- -- room psnum solved time place
- INSERT INTO "Team" VALUES (1, 'Dream Team', true , true , false , 1 , 1 , 10 , 60 , 2)
- INSERT INTO "Team" VALUES (2, 'Best Team', true , true , false , 1 , 2 , 9 , 60 , 3)
- INSERT INTO "Team" VALUES (3, 'Good Team', true , true , false , 1 , 3 , 8 , 60 , 4)
- INSERT INTO "Team" VALUES (4, 'Bad Team', true , true , false , 1 , 4 , 7 , 60 , 5)
- INSERT INTO "Team" VALUES (5, '239 Team', true , true , false , 1 , 5 , 6 , 60 , 6)
- INSERT INTO "Team" VALUES (6, 'ITMO + HSE Team', true , true , false , 1 , 6 , 5 , 60 , 7)
- INSERT INTO "Team" VALUES (7, 'SBP Team', true , true , false , 2 , 1 , 4 , 60 , 8)
- INSERT INTO "Team" VALUES (8, 'UFA Team', true , true , false , 2 , 2 , 3 , 60 , 9)
- INSERT INTO "Team" VALUES (9, 'DERYABINO Team', true , true , false , 2 , 3 , 2 , 60 , 10)
- INSERT INTO "Team" VALUES (10, 'TAMBOV Team', true , true , false , 2 , 4 , 1 , 60 , 11)
- INSERT INTO "Team" VALUES (11, 'SAMARA Team', true , true , false , 2 , 5 , 1 , 61 , 12)
- INSERT INTO "Team" VALUES (12, 'SARATOV Team', true , true , false , 2 , 6 , 1 , 62 , 13)
- INSERT INTO "Team" VALUES (13, 'KIIV Team', true , true , false , 2 , 7 , 11 , 1200 , 1)
- INSERT INTO "Team" VALUES (14, 'LVIV Team', true , true , false , 2 , 8 , 0 , 00 , 14)
- INSERT INTO "Team" VALUES (15, 'MINSK Team', true , true , false , 3 , 1 , 0 , 0 , 15)
- -- Заполним Чемпионаты
- INSERT INTO "Championship" VALUES (1, 15, 13 , 1 , 2 , 13 , 7)
- -- связи и между командой и чемпионатом
- INSERT INTO "raing" VALUES (1 , 1 , 2)
- INSERT INTO "raing" VALUES (1 , 2 , 3)
- INSERT INTO "raing" VALUES (1 , 3 , 4)
- INSERT INTO "raing" VALUES (1 , 4 , 5)
- INSERT INTO "raing" VALUES (1 , 5 , 6)
- INSERT INTO "raing" VALUES (1 , 6 , 7)
- INSERT INTO "raing" VALUES (1 , 7 , 8)
- INSERT INTO "raing" VALUES (1 , 8 , 9)
- INSERT INTO "raing" VALUES (1 , 9 , 10)
- INSERT INTO "raing" VALUES (1 , 10 , 11)
- INSERT INTO "raing" VALUES (1 , 11 , 12)
- INSERT INTO "raing" VALUES (1 , 12 , 13)
- INSERT INTO "raing" VALUES (1 , 13 , 1)
- INSERT INTO "raing" VALUES (1 , 14 , 14)
- INSERT INTO "raing" VALUES (1 , 15 , 15)
- -- связи между участникамии командой
- INSERT INTO "participant-team" VALUES (30 , 7 )
- INSERT INTO "participant-team" VALUES (40 , 7 )
- INSERT INTO "participant-team" VALUES (36 , 7 )
- INSERT INTO "participant-team" VALUES (43 , 13 )
- INSERT INTO "participant-team" VALUES (44 , 13 )
- INSERT INTO "participant-team" VALUES (45 , 13 )
- INSERT INTO "participant-team" VALUES (1 , 1 )
- INSERT INTO "participant-team" VALUES (2 , 1 )
- INSERT INTO "participant-team" VALUES (3 , 1 )
- INSERT INTO "participant-team" VALUES (4 , 2 )
- INSERT INTO "participant-team" VALUES (5 , 2)
- INSERT INTO "participant-team" VALUES (6 , 2 )
- INSERT INTO "participant-team" VALUES (7 , 3 )
- INSERT INTO "participant-team" VALUES (8 , 3 )
- INSERT INTO "participant-team" VALUES (9 , 3 )
- INSERT INTO "participant-team" VALUES (10 , 4 )
- INSERT INTO "participant-team" VALUES (11 , 4 )
- INSERT INTO "participant-team" VALUES (12 , 4 )
- INSERT INTO "participant-team" VALUES (13 , 5 )
- INSERT INTO "participant-team" VALUES (14 , 5 )
- INSERT INTO "participant-team" VALUES (15 , 5)
- INSERT INTO "participant-team" VALUES (16 , 6 )
- INSERT INTO "participant-team" VALUES (17 , 6 )
- INSERT INTO "participant-team" VALUES (18 , 6 )
- INSERT INTO "participant-team" VALUES (19 , 8 )
- INSERT INTO "participant-team" VALUES (20 , 8 )
- INSERT INTO "participant-team" VALUES (21 , 8 )
- INSERT INTO "participant-team" VALUES (22 , 9 )
- INSERT INTO "participant-team" VALUES (23 , 9 )
- INSERT INTO "participant-team" VALUES (24 , 9 )
- INSERT INTO "participant-team" VALUES (25 , 10)
- INSERT INTO "participant-team" VALUES (26 , 10 )
- INSERT INTO "participant-team" VALUES (27 , 10 )
- INSERT INTO "participant-team" VALUES (28 , 11 )
- INSERT INTO "participant-team" VALUES (29 , 11 )
- INSERT INTO "participant-team" VALUES (31 , 11 )
- INSERT INTO "participant-team" VALUES (32 , 12 )
- INSERT INTO "participant-team" VALUES (33 , 12 )
- INSERT INTO "participant-team" VALUES (34 , 12 )
- INSERT INTO "participant-team" VALUES (35 , 14 )
- INSERT INTO "participant-team" VALUES (37 , 14 )
- INSERT INTO "participant-team" VALUES (38 , 14 )
- INSERT INTO "participant-team" VALUES (39 , 15 )
- INSERT INTO "participant-team" VALUES (41 , 15 )
- INSERT INTO "participant-team" VALUES (42 , 15 )
- -- все заполнили
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement