111

CREATE TABLE hostels (id_corp int, name_hostel text, number_room int);

CREATE TABLE faculties (id_fac int, name_fac text, lastname_dean text);

CREATE TABLE groups (id_group int, name_group text, course int CHECK (course>0) CHECK (course<=6));

CREATE TABLE students (id_student int, firstname text, lastname text, address text, scholarship int CHECK (scholarship>=750) CHECK (scholarship<=3500));

ALTER TABLE faculties ADD groupIds int ARRAY;

ALTER TABLE groups ADD studentIds int ARRAY;

CREATE TABLE results (id_student int PRIMARY KEY REFERENCES students, firstname text , lastname text, name_fac text , name_group text , name_hostel text, number_room int);

////////////////////////////////////////////////

BEGIN;

INSERT INTO results (id_student, firstname, lastname) SELECT id_student, firstname, lastname FROM students ON CONFLICT DO NOTHING;

UPDATE results SET name_group = (SELECT name_group FROM groups WHERE id_student = ANY (studentIds));

UPDATE results SET name_fac = (SELECT name_fac FROM faculties WHERE (SELECT id_group FROM groups WHERE id_student = ANY (studentIds)) = ANY (groupIds));

UPDATE results SET 

name_hostel = (SELECT name_hostel FROM hostels WHERE name_hostel = split_part((SELECT address FROM students WHERE results.id_student = id_student), ' ', 1)),

number_room = to_number(split_part((SELECT address FROM students WHERE results.id_student = id_student), ' ', 2), '99');

COMMIT;