SQLize | PHPize | SQLtest

CREATE schema University; -- Информация о группе CREATE table University.GROUP ( group_no INTEGER NOT NULL PRIMARY KEY, basis VARCHAR(255) NOT NULL, year_in INTEGER NOT NULL, year_out INTEGER NOT NULL ); -- Информация о студенте CREATE table University.STUDENT ( student_id SERIAL PRIMARY KEY, first_nm VARCHAR(255) NOT NULL, second_nm VARCHAR(255) NOT NULL, birth_dt DATE, group_no INTEGER NOT NULL, FOREIGN KEY (group_no) REFERENCES University.GROUP(group_no) ); --Информация о кафедре CREATE table University.CHAIR ( chair_id SERIAL PRIMARY KEY, chair_nm VARCHAR(255) NOT NULL, opening_year INTEGER NOT NULL ); --Информация о предмете CREATE table University.SUBJECT( subject_id SERIAL PRIMARY KEY, subject_nm VARCHAR(255) NOT NULL, sem_start INTEGER CHECK (sem_start > 0 and sem_start <= 12), sem_end INTEGER CHECK (sem_end >= sem_start and sem_end <= 12), chair_id INTEGER NOT NULL, FOREIGN KEY (chair_id) REFERENCES University.CHAIR(chair_id) ); --Информация о преподавателе CREATE table University.TEACHER ( teacher_id SERIAL PRIMARY KEY, first_nm VARCHAR(255) NOT NULL, second_nm VARCHAR(255) NOT NULL, salary INTEGER CHECK(salary > 0) ); --Таблица-связка студент - предмет CREATE table University.STUDENT_x_SUBJECT ( student_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, valid_from DATE NOT NULL, exam_form VARCHAR(255) CHECK(exam_form = 'устный экзамен' OR exam_form = 'диф. зачет'), mark INTEGER CHECK(mark > 0 AND mark < 11), PRIMARY KEY (student_id, subject_id, valid_from), FOREIGN KEY (student_id) REFERENCES University.STUDENT(student_id), FOREIGN KEY (subject_id) REFERENCES University.Subject(subject_id) ); --Таблица-связка предмет-преподаватель CREATE table University.SUBJECT_x_TEACHER ( subject_id INTEGER NOT NULL, teacher_id INTEGER NOT NULL, PRIMARY KEY(subject_id, teacher_id), FOREIGN KEY (subject_id) REFERENCES University.Subject(subject_id), FOREIGN KEY (teacher_id) REFERENCES University.TEACHER(teacher_id) ); --Таблица-связка преподаватель-кафедра CREATE table University.TEACHER_x_CHAIR ( teacher_id INTEGER NOT NULL, chair_id INTEGER NOT NULL, PRIMARY KEY(teacher_id, chair_id), FOREIGN KEY (teacher_id) REFERENCES University.TEACHER(teacher_id), FOREIGN KEY (chair_id) REFERENCES University.CHAIR(chair_id) ); -- DDL скрипты на ALTER ALTER TABLE University.STUDENT ADD school_name VARCHAR(255); ALTER TABLE University.STUDENT DROP school_name; -- DDL скрипты на DROP -- DROP TABLE University.GROUP, University.CHAIR, University.TEACHER, University.SUBJECT, University.STUDENT, -- University.STUDENT_x_SUBJECT, University.SUBJECT_x_TEACHER, University.TEACHER_x_CHAIR; -- DROP schema University; -- GROUP INSERT INTO University.GROUP VALUES (27, 'ПМИ', 2020, 2024); INSERT INTO University.GROUP VALUES (3, 'ПМФ', 2020, 2024); INSERT INTO University.GROUP VALUES (124, 'ПМИ', 2021, 2025); INSERT INTO University.GROUP VALUES (801, 'ПМФ', 2018, 2022); INSERT INTO University.GROUP VALUES (920, 'ПМИ', 2019, 2023); INSERT INTO University.GROUP VALUES (126, 'ПМИ', 2021, 2025); INSERT INTO University.Group VALUES (927, 'ПМИ', 2019, 2023); -- STUDENT INSERT INTO University.STUDENT VALUES (1, 'Александр', 'Баринов', '27/10/2003', 27); INSERT INTO University.STUDENT VALUES (2, 'Шестопалов', 'Даниил', '02/07/2003', 3); INSERT INTO University.STUDENT VALUES (3, 'Давид', 'Гвиниашвили', '23/03/2002', 124); INSERT INTO University.STUDENT VALUES (4, 'Альбина', 'Рухадзе', '10/8/2000', 801); INSERT INTO University.STUDENT VALUES (5, 'Максим', 'Тимошенко', '03/31/2002', 920); INSERT INTO University.STUDENT VALUES (6, 'Дмитрий', 'Медведев', '12/21/2002', 3); INSERT INTO University.STUDENT VALUES (7, 'Никита', 'Монченко', '2/1/2003', 126); INSERT INTO University.STUDENT VALUES (8, 'Михаил', 'Еськов', '05/16/2002', 27); INSERT INTO University.STUDENT VALUES (9, 'Владислав', 'Лазарев', '03/03/2001', 3); INSERT INTO University.STUDENT VALUES (10, 'Валерий', 'Алексинский', '08/08/2002', 927); -- CHAIR INSERT INTO University.CHAIR VALUES (1, 'высшая математика', 1974); INSERT INTO University.CHAIR VALUES (2, 'алгоритмы и технологии программирования', 1995); INSERT INTO University.CHAIR VALUES (3, 'математические основы управления', 2001); INSERT INTO University.CHAIR VALUES (4, 'дискретная математика', 1981); -- SUBJECT INSERT INTO University.SUBJECT VALUES (1, 'метамтический анализ', 1, 4, 1); INSERT INTO University.SUBJECT VALUES (2, 'алгоритмы и структуры данных', 1, 3, 2); INSERT INTO University.SUBJECT VALUES (3, 'базы данных(ПМИ)', 4, 4, 2); INSERT INTO University.SUBJECT VALUES (4, 'базы данных(ПМФ)', 1, 1, 3); INSERT INTO University.SUBJECT VALUES (5, 'Python', 2, 2, 2); INSERT INTO University.SUBJECT VALUES (6, 'ТРЯП', 3, 3, 3); INSERT INTO University.SUBJECT VALUES (7, 'основы комбинаторики и теории чисел', 1, 2, 4); INSERT INTO University.SUBJECT VALUES (8, 'теория вероятностей', 4, 5, 4); -- TEACHER INSERT INTO University.TEACHER VALUES (1, 'Никита', 'Орлов', 100000); INSERT INTO University.TEACHER VALUES (2, 'Вадим', 'Редкозубов', 120000); INSERT INTO University.TEACHER VALUES (3, 'Илья', 'Степанов', 87000); INSERT INTO University.TEACHER VALUES (4, 'Татьяна', 'Краснюк', 65000); INSERT INTO University.TEACHER VALUES (5, 'Гущин', 'Дмитрий', 50000); INSERT INTO University.TEACHER VALUES (6, 'Олег', 'Богданов', 50000); INSERT INTO University.TEACHER VALUES (7, 'Алла', 'Борисова', 71000); -- STUDENT_x_SUBJECT - версионная таблица INSERT INTO University.STUDENT_x_SUBJECT VALUES (1, 1, '05/27/2021', 'устный экзамен', 8); INSERT INTO University.STUDENT_x_SUBJECT VALUES (1, 2, '01/14/2022', 'устный экзамен', 9); INSERT INTO University.STUDENT_x_SUBJECT VALUES (1, 3, '05/18/2022', 'диф. зачет', 10); INSERT INTO University.STUDENT_x_SUBJECT VALUES (1, 5, '06/07/2021', 'диф. зачет', 10); INSERT INTO University.STUDENT_x_SUBJECT VALUES (1, 7, '05/22/2021', 'устный экзамен', 6); INSERT INTO University.STUDENT_x_SUBJECT VALUES (1, 8, '06/15/2022', 'устный экзамен', 9); INSERT INTO University.STUDENT_x_SUBJECT VALUES (2, 1, '06/22/2022', 'устный экзамен', 9); INSERT INTO University.STUDENT_x_SUBJECT VALUES (2, 2, '05/30/2021', 'диф. зачет', 9); INSERT INTO University.STUDENT_x_SUBJECT VALUES (2, 4, '01/10/2021', 'диф. зачет', 8); INSERT INTO University.STUDENT_x_SUBJECT VALUES (2, 6, '12/28/2021', 'устный экзамен', 7); INSERT INTO University.STUDENT_x_SUBJECT VALUES (2, 8, '06/24/2022', 'устный экзамен', 10); INSERT INTO University.STUDENT_x_SUBJECT VALUES (3, 1, '05/30/2022', 'устный экзамен', 4); INSERT INTO University.STUDENT_x_SUBJECT VALUES (3, 2, '01/21/2022', 'устный экзамен', 10); INSERT INTO University.STUDENT_x_SUBJECT VALUES (3, 5, '05/18/2022', 'диф. зачет', 6); INSERT INTO University.STUDENT_x_SUBJECT VALUES (3, 7, '01/10/2022', 'диф. зачет', 2); INSERT INTO University.STUDENT_x_SUBJECT VALUES (4, 1, '05/27/2020', 'устный экзамен', 6); INSERT INTO University.STUDENT_x_SUBJECT VALUES (4, 4, '01/11/2019', 'диф. зачет', 5); INSERT INTO University.STUDENT_x_SUBJECT VALUES (4, 6, '01/15/2020', 'устный экзамен', 6); INSERT INTO University.STUDENT_x_SUBJECT VALUES (5, 2, '12/26/2020', 'устный экзамен', 7); INSERT INTO University.STUDENT_x_SUBJECT VALUES (5, 5, '05/22/2020', 'диф. зачет', 9); INSERT INTO University.STUDENT_x_SUBJECT VALUES (5, 7, '01/20/2020', 'диф. зачет', 3); INSERT INTO University.STUDENT_x_SUBJECT VALUES (6, 1, '06/22/2022', 'устный экзамен', 10); INSERT INTO University.STUDENT_x_SUBJECT VALUES (6, 4, '01/10/2021', 'диф. зачет', 6); INSERT INTO University.STUDENT_x_SUBJECT VALUES (6, 2, '05/30/2021', 'диф. зачет', 5); INSERT INTO University.STUDENT_x_SUBJECT VALUES (6, 6, '12/28/2021', 'устный экзамен', 10); INSERT INTO University.STUDENT_x_SUBJECT VALUES (7, 1, '05/27/2020', 'устный экзамен', 9); INSERT INTO University.STUDENT_x_SUBJECT VALUES (7, 2, '01/21/2022', 'устный экзамен', 7); INSERT INTO University.STUDENT_x_SUBJECT VALUES (7, 5, '05/20/2022', 'диф. зачет', 2); INSERT INTO University.STUDENT_x_SUBJECT VALUES (7, 7, '01/12/2022', 'диф. зачет', 8); INSERT INTO University.STUDENT_x_SUBJECT VALUES (8, 1, '05/27/2021', 'устный экзамен', 4); INSERT INTO University.STUDENT_x_SUBJECT VALUES (8, 2, '01/14/2022', 'устный экзамен', 7); INSERT INTO University.STUDENT_x_SUBJECT VALUES (8, 3, '05/18/2022', 'диф. зачет', 8); INSERT INTO University.STUDENT_x_SUBJECT VALUES (8, 5, '06/07/2021', 'диф. зачет', 2); INSERT INTO University.STUDENT_x_SUBJECT VALUES (8, 7, '05/22/2021', 'устный экзамен', 3); INSERT INTO University.STUDENT_x_SUBJECT VALUES (8, 8, '06/15/2022', 'устный экзамен', 5); INSERT INTO University.STUDENT_x_SUBJECT VALUES (9, 1, '06/22/2022', 'устный экзамен', 6); INSERT INTO University.STUDENT_x_SUBJECT VALUES (9, 4, '01/10/2021', 'диф. зачет', 8); INSERT INTO University.STUDENT_x_SUBJECT VALUES (9, 2, '05/30/2021', 'диф. зачет', 4); INSERT INTO University.STUDENT_x_SUBJECT VALUES (9, 6, '12/28/2021', 'устный экзамен', 5); INSERT INTO University.STUDENT_x_SUBJECT VALUES (10, 2, '12/28/2020','устный экзамен', 6); INSERT INTO University.STUDENT_x_SUBJECT VALUES (10, 5, '05/20/2020', 'диф. зачет', 3); INSERT INTO University.STUDENT_x_SUBJECT VALUES (10, 7, '01/24/2020', 'диф. зачет', 2); -- Версионные записи INSERT INTO University.STUDENT_x_SUBJECT VALUES (10, 7, '02/14/2020', 'диф. зачет', 8); INSERT INTO University.STUDENT_x_SUBJECT VALUES (8, 5, '09/14/2021', 'диф. зачет', 5); INSERT INTO University.STUDENT_x_SUBJECT VALUES (7, 5, '09/12/2022', 'диф. зачет', 7); INSERT INTO University.STUDENT_x_SUBJECT VALUES (3, 7, '02/10/2022', 'диф. зачет', 4); -- SUBJECT_x_TEACHER INSERT INTO University.SUBJECT_x_TEACHER VALUES (1, 2); INSERT INTO University.SUBJECT_x_TEACHER VALUES (2, 3); INSERT INTO University.SUBJECT_x_TEACHER VALUES (3, 1); INSERT INTO University.SUBJECT_x_TEACHER VALUES (4, 1); INSERT INTO University.SUBJECT_x_TEACHER VALUES (5, 3); INSERT INTO University.SUBJECT_x_TEACHER VALUES (6, 4); INSERT INTO University.SUBJECT_x_TEACHER VALUES (7, 5); INSERT INTO University.SUBJECT_x_TEACHER VALUES (8, 5); INSERT INTO University.SUBJECT_x_TEACHER VALUES (1, 6); INSERT INTO University.SUBJECT_x_TEACHER VALUES (8, 7); -- TEACHER_x_CHAIR INSERT INTO University.TEACHER_x_CHAIR VALUES (1, 2); INSERT INTO University.TEACHER_x_CHAIR VALUES (1, 3); INSERT INTO University.TEACHER_x_CHAIR VALUES (2, 1); INSERT INTO University.TEACHER_x_CHAIR VALUES (3, 2); INSERT INTO University.TEACHER_x_CHAIR VALUES (4, 3); INSERT INTO University.TEACHER_x_CHAIR VALUES (5, 4); INSERT INTO University.TEACHER_x_CHAIR VALUES (6, 1); INSERT INTO University.TEACHER_x_CHAIR VALUES (7, 4); INSERT INTO University.TEACHER_x_CHAIR VALUES (5, 3); -- Update - в аббревиатуре CRUD соответствие U - Update UPDATE University.SUBJECT SET subject_nm = 'математический анализ' WHERE subject_id = 1; UPDATE University.TEACHER SET first_nm = 'Дмитрий', second_nm = 'Гущин' WHERE teacher_id = 5; UPDATE University.STUDENT SET birth_dt = '05/17/2002' WHERE student_id = 8; UPDATE University.CHAIR SET opening_year = '1965' WHERE chair_nm = 'высшая математика'; UPDATE University.TEACHER SET salary = '98500' WHERE teacher_id = 2; UPDATE University.TEACHER SET salary = '93400' WHERE teacher_id = 1; UPDATE University.TEACHER SET salary = '8988' WHERE teacher_id = 5; UPDATE University.TEACHER SET salary = '100200' WHERE teacher_id = 1; UPDATE University.STUDENT SET first_nm = 'Наталья' WHERE student_id = 9; UPDATE University.STUDENT_x_SUBJECT SET mark = 10 WHERE student_id = 2 AND subject_id = 1; UPDATE University.STUDENT_x_SUBJECT SET mark = 9 WHERE student_id = 3 AND subject_id = 2; UPDATE University.STUDENT_x_SUBJECT SET mark = 9 WHERE student_id = 8 AND subject_id = 3; -- Select - в аббревиатуре CRUD соответствие R - Read SELECT * FROM University.CHAIR; SELECT * FROM University.STUDENT; SELECT * FROM University.GROUP; SELECT * FROM University.TEACHER; -- Средняя оценка по всем ученикам по всем предметам SELECT AVG(mark) AS avg_mark FROM University.STUDENT_x_SUBJECT; -- Средняя зарплата преподавателей SELECT AVG(salary) AS avg_salary FROM University.TEACHER; -- Возраст самого старшего ученика в БД SELECT ('05/25/2022' - MIN(birth_dt)) / 365 AS age_oldest FROM University.STUDENT; -- Возраст самого младшего ученика в БД SELECT ('05/25/2022' - MAX(birth_dt)) / 365 AS age_youngest FROM University.STUDENT; -- Все группы в порядке возрастания номера SELECT group_no FROM University.GROUP ORDER BY group_no; -- Разница между наиб и наим зарплатой учителей SELECT MAX(salary) - MIN(salary) AS salary_diff FROM University.TEACHER; -- Delete - в аббревиатуре CRUD соответствие D - Delete -- Чтоб удалить что-то не нужное - нужно вставить что-то не нужное INSERT INTO University.TEACHER VALUES (8, 'Максим', 'Георгиев', 25000); INSERT INTO University.TEACHER VALUES (9, 'Мария', 'Долина', 33000); INSERT INTO University.GROUP VALUES (22, 'ПМИ', 2020, 2024); INSERT INTO University.STUDENT VALUES (11, 'Полина', 'Лыскова', '12/24/2001', 22); INSERT INTO University.GROUP VALUES (26, 'ПМИ', 2020, 2024); INSERT INTO University.STUDENT VALUES (12, 'Николай', 'Спицын', '12/20/2002', 26); INSERT INTO University.CHAIR VALUES (5, 'общая физика', 1966); INSERT INTO University.SUBJECT_x_TEACHER VALUES (8, 1); DELETE FROM University.TEACHER WHERE teacher_id = 8; DELETE FROM University.TEACHER WHERE teacher_id = 9; DELETE FROM University.STUDENT WHERE student_id = 11; DELETE FROM University.GROUP WHERE group_no = 22; DELETE FROM University.STUDENT WHERE student_id = 12; DELETE FROM University.GROUP WHERE group_no = 26; DELETE FROM University.STUDENT_x_SUBJECT WHERE student_id = 6 AND subject_id = 1; DELETE FROM University.STUDENT_x_SUBJECT WHERE student_id = 1 AND subject_id = 5; DELETE FROM University.CHAIR WHERE chair_id = 5; DELETE FROM University.SUBJECT_x_TEACHER WHERE subject_id = 8 AND teacher_id = 1;