SQL 2017 CSC Course on Stepik (Week 5, task 1)

db2017_w5hw1.sql

DROP TABLE IF EXISTS Conference;
DROP TABLE IF EXISTS ConferenceEvent;
DROP TABLE IF EXISTS Paper;

-- Серия ежегодных конференций

CREATE TABLE Conference (
  id   INT PRIMARY KEY,
  name TEXT
);

-- "Событие" -- конференция в конкретном году

CREATE TABLE ConferenceEvent (
  id               INT PRIMARY KEY,
  conference_id    INT REFERENCES Conference,
  year             INT,
  total_papers     INT,
  accepted_papers  INT,
  acceptance_ratio NUMERIC(3, 2),
  UNIQUE (conference_id, year)
);

ALTER TABLE ConferenceEvent DROP COLUMN total_papers;
ALTER TABLE ConferenceEvent DROP COLUMN accepted_papers;
ALTER TABLE ConferenceEvent DROP COLUMN acceptance_ratio;

CREATE TABLE Paper(
  id INT PRIMARY KEY,
  event_id INT REFERENCES ConferenceEvent,
  title TEXT,
  accepted BOOLEAN);

INSERT INTO Conference VALUES (1, 'CAFE');
INSERT INTO Conference VALUES (2, 'DEAD');
INSERT INTO Conference VALUES (3, 'BEEF');

INSERT INTO ConferenceEvent VALUES (1, 1, 2015);
INSERT INTO ConferenceEvent VALUES (2, 2, 2015);
INSERT INTO ConferenceEvent VALUES (3, 3, 2014);
INSERT INTO ConferenceEvent VALUES (4, 3, 1954);

INSERT INTO Paper VALUES (1, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (2, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (3, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (4, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (5, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (6, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (7, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (8, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (9, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (10, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (11, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (12, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (13, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (14, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (15, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (16, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (17, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (18, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (19, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (20, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (21, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (22, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (23, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (24, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (25, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (26, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (27, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (28, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (29, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (30, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (31, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (32, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (33, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (34, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (35, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (36, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (37, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (38, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (39, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);
INSERT INTO Paper VALUES (40, random() * 3 + 1, 'Paper' || to_char((random() * 100 + 1)::INT, '999'), random()::INT::BOOLEAN);