SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE schema LECTORIUM; CREATE table LECTORIUM.EMPLOYEE( employee_id integer primary key , name_nm varchar(255) , cameraman_flg boolean , videoeditor_flg boolean ); CREATE table LECTORIUM.TEACHER( teacher_id integer primary key , teacher_nm varchar(255) , agree_flg boolean ); CREATE table LECTORIUM.CAMERA( camera_id integer primary key , model_nm varchar(255) , state_flg boolean ); CREATE table LECTORIUM.MICROPHONE( microphone_id integer primary key , model_nm varchar(255) , state_flg boolean ); CREATE table LECTORIUM.CAPTURE_CARD( capture_card_id integer primary key , model_nm varchar(255) , state_flg boolean ); CREATE table LECTORIUM.COMPUTER( computer_id integer primary key , model_nm varchar(255) , state_flg boolean ); CREATE table LECTORIUM.ROOM( room_id integer primary key , room_nm varchar(255) , camera_id integer references LECTORIUM.CAMERA(camera_id) , microphone_id integer references LECTORIUM.MICROPHONE(microphone_id) , capture_card_id integer references LECTORIUM.CAPTURE_CARD(capture_card_id) , computer_id integer references LECTORIUM.COMPUTER(computer_id) ); CREATE table LECTORIUM.SUBJECT( subject_id integer primary key , subject_nm varchar(255) , course_no integer , room_id integer references LECTORIUM.ROOM(room_id) , teacher_id integer references LECTORIUM.TEACHER(teacher_id) ); CREATE table LECTORIUM.LECTURE( lecture_id integer primary key , subject_id integer references LECTORIUM.SUBJECT(subject_id) , room_id integer references LECTORIUM.ROOM(room_id) , shooting_dt date , cameraman_nm varchar(255) , videoeditor_nm varchar(255) , ready_flg boolean ); CREATE table LECTORIUM.LECTURE_X_EMPLOYEE( employee_id integer references LECTORIUM.EMPLOYEE(employee_id) , lecture_id integer references LECTORIUM.LECTURE(lecture_id) , role_nm varchar(255) , start_dttm timestamp , end_dttm timestamp ); INSERT INTO LECTORIUM.EMPLOYEE VALUES (1, 'Иванов Максим Игоревич', TRUE, TRUE) , (2, 'Петров Иван Ильич', TRUE, FALSE) , (3, 'Павлов Андрей Алексеевич', FALSE, TRUE) , (4, 'Дмитриев Дмитрий Дмитревич', TRUE, TRUE) , (5, 'Ульянова Мария Евгеньевна', TRUE, FALSE) , (6, 'Бурундуков Михаил Алексеевич', TRUE, TRUE) , (7, 'Губкин Алексей Ильич', TRUE, TRUE) , (8, 'Шаронов Арон Аронович', FALSE, TRUE) , (9, 'Златопольский Григорий Евгеньевич', TRUE, FALSE) , (10, 'Родригес Марио Леонович', FALSE, TRUE); INSERT INTO LECTORIUM.TEACHER VALUES (1, 'Андреев Борис Викторович', TRUE) , (2, 'Гаврилин Дмитрий Евгеньевич', TRUE) , (3, 'Ёршик Жанна Зиновьевна', TRUE) , (4, 'Иод Иоахим Кириллович', TRUE) , (5, 'Лампель Максим Николаевич', TRUE) , (6, 'Остроградский Павел Романович', TRUE) , (7, 'Савельева Татьяна Устиновна', TRUE) , (8, 'Франкенштейн Харитон Харитонович', TRUE) , (9, 'Целков Роман Романович', TRUE) , (10, 'Ястреб Эмиль Юрьевич', TRUE); INSERT INTO LECTORIUM.CAMERA VALUES (1, 'Pony HD100500', TRUE) , (2, 'Pony HD100600', TRUE) , (3, 'Monosonic Ultra4', TRUE) , (4, 'Monosonic Ultra5', TRUE) , (5, 'Nikonor 666HD', TRUE) , (6, 'Nikonor 666HD', TRUE) , (7, 'Pony HD100500', TRUE) , (8, 'Pony HD100600', FALSE) , (9, 'Zenith 2', TRUE) , (10, 'Zenith 2', FALSE); INSERT INTO LECTORIUM.MICROPHONE VALUES (1, 'Hahaheiser 333', TRUE) , (2, 'Hahaheiser 343', TRUE) , (3, 'Hahaheiser 333', TRUE) , (4, 'Hahaheiser 343', TRUE) , (5, 'Hahaheiser 666', TRUE) , (6, 'Hahaheiser 343', TRUE) , (7, 'Hahaheiser 666', TRUE) , (8, 'Hahaheiser 333', FALSE) , (9, 'Hahaheiser 343', TRUE) , (10, 'Hahaheiser 343', FALSE); INSERT INTO LECTORIUM.CAPTURE_CARD VALUES (1, 'AreYouMedia Portable 1', TRUE) , (2, 'AreYouMedia Portable 1', TRUE) , (3, 'AreYouMedia Portable 4', TRUE) , (4, 'AreYouMedia Portable 4', TRUE) , (5, 'AreYouMedia Portable 1', TRUE) , (6, 'AreYouMedia Portable 4', TRUE) , (7, 'AreYouMedia Portable 1', TRUE) , (8, 'AreYouMedia Portable 3', FALSE) , (9, 'AreYouMedia Portable 1', TRUE) , (10, 'AreYouMedia Portable 2', TRUE); INSERT INTO LECTORIUM.COMPUTER VALUES (1, 'Lenoldo 640', TRUE) , (2, 'Lenoldo 950', TRUE) , (3, 'Lenoldo 960', TRUE) , (4, 'Lenoldo 640', TRUE) , (5, 'Lenoldo 330', TRUE) , (6, 'Lenoldo 110', TRUE) , (7, 'Lenoldo 640', TRUE) , (8, 'Lenoldo 114', TRUE) , (9, 'Lenoldo 252', TRUE) , (10, 'Lenoldo 252', TRUE); INSERT INTO LECTORIUM.ROOM VALUES (1, 'Арктика 225', 1, 2, 3, 4) , (2, 'Арктика 227', 5, 6, 7, 8) , (3, 'Антарктика 325', 9, 10, 1, 2) , (4, 'Антарктика 327', 3, 4, 5, 6) , (5, 'Африка 108', 7, 8, 9, 10) , (6, 'Африка 109', 2, 3, 4, 5) , (7, 'Азия 222', 6, 7, 8, 9) , (8, 'Европа 666', 10, 1, 2, 3) , (9, 'Австралия 112', 4, 5, 6, 7) , (10, 'Америка 911', 8, 9, 10, 1); INSERT INTO LECTORIUM.SUBJECT VALUES (1, 'Алгебра и геометрия', 1, 2, 3) , (2, 'Алгоритмы и структуры данных', 1, 6, 7) , (3, 'Математический анализ', 1, 10, 1) , (4, 'Теория вероятностей', 2, 4, 5) , (5, 'История компьютерных технологий', 2, 8, 9) , (6, 'Многопоточная синхронизация', 2, 3, 4) , (7, 'Машинное обучение', 3, 7, 8) , (8, 'Математическая статистика', 3, 1, 2) , (9, 'Компьютерная графика', 4, 5, 6) , (10, 'Философия', 4, 9, 10); INSERT INTO LECTORIUM.LECTURE VALUES (1, 1, 2, '2022-02-02', 'Петров Иван Ильич', 'Иванов Максим Игоревич', TRUE) , (2, 1, 2, '2022-02-09', 'Петров Иван Ильич', 'Иванов Максим Игоревич', FALSE) , (3, 2, 6, '2022-02-02', 'Дмитриев Дмитрий Дмитревич', 'Павлов Андрей Алексеевич', TRUE) , (4, 2, 6, '2022-02-09', 'Дмитриев Дмитрий Дмитревич', 'Павлов Андрей Алексеевич', TRUE) , (5, 3, 10, '2022-02-02', 'Дмитриев Дмитрий Дмитревич', 'Павлов Андрей Алексеевич', TRUE) , (6, 3, 10, '2022-02-04', 'Дмитриев Дмитрий Дмитревич', 'Павлов Андрей Алексеевич', TRUE) , (7, 4, 4, '2022-02-03', 'Бурундуков Михаил Алексеевич', 'Бурундуков Михаил Алексеевич', TRUE) , (8, 4, 4, '2022-02-10', 'Бурундуков Михаил Алексеевич', 'Бурундуков Михаил Алексеевич', TRUE) , (9, 5, 8, '2022-02-03', 'Ульянова Мария Евгеньевна', 'Бурундуков Михаил Алексеевич', TRUE) , (10, 5, 8, '2022-02-10', 'Ульянова Мария Евгеньевна', 'Бурундуков Михаил Алексеевич', TRUE); INSERT INTO LECTORIUM.LECTURE_X_EMPLOYEE VALUES (2, 1, 'Оператор', '2022-02-02 12:20:00', '2022-02-02 13:45:00') , (2, 2, 'Оператор', '2022-02-09 12:20:00', '2022-02-09 13:45:00') , (1, 1, 'Монтажёр', '2022-02-02 22:00:00', '2022-02-02 23:00:00') , (1, 2, 'Монтажёр', NULL, NULL) , (4, 3, 'Оператор', '2022-02-02 13:55:00', '2022-02-02 15:20:00') , (4, 5, 'Оператор', '2022-02-02 15:30:00', '2022-02-02 16:55:00') , (4, 6, 'Оператор', '2022-02-04 13:55:00', '2022-02-04 15:20:00') , (4, 4, 'Оператор', '2022-02-09 13:55:00', '2022-02-09 15:20:00') , (3, 3, 'Монтажёр', '2022-02-02 22:55:00', '2022-02-02 23:45:00') , (3, 4, 'Монтажёр', '2022-02-09 22:50:00', '2022-02-09 23:40:00') , (3, 5, 'Монтажёр', '2022-02-10 22:50:00', '2022-02-10 23:40:00') , (3, 6, 'Монтажёр', '2022-02-11 22:50:00', '2022-02-11 23:40:00') , (6, 7, 'Оператор', '2022-02-03 09:00:00', '2022-02-03 10:25:00') , (6, 8, 'Оператор', '2022-02-10 09:00:00', '2022-02-10 10:25:00') , (6, 7, 'Монтажёр', '2022-02-11 09:00:00', '2022-02-11 10:25:00') , (6, 8, 'Монтажёр', '2022-02-11 11:00:00', '2022-02-11 12:25:00') , (6, 9, 'Монтажёр', '2022-02-11 13:00:00', '2022-02-11 14:25:00') , (6, 10, 'Монтажёр', '2022-02-11 15:00:00', '2022-02-11 16:25:00') , (5, 9, 'Оператор', '2022-02-03 10:45:00', '2022-02-03 12:10:00') , (5, 10, 'Оператор', '2022-02-10 10:45:00', '2022-02-10 12:10:00'); -- CRUD: Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE) -- Преподаватели устроили забастовку UPDATE LECTORIUM.TEACHER SET agree_flg = FALSE WHERE agree_flg = TRUE; -- Преподаватели перестали бастовать в обмен на то, чтобы все снимались UPDATE LECTORIUM.TEACHER set agree_flg = TRUE WHERE agree_flg = FALSE; -- Проверим, что действительно все согласны SELECT teacher_nm, agree_flg FROM LECTORIUM.TEACHER; -- На одну из кафедр пришли четверокурсники... INSERT INTO LECTORIUM.TEACHER VALUES (11, 'Неизвестный Никита Алексеевич', FALSE) , (12, 'Известный Алексей Никитич', FALSE); -- ..которые ушли почти сразу же DELETE FROM LECTORIUM.TEACHER WHERE teacher_id = 11 AND teacher_nm = 'Неизвестный Никита Алексеевич'; DELETE FROM LECTORIUM.TEACHER WHERE teacher_id = 12 AND teacher_nm = 'Известный Алексей Никитич'; -- Смотрим на камеры SELECT * FROM LECTORIUM.CAMERA; -- Смотрим на микрофоны SELECT * FROM LECTORIUM.MICROPHONE; -- Смотрим на карты захвата SELECT * FROM LECTORIUM.CAPTURE_CARD; -- Смотрми на компьютеры SELECT * FROM LECTORIUM.COMPUTER; -- SELECT queries -- 1: GROUP BY + HAVING SELECT model_nm, COUNT(model_nm) AS model_cnt FROM LECTORIUM.CAMERA GROUP BY model_nm HAVING COUNT(model_nm) >= 2; -- 2: ORDER BY SELECT microphone_id, model_nm FROM LECTORIUM.MICROPHONE WHERE state_flg = TRUE ORDER BY model_nm; -- 3: agg. function + PARTITION BY SELECT DISTINCT start_dttm::date AS start_day_dttm , MAX(end_dttm) OVER(PARTITION BY start_dttm::date) FROM LECTORIUM.LECTURE_X_EMPLOYEE WHERE start_dttm::date IS NOT NULL; -- 4 ranking function + ORDER BY SELECT DISTINCT cameraman_nm, start_rank_num FROM ( SELECT cameraman_nm , DENSE_RANK() OVER(ORDER BY shooting_dt) AS start_rank_num FROM LECTORIUM.LECTURE ORDER BY start_rank_num ) RANKED_CAMERAMAN WHERE start_rank_num = 1; -- 5 offset function + PARTITION BY + ORDER BY WITH LECTURE_INFO AS ( SELECT * FROM LECTORIUM.LECTURE_X_EMPLOYEE le JOIN LECTORIUM.LECTURE l ON le.lecture_id = l.lecture_id WHERE role_nm = 'Оператор' ) SELECT start_dttm , course_no , subject_nm as current_lecture_nm , COALESCE(LEAD(subject_nm) OVER(PARTITION BY course_no, start_dttm::date ORDER BY start_dttm), '') AS next_lecture_nm FROM LECTURE_INFO li JOIN LECTORIUM.SUBJECT s ON li.subject_id = s.subject_id; -- 6: all types of functions WITH LECTURE_INFO AS ( SELECT * FROM LECTORIUM.LECTURE_X_EMPLOYEE le JOIN LECTORIUM.LECTURE l ON le.lecture_id = l.lecture_id WHERE role_nm = 'Оператор' ) SELECT start_dttm::date AS lecture_date_dttm , course_no , COUNT(*) OVER(PARTITION BY course_no, start_dttm::date) AS lectures_cnt , RANK() OVER(PARTITION BY course_no, start_dttm::date ORDER BY start_dttm) AS lecture_order_num , subject_nm as current_lecture_nm , COALESCE(LEAD(subject_nm) OVER(PARTITION BY course_no, start_dttm::date ORDER BY start_dttm), '') AS next_lecture_nm FROM LECTURE_INFO li JOIN LECTORIUM.SUBJECT s ON li.subject_id = s.subject_id; -- Indices CREATE INDEX ON LECTORIUM.EMPLOYEE(employee_id); CREATE INDEX ON LECTORIUM.TEACHER(teacher_id); CREATE INDEX ON LECTORIUM.CAMERA(camera_id); CREATE INDEX ON LECTORIUM.MICROPHONE(microphone_id); CREATE INDEX ON LECTORIUM.CAPTURE_CARD(capture_card_id); CREATE INDEX ON LECTORIUM.COMPUTER(computer_id); CREATE INDEX ON LECTORIUM.ROOM(room_id); CREATE INDEX ON LECTORIUM.SUBJECT(subject_id); CREATE INDEX ON LECTORIUM.LECTURE(lecture_id); CREATE INDEX ON LECTORIUM.LECTURE_X_EMPLOYEE(employee_id, lecture_id); -- Views -- 1: masking employees' names in shooting info CREATE VIEW V_LECTURE_MASKED_EMPLOYEE AS SELECT lecture_id, shooting_dt , CONCAT(SUBSTRING(cameraman_nm, 1, 3), '***') AS cameraman_hidden_nm , CONCAT(SUBSTRING(videoeditor_nm, 1, 3), '***') AS videoeditor_hidden_nm FROM LECTORIUM.LECTURE; -- Let's check that it works SELECT * FROM V_LECTURE_MASKED_EMPLOYEE; -- 2: masking teachers' names CREATE VIEW V_TEACHER_MASKED AS SELECT teacher_id , CONCAT(SUBSTRING(teacher_nm, 1, 3), '***') AS teacher_hidden_nm FROM LECTORIUM.TEACHER; SELECT * FROM V_TEACHER_MASKED; -- 3: masking employees' names in the total list CREATE VIEW V_EMPLOYEE_MASKED AS SELECT employee_id , CONCAT(SUBSTRING(name_nm, 1, 3), '***') AS name_hidden_nm FROM LECTORIUM.EMPLOYEE; SELECT * FROM V_EMPLOYEE_MASKED; -- 4: stats which show the time that our employees spent CREATE VIEW V_EMPLOYEE_STAT AS SELECT name_nm, role_nm, SUM(end_dttm - start_dttm) AS spent_time_tm FROM LECTORIUM.EMPLOYEE e JOIN LECTORIUM.LECTURE_X_EMPLOYEE le ON e.employee_id = le.employee_id GROUP BY e.employee_id, le.role_nm; SELECT * FROM V_EMPLOYEE_STAT; -- 5: room info CREATE VIEW V_ROOM_INFO AS SELECT room_nm , ca.model_nm AS camera_nm , mi.model_nm AS microphone_nm , cc.model_nm AS capture_card_nm , co.model_nm AS computer_nm FROM LECTORIUM.ROOM r JOIN LECTORIUM.CAMERA ca ON r.camera_id = ca.camera_id JOIN LECTORIUM.MICROPHONE mi ON r.microphone_id = mi.microphone_id JOIN LECTORIUM.CAPTURE_CARD cc ON r.capture_card_id = cc.capture_card_id JOIN LECTORIUM.COMPUTER co ON r.computer_id = co.computer_id; SELECT * FROM V_ROOM_INFO; -- 6: employees and subjects info CREATE VIEW V_EMPLOYEE_SUBJECT_INFO AS SELECT DISTINCT e.name_nm, s.subject_nm, le.role_nm FROM LECTORIUM.EMPLOYEE e JOIN LECTORIUM.LECTURE_X_EMPLOYEE le ON e.employee_id = le.employee_id JOIN LECTORIUM.LECTURE l ON le.lecture_id = l.lecture_id JOIN LECTORIUM.SUBJECT s ON l.subject_id = s.subject_id ORDER BY e.name_nm; SELECT * FROM V_EMPLOYEE_SUBJECT_INFO;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear