SQLize Online / PHPize Online  /  SQLtest Online

DROP TABLE IF EXISTS `Мероприятие`; CREATE TABLE `Мероприятие` ( `Ид_мероприятия` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Регномер_мероприятия` CHAR (4) NOT NULL UNIQUE , `Название_мероприятия` VARCHAR (50) NULL ); INSERT INTO `Мероприятие` VALUES (DEFAULT, "м001", "Мероприятие1"), (DEFAULT, "м002", "Мероприятие2"), (DEFAULT, "м003", "Мероприятие3"), (DEFAULT, "м004", "Мероприятие4"), (DEFAULT, "м005", "Мероприятие5"); SELECT "Из1: `Мероприятие`" FROM `Мероприятие` WHERE 1=0; SELECT * FROM `Мероприятие`; DROP TABLE IF EXISTS `Вид мероприятия`; CREATE TABLE `Вид мероприятия` ( `Ид_вида_мероприятия` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Код_вида_мероприятия` CHAR (4) NOT NULL UNIQUE , `Название_вида_мероприятия` VARCHAR (50) NULL ); INSERT INTO `Вид мероприятия` VALUES (DEFAULT, "вм01", "Вид Мероприятия 1"), (DEFAULT, "вм02", "Вид Мероприятия 2"), (DEFAULT, "вм03", "Вид Мероприятия 3"), (DEFAULT, "вм04", "Вид Мероприятия 4"), (DEFAULT, "вм05", "Вид Мероприятия 5"); SELECT "Из2: `Вид мероприятия`" FROM `Вид мероприятия` WHERE 1=0; SELECT * FROM `Вид мероприятия`; DROP TABLE IF EXISTS `Место`; CREATE TABLE `Место` ( `Ид_место` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Код_место` CHAR (4) NOT NULL UNIQUE , `Название_место` VARCHAR (50) NULL ); INSERT INTO `Место` VALUES (DEFAULT, "ме01", "Место1"), (DEFAULT, "ме02", "Место2"), (DEFAULT, "ме03", "Место3"), (DEFAULT, "ме04", "Место4"), (DEFAULT, "ме05", "Место5"); SELECT "Из3: `Место`" FROM `Место` WHERE 1=0; SELECT * FROM `Место`; DROP TABLE IF EXISTS `Культответственный`; CREATE TABLE `Культответственный` ( `Ид_культответственного` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Код_культответственного` CHAR (4) NOT NULL UNIQUE , `ФИО_культответственного` VARCHAR (50) NOT NULL , `Код_должности` CHAR (4) NOT NULL , `Название_должности` VARCHAR (50) NULL ); INSERT INTO `Культответственный` VALUES (DEFAULT, "к001", "Иванов И. И.", "про", "профессср кафедры"), (DEFAULT, "к002", "Петров П. П.", "доц", "доцент кафедры"), (DEFAULT, "к003", "Сидоров С. С.", "про", "профессср кафедры"), (DEFAULT, "к004", "Николаев Н. Н.", "доц", "доцент кафедры"), (DEFAULT, "к005", "Попов П. П.", "зав", "заведующий кафедрой"); SELECT "Из4: `Культответственный`" FROM `Культответственный` WHERE 1=0; SELECT * FROM `Культответственный`; DROP TABLE IF EXISTS `Учебный_календарь`; -- MariaDB 10 — не все функции даты/времени допустимы в GENERATED ALWAYS CREATE TABLE `Учебный_календарь` ( `Код_даты` DATE NOT NULL PRIMARY KEY , `Название_даты` VARCHAR (255) GENERATED ALWAYS AS (`Код_даты`) STORED , `Сведения_даты` VARCHAR (255) GENERATED ALWAYS AS (`Код_даты`) STORED , `Код_уч_года` VARCHAR (255) GENERATED ALWAYS AS (CONCAT ( IF (MONTH (`Код_даты`) > 6, YEAR (`Код_даты`), YEAR (`Код_даты`)-1), '-', IF (MONTH (`Код_даты`) > 6, YEAR (`Код_даты`), YEAR (`Код_даты`)-1) + 1 )) STORED , `Название_уч_года` VARCHAR (255) GENERATED ALWAYS AS (CONCAT ( IF (MONTH (`Код_даты`) > 6, YEAR (`Код_даты`), YEAR (`Код_даты`)-1), '-', IF (MONTH (`Код_даты`) > 6, YEAR (`Код_даты`), YEAR (`Код_даты`)-1) + 1, ' уч. г. ')) STORED , `Код_семестра` VARCHAR (255) GENERATED ALWAYS AS (IF (MONTH (`Код_даты`) > 6, 1, 2)) STORED , `Название_семестра` VARCHAR (255) GENERATED ALWAYS AS (IF (`Код_семестра` = 1, "осенний", "весенний")) STORED , `Код_месяца` VARCHAR (255) GENERATED ALWAYS AS (MONTH (`Код_даты`)) STORED , `Название_месяца` VARCHAR (255) GENERATED ALWAYS AS (MONTH (`Код_даты`)) STORED ); INSERT INTO `Учебный_календарь` (`Код_даты`) VALUES ("2022-02-09"), ("2022-02-10"), ("2022-03-18"), ("2022-03-19"), ("2022-09-03"), ("2022-09-04"), ("2022-11-07"), ("2022-11-08"), ("2023-02-08"), ("2023-02-09"), ("2023-03-17"), ("2023-03-18"), ("2023-09-04"), ("2023-09-05"), ("2023-11-08"), ("2023-11-09"); SELECT "Из5: `Учебный_календарь`" FROM `Учебный_календарь` WHERE 1=0; SELECT * FROM `Учебный_календарь`; DROP TABLE IF EXISTS `Выступление`; CREATE TABLE `Выступление` ( `Ид_выступления` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Регномер_выступления` CHAR (4) NOT NULL UNIQUE , `Название_выступления` VARCHAR (50) NULL ); INSERT INTO `Выступление` VALUES (DEFAULT, "в001", "Выступление1"), (DEFAULT, "в002", "Выступление2"), (DEFAULT, "в003", "Выступление3"), (DEFAULT, "в004", "Выступление4"), (DEFAULT, "в005", "Выступление5"); SELECT "Из6: `Выступление`" FROM `Выступление` WHERE 1=0; SELECT * FROM `Выступление`; DROP TABLE IF EXISTS `Вид выступления`; CREATE TABLE `Вид выступления` ( `Ид_вида_выступления` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Код_вида_выступления` CHAR (4) NOT NULL UNIQUE , `Название_вида_выступления` VARCHAR (50) NULL ); INSERT INTO `Вид выступления` VALUES (DEFAULT, "вв01", "Вид выступления1"), (DEFAULT, "вв02", "Вид выступления2"), (DEFAULT, "вв03", "Вид выступления3"), (DEFAULT, "вв04", "Вид выступления4"), (DEFAULT, "вв05", "Вид выступления5"); SELECT "Из7: `Вид выступления`" FROM `Вид выступления` WHERE 1=0; SELECT * FROM `Вид выступления`; DROP TABLE IF EXISTS `Мероприятия`; CREATE TABLE `Мероприятия` ( `Из1.Ид_мероприятие` INT NOT NULL REFERENCES `Мероприятие` (`Ид_мероприятия`) , `Из2.Ид_вид_мероприятия` INT NOT NULL REFERENCES `Вид мероприятия` (`Ид_вида_мероприятия`) , `Из3.Ид_место` INT NOT NULL REFERENCES `Место` (`Ид_место`) , `Из4.Ид_культответственный` INT NOT NULL REFERENCES `Культответственный` (`Ид_культответственного`) , `Из5.Код_даты` DATE NOT NULL REFERENCES `Учебный_календарь` (`Код_даты`) , `Из6.Ид_выступление` INT NOT NULL REFERENCES `Выступление` (`Ид_выступления`) , `Из7.Ид_вида_выступления` INT NOT NULL REFERENCES `Вид выступления` (`Ид_вида_выступления`) , `Кол-во` CHAR (8) NOT NULL , `Продолжительность` CHAR (8) NOT NULL , `Основное_место (да/нет)` CHAR (3) NULL , `Нагрузка` INT NULL , CONSTRAINT `ПК\Фк1` PRIMARY KEY (`Из1.Ид_мероприятие`, `Из2.Ид_вид_мероприятия`, `Из3.Ид_место`, `Из4.Ид_культответственный`, `Из5.Код_даты`, `Из6.Ид_выступление`, `Из7.Ид_вида_выступления`, `Кол-во`, `Продолжительность`) ); INSERT INTO `Мероприятия` SET `Из1.Ид_мероприятие` = (SELECT `Ид_мероприятия` FROM `Мероприятие` WHERE `Регномер_мероприятия` = "м001"), `Из2.Ид_вид_мероприятия` = (SELECT `Ид_вида_мероприятия` FROM `Вид мероприятия` WHERE `Код_вида_мероприятия` = "вм01"), `Из3.Ид_место` = (SELECT `Ид_место` FROM `Место` WHERE `Код_место` = "ме01"), `Из4.Ид_культответственный` = (SELECT `Ид_культответственного` FROM `Культответственный` WHERE `Код_культответственного` = "к001"), `Из5.Код_даты` = "2023-02-09", `Из6.Ид_выступление` = (SELECT `Ид_выступления` FROM `Выступление` WHERE `Регномер_выступления` = "в001"), `Из7.Ид_вида_выступления` = (SELECT `Ид_вида_выступления` FROM `Вид выступления` WHERE `Код_вида_выступления` = "вв01"), `Кол-во` = "1", `Продолжительность` = "12", `Основное_место (да/нет)` = "да", `Нагрузка` = 4; INSERT INTO `Мероприятия` SET `Из1.Ид_мероприятие` = (SELECT `Ид_мероприятия` FROM `Мероприятие` WHERE `Регномер_мероприятия` = "м002"), `Из2.Ид_вид_мероприятия` = (SELECT `Ид_вида_мероприятия` FROM `Вид мероприятия` WHERE `Код_вида_мероприятия` = "вм02"), `Из3.Ид_место` = (SELECT `Ид_место` FROM `Место` WHERE `Код_место` = "ме02"), `Из4.Ид_культответственный` = (SELECT `Ид_культответственного` FROM `Культответственный` WHERE `Код_культответственного` = "к002"), `Из5.Код_даты` = "2022-03-19", `Из6.Ид_выступление` = (SELECT `Ид_выступления` FROM `Выступление` WHERE `Регномер_выступления` = "в002"), `Из7.Ид_вида_выступления` = (SELECT `Ид_вида_выступления` FROM `Вид выступления` WHERE `Код_вида_выступления` = "вв02"), `Кол-во` = "2", `Продолжительность` = "14", `Основное_место (да/нет)` = "нет", `Нагрузка` = 2; SELECT "Фк1: `Мероприятия`" FROM `Мероприятия` WHERE 1=0; SELECT * FROM `Мероприятия`; DROP VIEW IF EXISTS `Запрос1`; CREATE VIEW `Запрос1` AS SELECT Из2.`Ид_вида_мероприятия`, Из2.`Название_вида_мероприятия` AS `Вид_мероприятия`, COUNT(Фк1.`Из1.Ид_мероприятие`) AS `Количество_мероприятий`, SUM(TIME_TO_SEC(Фк1.`Продолжительность`)) AS `Суммарная_продолжительность` FROM `Мероприятия` Фк1 JOIN `Вид мероприятия` Из2 ON Фк1.`Из2.Ид_вид_мероприятия` = Из2.`Ид_вида_мероприятия` GROUP BY `Ид_вида_мероприятия`; SELECT * FROM `Запрос1`; CREATE VIEW Запрос2 AS SELECT Место.`Ид_место` AS Ид_место, Место.`Название_место` AS Название_место, COUNT(Мероприятия.`Из4.Ид_культответственный`) AS Количество, SUM(Мероприятия.`Нагрузка`) AS Объем FROM Мероприятия JOIN Место ON Мероприятия.`Из3.Ид_место` = Место.`Ид_место` GROUP BY Место.`Ид_место`, Место.`Название_место`; SELECT * FROM `Запрос2`; CREATE VIEW Запрос3 AS SELECT Мероприятия.`Из1.Ид_мероприятие` AS Ид_мероприятия, Культответственный.`ФИО_культответственного` AS ФИО_культответственного, Культответственный.`Название_должности` AS Должность, Мероприятия.`Нагрузка` AS ОбъемРаботы, SUM(Мероприятия.`Нагрузка`) OVER (PARTITION BY Культответственный.`Ид_культответственного`, MONTH(Мероприятия.`Из5.Код_даты`)) AS СуммарныйМесячныйОбъем, (Мероприятия.`Нагрузка` / NULLIF(SUM(Мероприятия.`Нагрузка`) OVER (PARTITION BY Культответственный.`Ид_культответственного`, MONTH(Мероприятия.`Из5.Код_даты`)), 0)) * 100 AS ПроцентНагрузки, RANK() OVER (PARTITION BY MONTH(Мероприятия.`Из5.Код_даты`) ORDER BY SUM(Мероприятия.`Нагрузка`) DESC) AS РангМесяца FROM Мероприятия JOIN Культответственный ON Мероприятия.`Из4.Ид_культответственный` = Культответственный.`Ид_культответственного`; SELECT * FROM `Запрос3`;