-- Предметы
CREATE TABLE IF NOT EXISTS subjects (
subject_id SERIAL PRIMARY KEY,
subject_name VARCHAR(100) NOT NULL,
min_grade SMALLINT NOT NULL CHECK (min_grade > 0 AND min_grade < 12),
max_grade SMALLINT NOT NULL CHECK (max_grade > 0 AND max_grade < 12),
subject_rank SMALLINT CHECK (subject_rank > 0 AND subject_rank < 4),
CHECK (min_grade <= max_grade)
);
--Категории
CREATE TABLE IF NOT EXISTS categories (
category_id SERIAL PRIMARY KEY,
category_name VARCHAR(100) NOT NULL,
category_description TEXT NOT NULL,
category_salary INTEGER NOT NULL CHECK (category_salary > 0)
);
-- Проверяющие
CREATE TABLE IF NOT EXISTS examiners (
examiner_id SERIAL PRIMARY KEY,
examiner_email VARCHAR(200) NOT NULL CHECK (examiner_email LIKE '_%@_%._%'),
subject_id INTEGER REFERENCES subjects,
examiner_name VARCHAR(200) NOT NULL CHECK (examiner_name LIKE '_% _%'),
category_id INTEGER REFERENCES categories
);
-- Изменения категории
CREATE TABLE IF NOT EXISTS category_changings (
changing_id SERIAL PRIMARY KEY,
examiner_id INTEGER REFERENCES examiners,
changing_date TIMESTAMP NOT NULL CHECK (changing_date < current_timestamp),
supporting_document VARCHAR(500) NOT NULL CHECK (supporting_document LIKE 'https://_%._%'),
previous_category INTEGER REFERENCES categories
);
-- Даты проведения олимпиады
CREATE TABLE IF NOT EXISTS dates (
date_id SERIAL PRIMARY KEY,
date TIMESTAMP NOT NULL CHECK (date >= '2025-01-01 00:00'),
subject_id INTEGER REFERENCES subjects
);
-- Места проведения
CREATE TABLE IF NOT EXISTS places (
place_id SERIAL PRIMARY KEY,
addres VARCHAR(500) NOT NULL,
responsible_name VARCHAR(200) NOT NULL CHECK (responsible_name LIKE '_% _%'),
responsible_number CHAR(16) NOT NULL CHECK (responsible_number LIKE '+7 ___ ___-__-__'),
responsible_email VARCHAR(200) NOT NULL CHECK (responsible_email LIKE '_%@_%._%')
);
-- Проведение олимпиады
CREATE TABLE IF NOT EXISTS olympics (
olympics_id SERIAL PRIMARY KEY,
date_id INTEGER REFERENCES dates,
place_id INTEGER REFERENCES places
);
-- Школьники
CREATE TABLE IF NOT EXISTS students (
student_id SERIAL PRIMARY KEY,
student_email VARCHAR(200) NOT NULL CHECK (student_email LIKE '_%@_%._%'),
student_name VARCHAR(200) NOT NULL CHECK (student_name LIKE '_% _%'),
date_of_birth DATE CHECK (date_of_birth < current_timestamp),
grade SMALLINT NOT NULL CHECK (grade > 0 AND grade < 12),
parent_name VARCHAR(200) CHECK (parent_name LIKE '_% _%'),
number CHAR(16) NOT NULL CHECK (number LIKE '+7 ___ ___-__-__')
);
-- Участие в олимпиаде школьника
CREATE TABLE IF NOT EXISTS participations (
participation_id SERIAL PRIMARY KEY,
student_id INTEGER REFERENCES students,
examiner_id INTEGER REFERENCES examiners,
olympics_id INTEGER REFERENCES olympics,
score SMALLINT NOT NULL CHECK (score >= 0)
);
-- Аппеляции
CREATE TABLE IF NOT EXISTS appeals (
participation_id INTEGER PRIMARY KEY REFERENCES participations,
examiner_id INTEGER REFERENCES examiners,
appeal_time TIMESTAMP NOT NULL CHECK (appeal_time < current_timestamp),
previous_score SMALLINT NOT NULL
);
-- categories
INSERT INTO categories (category_id, category_name, category_description, category_salary) VALUES
(1, 'student', 'Студент соответствующего направления, не работающий в образовательной сфере.', 5000),
(2, 'bachelor', 'Бакалавр соответствующего направления, не работающий в образовательной сфере.', 7000),
(3, 'school teacher', 'Учитель соответствующего предмета.', 12000),
(4, 'university teacher', 'Семинарист соответствующего предмета.', 13000);
-- subjects
INSERT INTO subjects (subject_id, subject_name, min_grade, max_grade, subject_rank) VALUES
(1, 'math', 1, 11, 2),
(2, 'physics', 7, 11, 3),
(3, 'IT', 5, 11, 1),
(4, 'russian language', 1, 11, 2),
(5, 'english', 3, 11, 3),
(6, 'literature', 1, 11, NULL),
(7, 'geography', 1, 11, NULL),
(8, 'astronomy', 8, 11, 3),
(9, 'economics', 5, 11, 2),
(10, 'biology', 5, 11, NULL),
(11, 'chemistry', 8, 11, NULL),
(12, 'physical education', 1, 11, 3),
(13, 'history', 5, 11, 2),
(14, 'surrounding world', 1, 4, NULL),
(15, 'art', 1, 11, NULL),
(16, 'social science', 5, 11, 2),
(17, 'technology', 1, 11, 3);
-- examiners
INSERT INTO examiners (examiner_id, examiner_email, subject_id, examiner_name, category_id) VALUES
(1, 'gtellesson0@list-manage.com', 10, 'Ginni Tellesson', 4),
(2, 'cshelbourne1@biblegateway.com', 11, 'Candice Shelbourne', 3),
(3, 'mtrappe2@biglobe.ne.jp', 7, 'Muriel Trappe', 1),
(4, 'bjarlmann3@ucoz.com', 3, 'Bartolemo Jarlmann', 4),
(5, 'dashleigh4@gizmodo.com', 16, 'Denyse Ashleigh', 1),
(6, 'terett5@reverbnation.com', 7, 'Tamas Erett', 2),
(7, 'soffen6@toplist.cz', 12, 'Sigfried Offen', 4),
(8, 'elowndes7@digg.com', 6, 'Ellie Lowndes', 1),
(9, 'rgarrould8@uiuc.edu', 11, 'Robinetta Garrould', 3),
(10, 'sdarey9@cargocollective.com', 4, 'Sheeree Darey', 2),
(11, 'fantonellia@booking.com', 11, 'Fernandina Antonelli', 4),
(12, 'tgoaneb@yale.edu', 5, 'Theda Goane', 2),
(13, 'hcamillic@examiner.com', 8, 'Hilton Camilli', 3),
(14, 'kdearld@cnet.com', 11, 'Kellyann Dearl', 2),
(15, 'nthibodeauxe@soundcloud.com', 7, 'Nessi Thibodeaux', 2),
(16, 'cbraunsteinf@paginegialle.it', 12, 'Cecilio Braunstein', 2),
(17, 'habraminog@ihg.com', 2, 'Holt Abramino', 4),
(18, 'wkyteleyh@soundcloud.com', 17, 'Winfield Kyteley', 4),
(19, 'whawkswoodi@nyu.edu', 9, 'Willie Hawkswood', 3),
(20, 'mkoeppkej@chron.com', 10, 'Michaeline Koeppke', 3),
(21, 'icramphornk@mozilla.org', 17, 'Irwin Cramphorn', 2),
(22, 'ifairfoull@narod.ru', 12, 'Isador Fairfoul', 1),
(23, 'ashoutm@cisco.com', 9, 'Abie Shout', 4),
(24, 'gclaworthn@hugedomains.com', 4, 'Garvy Claworth', 2),
(25, 'kerato@uiuc.edu', 15, 'Kipp Erat', 4),
(26, 'alapidesp@nationalgeographic.com', 17, 'Alicea Lapides', 3),
(27, 'whobbenq@ed.gov', 4, 'Wesley Hobben', 3),
(28, 'snieassr@phoca.cz', 9, 'Sophie Nieass', 3),
(29, 'hgreatbanks@statcounter.com', 7, 'Ham Greatbank', 4),
(30, 'ehallamt@zimbio.com', 12, 'Elbertina Hallam', 3),
(31, 'blamondu@epa.gov', 16, 'Brita Lamond', 2),
(32, 'aharnellv@ask.com', 9, 'Alexia Harnell', 3),
(33, 'jcorringtonw@bing.com', 16, 'Jonah Corrington', 4),
(34, 'lcraiggx@upenn.edu', 7, 'Lorrayne Craigg', 3),
(35, 'lcoumbey@ihg.com', 6, 'Laurice Coumbe', 3),
(36, 'hpickrillz@wiley.com', 17, 'Harley Pickrill', 1),
(37, 'gtookill10@zimbio.com', 5, 'Glenna Tookill', 4),
(38, 'skuhnt11@cdc.gov', 13, 'Sophie Kuhnt', 3),
(39, 'ljolliman12@reuters.com', 15, 'Lorne Jolliman', 1),
(40, 'nlamond13@time.com', 7, 'Ninetta Lamond', 4),
(41, 'tderrett14@qq.com', 4, 'Theressa Derrett', 2),
(42, 'aledgister15@mlb.com', 7, 'Aylmar Ledgister', 2),
(43, 'ashimony16@nytimes.com', 13, 'Alix Shimony', 2),
(44, 'odyble17@ycombinator.com', 11, 'Othello Dyble', 4),
(45, 'tbennough18@amazon.de', 14, 'Tallia Bennough', 1),
(46, 'kbridewell19@state.gov', 10, 'Karim Bridewell', 1),
(47, 'tdarbey1a@slideshare.net', 8, 'Teriann Darbey', 3),
(48, 'nkuban1b@about.com', 1, 'Nelson Kuban', 4),
(49, 'dhutcheon1c@mapy.cz', 3, 'Dick Hutcheon', 3),
(50, 'bthonason1d@google.de', 12, 'Berty Thonason', 1),
(51, 'seddington1e@answers.com', 15, 'Sylvan Eddington', 2),
(52, 'adesorts1f@phpbb.com', 6, 'Alair Desorts', 1),
(53, 'sle1g@bizjournals.com', 9, 'Stanfield Le feuvre', 1),
(54, 'ggresser1h@nydailynews.com', 16, 'Gloriana Gresser', 4),
(55, 'lryman1i@discuz.net', 1, 'Ladonna Ryman', 4),
(56, 'vhennemann1j@noaa.gov', 15, 'Vaughan Hennemann', 4),
(57, 'criddington1k@is.gd', 12, 'Chico Riddington', 1),
(58, 'framsbotham1l@phpbb.com', 13, 'Fidelity Ramsbotham', 1),
(59, 'gsnelle1m@360.cn', 9, 'Galven Snelle', 1),
(60, 'gsidary1n@xinhuanet.com', 9, 'Guendolen Sidary', 1),
(61, 'estandbrook1o@people.com.cn', 17, 'Errick Standbrook', 4),
(62, 'pblacket1p@nbcnews.com', 1, 'Peggie Blacket', 4),
(63, 'dhentzeler1q@nasa.gov', 10, 'Dyan Hentzeler', 2),
(64, 'vmcwhin1r@spiegel.de', 6, 'Veronika McWhin', 3),
(65, 'jgirke1s@craigslist.org', 7, 'Jeralee Girke', 3),
(66, 'agarvan1t@engadget.com', 11, 'Ashia Garvan', 2),
(67, 'mperazzo1u@gravatar.com', 5, 'Mendy Perazzo', 1),
(68, 'gambage1v@goo.ne.jp', 17, 'Ganny Ambage', 2),
(69, 'koshaughnessy1w@artisteer.com', 13, 'Katinka O''Shaughnessy', 2),
(70, 'rcopins1x@bloglines.com', 15, 'Romonda Copins', 3),
(71, 'rflatte1y@unblog.fr', 12, 'Rachelle Flatte', 4),
(72, 'fchaffen1z@amazon.co.uk', 14, 'Fredi Chaffen', 2),
(73, 'bmacaloren20@soundcloud.com', 3, 'Brody MacAloren', 2),
(74, 'xcrilley21@mayoclinic.com', 7, 'Xylia Crilley', 1),
(75, 'lcarsberg22@com.com', 7, 'Laughton Carsberg', 4),
(76, 'tgiovannetti23@desdev.cn', 3, 'Tobe Giovannetti', 1),
(77, 'kgetty24@mlb.com', 5, 'Kristo Getty', 4),
(78, 'ddaoust25@nhs.uk', 17, 'Davina Daoust', 2),
(79, 'nviccary26@zdnet.com', 8, 'Neil Viccary', 1),
(80, 'wburchmore27@geocities.com', 4, 'Waylin Burchmore', 3),
(81, 'abaskett28@amazon.co.uk', 16, 'Arney Baskett', 4),
(82, 'afremantle29@usda.gov', 3, 'Audi Fremantle', 3),
(83, 'apantecost2a@seattletimes.com', 15, 'Amelie Pantecost', 3),
(84, 'llemerie2b@princeton.edu', 8, 'Latia Lemerie', 3),
(85, 'ebarensen2c@myspace.com', 3, 'Ebonee Barensen', 1),
(86, 'katkirk2d@w3.org', 4, 'Kaine Atkirk', 4),
(87, 'pdalgliesh2e@usda.gov', 13, 'Phyllida Dalgliesh', 2),
(88, 'khounsham2f@qq.com', 17, 'Katie Hounsham', 3),
(89, 'tgerdes2g@mediafire.com', 14, 'Tansy Gerdes', 3),
(90, 'lsplevings2h@deviantart.com', 10, 'Lina Splevings', 2),
(91, 'ndimberline2i@omniture.com', 11, 'Nadia Dimberline', 1),
(92, 'lhirche2j@hubpages.com', 5, 'Laurie Hirche', 2),
(93, 'xcouche2k@privacy.gov.au', 16, 'Xylina Couche', 4),
(94, 'hpanichelli2l@wordpress.com', 12, 'Humberto Panichelli', 2),
(95, 'gferri2m@soup.io', 5, 'Gamaliel Ferri', 3),
(96, 'bdablin2n@foxnews.com', 11, 'Barbe Dablin', 2),
(97, 'gbrewin2o@kickstarter.com', 4, 'Garv Brewin', 4),
(98, 'pborgnol2p@fema.gov', 11, 'Pete Borgnol', 3),
(99, 'jrichford2q@weather.com', 16, 'Junina Richford', 2),
(100, 'meckley2r@youku.com', 8, 'Melissa Eckley', 2);
-- category changings
INSERT INTO category_changings (changing_id, examiner_id, changing_date, supporting_document, previous_category) VALUES
(1, 95, '2025-01-22 05:05:15', 'https://Sem.ppt', 4),
(2, 11, '2024-05-09 08:36:16', 'https://VelitId.txt', 4),
(3, 77, '2024-10-12 00:00:23', 'https://EuTinciduntIn.jpeg', 4),
(4, 90, '2024-09-24 19:14:25', 'https://Sed.xls', 2),
(5, 17, '2024-04-30 05:14:00', 'https://Id.avi', 1),
(6, 78, '2025-01-26 15:41:24', 'https://Congue.txt', 1),
(7, 93, '2024-12-03 04:55:03', 'https://Erat.xls', 3),
(8, 69, '2025-01-15 04:07:02', 'https://MaurisViverra.mov', 3),
(9, 99, '2024-07-09 19:37:41', 'https://CurabiturConvallis.xls', 2),
(10, 71, '2025-01-25 11:06:55', 'https://VestibulumVestibulum.gif', 1),
(11, 65, '2024-07-27 18:36:56', 'https://SitAmet.ppt', 4),
(12, 86, '2025-02-05 08:08:53', 'https://IpsumAliquamNon.avi', 3),
(13, 67, '2025-01-20 22:21:08', 'https://DuiLuctus.png', 3),
(14, 80, '2024-10-26 00:22:20', 'https://NuncNislDuis.jpeg', 1),
(15, 56, '2024-10-06 21:04:16', 'https://DuisBibendum.doc', 1),
(16, 59, '2024-12-29 10:51:46', 'https://LigulaNecSem.gif', 3),
(17, 74, '2024-12-17 08:04:33', 'https://UtNulla.ppt', 2),
(18, 41, '2024-10-10 20:08:27', 'https://PraesentId.txt', 3),
(19, 55, '2025-01-16 03:06:00', 'https://Magnis.ppt', 3),
(20, 85, '2024-11-27 03:34:05', 'https://Augue.tiff', 3),
(21, 60, '2024-08-23 15:53:14', 'https://UtAt.mp3', 3),
(22, 48, '2024-12-02 15:38:42', 'https://Vivamus.ppt', 4),
(23, 44, '2025-03-23 06:11:35', 'https://JustoSitAmet.avi', 4),
(24, 79, '2024-08-31 05:20:20', 'https://Amet.avi', 1),
(25, 26, '2024-11-13 03:50:37', 'https://Dis.xls', 1),
(26, 36, '2024-04-05 06:11:03', 'https://NonQuam.tiff', 1),
(27, 56, '2024-05-07 23:00:01', 'https://VivamusVestibulumSagittis.mp3', 2),
(28, 90, '2024-07-15 14:10:27', 'https://Luctus.gif', 1),
(29, 81, '2025-02-14 17:26:55', 'https://VolutpatEleifendDonec.mp3', 3),
(30, 78, '2024-11-02 05:20:59', 'https://NisiVenenatis.jpeg', 3),
(31, 100, '2025-02-19 05:00:52', 'https://DictumstEtiamFaucibus.mp3', 2),
(32, 75, '2024-09-22 05:42:30', 'https://IpsumPrimisIn.gif', 1),
(33, 29, '2024-06-22 21:19:36', 'https://NuncProinAt.tiff', 3),
(34, 11, '2024-06-19 10:33:47', 'https://EleifendLuctusUltricies.pdf', 3),
(35, 56, '2024-11-19 06:01:40', 'https://Mauris.avi', 4),
(36, 53, '2024-05-03 09:16:24', 'https://IntegerPedeJusto.mp3', 2),
(37, 49, '2024-10-01 09:42:21', 'https://Et.mp3', 1),
(38, 32, '2024-08-23 15:14:57', 'https://NullaUltrices.xls', 2),
(39, 20, '2024-06-11 02:15:17', 'https://JustoSollicitudin.mp3', 1),
(40, 40, '2024-08-02 17:34:02', 'https://FermentumDonec.avi', 3),
(41, 76, '2024-12-10 00:30:43', 'https://In.png', 2),
(42, 58, '2024-10-25 02:55:44', 'https://NonQuamNec.mov', 3),
(43, 23, '2025-03-26 23:48:11', 'https://LaciniaNisiVenenatis.avi', 1),
(44, 89, '2024-06-17 16:34:40', 'https://VulputateVitae.avi', 2),
(45, 2, '2025-03-04 04:55:50', 'https://EtEros.txt', 4),
(46, 79, '2024-08-02 15:47:39', 'https://InPorttitorPede.ppt', 4),
(47, 19, '2024-12-15 10:42:46', 'https://Interdum.avi', 3),
(48, 55, '2024-07-30 07:33:52', 'https://MiPedeMalesuada.ppt', 2),
(49, 7, '2024-05-01 16:11:56', 'https://AeneanSitAmet.avi', 4),
(50, 16, '2024-04-22 16:05:15', 'https://Justo.xls', 4),
(51, 37, '2024-04-18 01:02:08', 'https://In.jpeg', 2),
(52, 33, '2025-02-21 03:17:25', 'https://VitaeIpsum.avi', 1),
(53, 8, '2024-06-16 10:32:33', 'https://Hendrerit.pdf', 2),
(54, 47, '2024-11-24 04:06:49', 'https://Imperdiet.gif', 3),
(55, 53, '2024-04-14 15:50:03', 'https://EtUltricesPosuere.jpeg', 4),
(56, 60, '2024-10-24 14:03:14', 'https://Lobortis.ppt', 2),
(57, 85, '2025-03-10 23:45:26', 'https://Volutpat.gif', 2),
(58, 79, '2024-09-17 02:27:03', 'https://UltricesVel.tiff', 2),
(59, 40, '2024-03-31 19:51:03', 'https://AnteIpsumPrimis.ppt', 1),
(60, 41, '2024-10-11 04:13:12', 'https://AliquamLacus.png', 1),
(61, 72, '2024-05-26 13:59:32', 'https://PosuereCubilia.jpeg', 4),
(62, 94, '2024-10-02 16:19:50', 'https://MagnaVestibulumAliquet.mp3', 2),
(63, 92, '2024-04-03 14:45:06', 'https://AdipiscingElit.mp3', 1),
(64, 46, '2025-01-14 17:55:50', 'https://NecDui.xls', 2),
(65, 62, '2025-02-15 22:54:46', 'https://UrnaUt.ppt', 1),
(66, 55, '2024-11-01 01:28:31', 'https://EgetSemperRutrum.ppt', 2),
(67, 19, '2024-07-10 19:59:06', 'https://DisParturientMontes.ppt', 1),
(68, 1, '2024-07-26 17:18:21', 'https://LobortisConvallis.jpeg', 1),
(69, 58, '2024-05-20 02:43:17', 'https://A.avi', 3),
(70, 96, '2024-07-12 23:48:08', 'https://PosuereCubilia.avi', 1),
(71, 56, '2024-08-22 08:25:44', 'https://PedeLobortisLigula.jpeg', 2),
(72, 37, '2025-01-21 03:34:14', 'https://Aenean.doc', 2),
(73, 72, '2024-05-17 12:49:35', 'https://SapienArcu.ppt', 3),
(74, 99, '2025-02-14 16:20:22', 'https://PharetraMagnaVestibulum.doc', 3),
(75, 5, '2024-11-27 21:34:24', 'https://SemMaurisLaoreet.mpeg', 4),
(76, 100, '2024-09-08 10:46:43', 'https://TristiqueTortorEu.mp3', 3),
(77, 96, '2024-12-16 02:48:58', 'https://Sapien.avi', 3),
(78, 45, '2024-07-20 11:44:07', 'https://In.xls', 4),
(79, 85, '2024-08-29 17:50:47', 'https://Interdum.mp3', 1),
(80, 52, '2024-06-18 10:31:24', 'https://NonMiInteger.ppt', 1),
(81, 58, '2024-12-06 20:40:20', 'https://Posuere.xls', 4),
(82, 90, '2025-02-15 19:10:44', 'https://Fermentum.jpeg', 2),
(83, 68, '2024-12-29 13:19:38', 'https://PlateaDictumstMaecenas.mpeg', 3),
(84, 16, '2024-09-03 15:44:37', 'https://HacHabitasse.avi', 2),
(85, 73, '2024-06-29 23:12:01', 'https://Vestibulum.mpeg', 3),
(86, 32, '2024-09-16 05:37:31', 'https://Convallis.pdf', 3),
(87, 1, '2025-03-10 15:30:10', 'https://SitAmetConsectetuer.txt', 1),
(88, 44, '2024-09-11 15:04:17', 'https://Pede.ppt', 4),
(89, 91, '2024-08-17 01:06:41', 'https://FuscePosuereFelis.tiff', 2),
(90, 67, '2025-01-14 06:09:13', 'https://MaecenasUt.xls', 3),
(91, 54, '2025-02-19 09:52:22', 'https://VestibulumRutrum.avi', 1),
(92, 78, '2024-09-29 20:42:41', 'https://FelisSed.avi', 4),
(93, 36, '2024-12-10 01:25:00', 'https://VulputateUtUltrices.jpeg', 4),
(94, 30, '2024-12-26 10:22:11', 'https://AeneanFermentumDonec.doc', 2),
(95, 65, '2024-07-28 20:33:16', 'https://InHac.tiff', 4),
(96, 46, '2024-11-03 23:55:41', 'https://IdTurpis.tiff', 3),
(97, 84, '2024-05-16 23:57:46', 'https://IntegerAcNeque.xls', 2),
(98, 87, '2024-09-21 09:43:26', 'https://Lorem.xls', 3),
(99, 9, '2025-01-26 14:24:45', 'https://PorttitorIdConsequat.png', 3),
(100, 60, '2024-05-29 04:56:55', 'https://EratVolutpatIn.ppt', 2);
--places
INSERT INTO places (place_id, addres, responsible_name, responsible_number, responsible_email) VALUES
(1, 'Baltimore - 898 Oak St', 'Carena Riccelli', '+7 012 345-67-89', 'criccelli0@aol.com'),
(2, 'Omaha - 565 Walnut St', 'Kile Saltsberger', '+7 210 987-65-43', 'ksaltsberger1@wiley.com'),
(3, 'Los Angeles - 456 Elm St', 'Erie MacGow', '+7 876 543-21-09', 'emacgow2@shop-pro.jp'),
(4, 'Des Moines - 343 Pine St', 'Silvia Cann', '+7 543 210-98-76', 'scann3@gizmodo.com'),
(5, 'Omaha - 565 Walnut St', 'Doreen Varren', '+7 543 210-98-76', 'dvarren4@myspace.com'),
(6, 'Orlando - 888 Cedar St', 'Agnesse Shillabear', '+7 543 210-98-76', 'ashillabear5@ucoz.ru'),
(7, 'Albuquerque - 232 Maple St', 'Mary Rickett', '+7 876 543-21-09', 'mrickett6@earthlink.net'),
(8, 'New York - 123 Main St', 'Emma Render', '+7 654 321-09-87', 'erender7@freewebs.com'),
(9, 'Providence - 787 Cedar St', 'Iorgos Ghilks', '+7 901 234-56-78', 'ighilks8@biglobe.ne.jp'),
(10, 'Memphis - 010 Elm St', 'Natalee Ashpital', '+7 109 876-54-32', 'nashpital9@technorati.com'),
(11, 'Richmond - 909 Maple St', 'Araldo Huzzey', '+7 210 987-65-43', 'ahuzzeya@alibaba.com'),
(12, 'Memphis - 010 Elm St', 'Ferguson Knivett', '+7 654 321-09-87', 'fknivettb@ning.com'),
(13, 'Boston - 707 Elm St', 'Lory St. Louis', '+7 109 876-54-32', 'lstc@lulu.com'),
(14, 'Boston - 707 Elm St', 'Barb Heyward', '+7 012 345-67-89', 'bheywardd@wordpress.org'),
(15, 'Detroit - 232 Pine St', 'Stevie Thomassin', '+7 765 432-10-98', 'sthomassine@hexun.com'),
(16, 'Phoenix - 909 Maple St', 'Maurene Mewes', '+7 765 432-10-98', 'mmewesf@bigcartel.com'),
(17, 'Phoenix - 909 Maple St', 'Isiahi Dyster', '+7 109 876-54-32', 'idysterg@reuters.com'),
(18, 'Richmond - 909 Maple St', 'Myrtie Traylen', '+7 345 678-90-12', 'mtraylenh@altervista.org'),
(19, 'Kansas City - 565 Maple St', 'Zena Adriano', '+7 000 111-22-33', 'zadrianoi@nationalgeographic.com'),
(20, 'Providence - 787 Cedar St', 'Justine Sterman', '+7 000 111-22-33', 'jstermanj@purevolume.com'),
(21, 'Indianapolis - 676 Cedar St', 'Tamas Belin', '+7 456 789-01-23', 'tbelink@dot.gov'),
(22, 'Sacramento - 898 Birch St', 'Pier Rubberts', '+7 109 876-54-32', 'prubbertsl@mtv.com'),
(23, 'New York - 123 Main St', 'Kelsey Toffolo', '+7 000 111-22-33', 'ktoffolom@bloglovin.com'),
(24, 'Raleigh - 898 Walnut St', 'Reggie Tallis', '+7 333 444-55-66', 'rtallisn@msu.edu'),
(25, 'Seattle - 303 Cedar St', 'Cecelia Ygoe', '+7 210 987-65-43', 'cygoeo@godaddy.com'),
(26, 'Kansas City - 565 Maple St', 'Myrwyn Forge', '+7 109 876-54-32', 'mforgep@tiny.cc'),
(27, 'Indianapolis - 676 Cedar St', 'Noami Peare', '+7 555 666-77-88', 'npeareq@nymag.com'),
(28, 'Houston - 808 Oak St', 'Tierney Trengrouse', '+7 765 432-10-98', 'ttrengrouser@spiegel.de'),
(29, 'Omaha - 565 Walnut St', 'Rene Waters', '+7 678 901-23-45', 'rwaterss@acquirethisname.com'),
(30, 'Charlotte - 666 Oak St', 'Emera Humberston', '+7 987 654-32-10', 'ehumberstont@barnesandnoble.com');
-- dates
INSERT INTO dates (date_id, date, subject_id) VALUES
(1, '2026-03-20 20:49:23', 5),
(2, '2025-04-11 22:02:19', 1),
(3, '2026-04-08 22:29:52', 10),
(4, '2025-04-21 13:24:17', 7),
(5, '2026-03-08 04:38:13', 16),
(6, '2025-04-15 17:16:44', 14),
(7, '2026-04-22 06:53:59', 2),
(8, '2026-04-14 06:30:20', 10),
(9, '2025-04-07 02:33:22', 17),
(10, '2025-04-19 06:01:18', 7),
(11, '2025-03-07 23:12:32', 6),
(12, '2025-03-27 19:34:10', 16),
(13, '2026-04-24 21:30:02', 9),
(14, '2025-04-13 05:55:21', 7),
(15, '2026-04-26 05:20:52', 16),
(16, '2026-03-16 06:12:02', 16),
(17, '2025-04-17 03:09:39', 4),
(18, '2025-03-27 21:18:28', 3),
(19, '2025-03-11 19:21:55', 3),
(20, '2025-03-05 06:53:06', 3),
(21, '2025-03-29 14:45:36', 12),
(22, '2026-04-16 04:56:04', 10),
(23, '2026-04-06 18:33:14', 15),
(24, '2026-03-16 15:53:26', 7),
(25, '2025-04-19 15:16:48', 5),
(26, '2025-04-25 07:52:57', 5),
(27, '2025-04-05 13:58:15', 10),
(28, '2026-03-22 16:35:59', 2),
(29, '2025-03-27 19:18:38', 16),
(30, '2026-03-22 04:32:16', 7);
-- students
INSERT INTO students (student_id, student_email, student_name, date_of_birth, grade, parent_name, number) VALUES
(1, 'acrossman0@flavors.me', 'Ashely Caven', '2015-02-07', 4, 'Arabelle Crossman', '+7 456 789-01-23'),
(2, 'bbromehead1@patch.com', 'Morey Lemm', '2008-07-22', 11, 'Brennan Bromehead', '+7 000 999-88-77'),
(3, 'ehoulton2@hostgator.com', 'Benedetto Ewert', '2010-04-09', 9, 'Ezekiel Houlton', '+7 666 555-44-33'),
(4, 'mhechlin3@macromedia.com', 'Constantin Gniewosz', '2008-06-29', 11, 'Minta Hechlin', '+7 777 666-55-44'),
(5, 'fzorzetti4@unicef.org', 'Garrik Bamling', '2012-11-28', 7, 'Fabian Zorzetti', '+7 333 444-55-66'),
(6, 'syarmouth5@npr.org', 'Maddie Welland', '2011-10-20', 8, 'Sheffie Yarmouth', '+7 333 444-55-66'),
(7, 'rnettle6@toplist.cz', 'Haley Kruse', '2018-01-17', 1, 'Rhody Nettle', '+7 111 222-33-44'),
(8, 'dgillmor7@smugmug.com', 'West Urvoy', '2014-10-14', 5, 'Dorise Gillmor', '+7 789 012-34-56'),
(9, 'dwillshee8@toplist.cz', 'Lorianna Larmuth', '2009-02-19', 10, 'Der Willshee', '+7 333 222-11-00'),
(10, 'smclevie9@hibu.com', 'Keefe Brookton', '2012-09-02', 7, 'Sandro McLevie', '+7 888 999-00-11'),
(11, 'floudena@sphinn.com', 'Ryon Elfleet', '2017-11-23', 2, 'Friedrick Louden', '+7 789 012-34-56'),
(12, 'aocallaghanb@house.gov', 'Delilah Sollett', '2009-04-13', 10, 'Alfons O''Callaghan', '+7 678 901-23-45'),
(13, 'epalleskec@bbb.org', 'Eachelle Mc Ilory', '2008-06-14', 11, 'Eduardo Palleske', '+7 678 901-23-45'),
(14, 'rwardingleyd@cloudflare.com', 'Joshia Phethean', '2018-01-14', 1, 'Red Wardingley', '+7 234 567-89-01'),
(15, 'ceagelle@sakura.ne.jp', 'Huntington MacGovern', '2014-12-10', 5, 'Carlita Eagell', '+7 555 666-77-88'),
(16, 'aswornef@jiathis.com', 'Wallis McLanachan', '2009-08-08', 10, 'Arthur Sworne', '+7 555 666-77-88'),
(17, 'cpoling@google.ca', 'Jeffie Firby', '2017-06-14', 2, 'Carrol Polin', '+7 654 321-09-87'),
(18, 'bgaynorh@discovery.com', 'Elly Bleyman', '2017-04-25', 2, 'Bernette Gaynor', '+7 110 999-88-77'),
(19, 'jvani@techcrunch.com', 'Shep Loffill', '2017-04-25', 2, 'Josias Van Halen', '+7 333 222-11-00'),
(20, 'etolomellij@mysql.com', 'Maisey Blacklidge', '2017-11-23', 2, 'Elvira Tolomelli', '+7 987 654-32-10'),
(21, 'cgennyk@go.com', 'Robbyn Izakof', '2015-02-07', 4, 'Celestina Genny', '+7 666 555-44-33'),
(22, 'afynanl@amazon.de', 'Cammi Arnaez', '2008-07-09', 11, 'Andrea Fynan', '+7 109 876-54-32'),
(23, 'aemmattm@moonfruit.com', 'Robbert Winspear', '2017-04-25', 2, 'Ase Emmatt', '+7 666 777-88-99'),
(24, 'hbitchenon@washington.edu', 'Dora Gabbitus', '2015-10-06', 4, 'Hendrick Bitcheno', '+7 222 333-44-55'),
(25, 'sbudgetto@businessweek.com', 'Gayle Ewington', '2010-10-08', 9, 'Sybila Budgett', '+7 998 877-66-55'),
(26, 'scalladinep@webeden.co.uk', 'Dominga Jickles', '2016-08-01', 3, 'Shawna Calladine', '+7 332 211-00-99'),
(27, 'ddunkleyq@rediff.com', 'Brynna Picknett', '2015-10-06', 4, 'Dukie Dunkley', '+7 456 789-01-23'),
(28, 'kcatfordr@netlog.com', 'Bernelle Brittain', '2011-02-18', 8, 'Kiersten Catford', '+7 789 012-34-56'),
(29, 'hdimbylows@washingtonpost.com', 'Daniele Bilfoot', '2014-12-28', 5, 'Hussein Dimbylow', '+7 901 234-56-78'),
(30, 'asaylest@prlog.org', 'Brandon Mitchard', '2016-05-22', 3, 'Aubrette Sayles', '+7 444 555-66-77');
INSERT INTO olympics (date_id, place_id) VALUES
(7, 12), (15, 23), (3, 8), (22, 17), (9, 30),
(28, 5), (14, 19), (6, 11), (17, 2), (25, 14),
(10, 25), (1, 7), (19, 9), (4, 22), (12, 3),
(23, 16), (8, 28), (30, 1), (5, 18), (16, 29),
(20, 6), (11, 24), (2, 15), (27, 10), (13, 4),
(24, 27), (18, 13), (21, 20), (26, 21), (29, 26),
(7, 1), (15, 11), (3, 21), (22, 2), (9, 12),
(28, 22), (14, 3), (6, 23), (17, 4), (25, 24),
(10, 5), (1, 25), (19, 6), (4, 26), (12, 7),
(23, 27), (8, 8), (30, 28), (5, 9), (16, 29),
(20, 10), (11, 20), (2, 1), (27, 11), (13, 21),
(24, 2), (18, 12), (21, 22), (26, 3), (29, 23),
(7, 13), (15, 14), (3, 15), (22, 16), (9, 17),
(28, 18), (14, 19), (6, 20), (17, 21), (25, 22),
(10, 23), (1, 24), (19, 25), (4, 26), (12, 27),
(23, 28), (8, 29), (30, 30), (5, 1), (16, 2),
(20, 3), (11, 4), (2, 5), (27, 6), (13, 7),
(24, 8), (18, 9), (21, 10), (26, 11), (29, 12),
(7, 14), (15, 15), (3, 16), (22, 17), (9, 18),
(28, 19), (14, 20), (6, 21), (17, 22), (25, 23);
-- participations
INSERT INTO participations (participation_id, student_id, examiner_id, olympics_id, score) VALUES
(1, 5, 58, 81, 98),
(2, 11, 34, 10, 62),
(3, 4, 10, 69, 34),
(4, 21, 6, 83, 36),
(5, 16, 4, 36, 33),
(6, 25, 48, 45, 80),
(7, 6, 28, 14, 72),
(8, 25, 56, 99, 77),
(9, 20, 34, 19, 45),
(10, 1, 25, 37, 77),
(11, 5, 77, 21, 77),
(12, 26, 97, 50, 74),
(13, 7, 13, 6, 11),
(14, 25, 77, 47, 58),
(15, 4, 4, 84, 46),
(16, 19, 22, 26, 12),
(17, 30, 11, 77, 87),
(18, 5, 34, 97, 49),
(19, 3, 32, 26, 46),
(20, 17, 84, 69, 28),
(21, 6, 60, 77, 62),
(22, 17, 55, 41, 100),
(23, 22, 31, 28, 84),
(24, 5, 65, 90, 61),
(25, 5, 52, 16, 78),
(26, 25, 82, 9, 67),
(27, 8, 13, 29, 14),
(28, 3, 18, 13, 97),
(29, 12, 75, 68, 81),
(30, 12, 34, 16, 74),
(31, 9, 20, 18, 78),
(32, 17, 78, 63, 62),
(33, 8, 7, 39, 68),
(34, 11, 12, 99, 91),
(35, 14, 1, 71, 72),
(36, 13, 84, 90, 63),
(37, 4, 57, 68, 86),
(38, 13, 53, 92, 56),
(39, 19, 88, 18, 34),
(40, 13, 39, 81, 11),
(41, 19, 54, 10, 32),
(42, 25, 85, 12, 32),
(43, 14, 70, 5, 99),
(44, 13, 72, 90, 95),
(45, 18, 36, 84, 38),
(46, 5, 84, 100, 37),
(47, 2, 2, 19, 27),
(48, 21, 86, 92, 90),
(49, 21, 92, 3, 71),
(50, 14, 33, 10, 60),
(51, 8, 54, 86, 87),
(52, 22, 62, 70, 21),
(53, 24, 79, 93, 12),
(54, 27, 62, 85, 77),
(55, 24, 19, 78, 47),
(56, 22, 99, 94, 32),
(57, 17, 37, 58, 11),
(58, 3, 40, 11, 42),
(59, 16, 35, 96, 68),
(60, 8, 72, 74, 96),
(61, 5, 66, 27, 64),
(62, 22, 48, 71, 18),
(63, 12, 6, 36, 89),
(64, 8, 57, 66, 96),
(65, 26, 91, 44, 49),
(66, 8, 9, 90, 62),
(67, 30, 20, 91, 85),
(68, 27, 28, 90, 28),
(69, 5, 56, 18, 79),
(70, 6, 21, 77, 41),
(71, 5, 33, 52, 85),
(72, 12, 55, 20, 94),
(73, 19, 9, 50, 87),
(74, 17, 14, 73, 27),
(75, 16, 41, 97, 10),
(76, 21, 29, 8, 83),
(77, 12, 7, 63, 39),
(78, 16, 51, 85, 37),
(79, 13, 14, 18, 55),
(80, 20, 64, 88, 90),
(81, 12, 53, 32, 98),
(82, 20, 42, 55, 59),
(83, 22, 31, 96, 10),
(84, 15, 21, 65, 19),
(85, 17, 46, 57, 4),
(86, 26, 93, 61, 61),
(87, 10, 66, 62, 39),
(88, 14, 91, 27, 66),
(89, 15, 36, 47, 29),
(90, 25, 74, 23, 10),
(91, 7, 20, 85, 20),
(92, 4, 66, 66, 73),
(93, 10, 73, 81, 99),
(94, 20, 41, 53, 36),
(95, 8, 20, 59, 41),
(96, 23, 45, 52, 25),
(97, 25, 39, 52, 61),
(98, 21, 14, 40, 30),
(99, 30, 66, 59, 95),
(100, 14, 21, 26, 87);
-- appeals
INSERT INTO appeals (participation_id, examiner_id, appeal_time, previous_score) VALUES
(6, 24, '2025-03-09 00:29:53', 58),
(9, 75, '2025-03-01 03:22:39', 26),
(11, 43, '2025-03-19 21:36:23', 60),
(12, 95, '2025-03-24 16:02:51', 45),
(13, 12, '2025-03-20 23:35:47', 82),
(14, 78, '2025-03-17 13:31:17', 2),
(15, 12, '2025-03-27 00:53:54', 60),
(16, 65, '2025-03-05 05:26:31', 84),
(17, 64, '2025-03-14 12:20:03', 6),
(19, 45, '2025-03-05 00:27:38', 75),
(20, 13, '2025-03-18 10:11:38', 44),
(23, 19, '2025-03-12 22:40:31', 45),
(25, 5, '2025-03-28 13:32:43', 20),
(26, 74, '2025-03-01 11:45:30', 47),
(27, 39, '2025-03-15 08:07:24', 91),
(29, 73, '2025-03-20 16:10:24', 23),
(33, 44, '2025-03-15 11:02:19', 38),
(34, 35, '2025-03-31 13:02:47', 48),
(35, 63, '2025-03-25 18:56:15', 66),
(41, 36, '2025-03-07 18:17:01', 32),
(45, 62, '2025-03-23 19:25:45', 74),
(47, 61, '2025-03-22 10:37:16', 88),
(48, 15, '2025-03-19 08:57:32', 20),
(49, 64, '2025-03-17 10:56:30', 28),
(50, 20, '2025-03-12 14:25:12', 27),
(53, 61, '2025-03-24 19:28:00', 72),
(56, 31, '2025-03-26 02:19:17', 5),
(59, 57, '2025-03-03 08:23:52', 1),
(62, 70, '2025-03-11 03:24:38', 62),
(63, 34, '2025-03-28 00:06:29', 38),
(67, 94, '2025-03-26 20:04:54', 41),
(68, 64, '2025-03-30 03:43:01', 80),
(73, 40, '2025-03-25 04:18:55', 87),
(74, 65, '2025-03-27 13:39:38', 69),
(76, 86, '2025-03-08 08:38:12', 92),
(80, 55, '2025-03-20 22:40:41', 50),
(81, 31, '2025-03-08 05:08:48', 3),
(85, 5, '2025-03-16 23:06:32', 88),
(87, 17, '2025-03-16 15:28:48', 11),
(88, 51, '2025-03-19 02:07:43', 84),
(89, 81, '2025-03-21 10:41:52', 69),
(90, 73, '2025-03-13 11:34:52', 54),
(91, 64, '2025-03-19 15:28:27', 51),
(92, 54, '2025-03-16 06:16:45', 39),
(94, 28, '2025-03-10 07:38:06', 99),
(95, 68, '2025-03-10 13:53:34', 60),
(96, 65, '2025-03-28 09:56:48', 44),
(98, 26, '2025-03-04 10:10:11', 3),
(99, 31, '2025-03-16 19:02:27', 81),
(100, 56, '2025-03-30 21:59:20', 79);
CREATE VIEW view_dates_places_subjects AS
SELECT date, subject_name, addres
FROM dates
JOIN olympics USING(date_id)
JOIN places USING(place_id)
JOIN subjects USING(subject_id);
SELECT * FROM view_dates_places_subjects
Telegram: DB developers channel