SQLize Online / PHPize Online  /  SQLtest Online

DROP TABLE IF EXISTS `Справка+`; CREATE TABLE `Справка+` ( `Ид` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Слк` CHAR (3) NOT NULL , `Код` CHAR (3) NOT NULL , `Название` VARCHAR (50) NOT NULL , `Содержание` VARCHAR (100) NULL , `Ид_родитель\СПР^СПР` INT NULL REFERENCES `Справка+` (`Ид`) , CONSTRAINT `Ун1\СПР` UNIQUE (`Слк`, `Код`) ); INSERT INTO `Справка+` VALUES (DEFAULT, "ДОЛ", "про", "проф.", "профессср кафедры", NULL), (DEFAULT, "ДОЛ", "доц", "доц. ", "доцент кафедры", NULL), (DEFAULT, "ДОЛ", "лаб", "лаборант", NULL, NULL), (NULL, "СТЕ", "дтн", "д-р техн. наук", "доктор технических наук", NULL), (NULL, "СТЕ", "ктн", "канд. техн. наук", "кандидат технических наук", NULL), (NULL, "ЗВА", "про", "проф.", "профессор ВАК", NULL), (NULL, "ЗВА", "доц", "доц.", "доцент ВАК", NULL), (NULL, "ПОД", "ИРТ", "ФИРТ", "ф-т информатикики и робототехники", NULL); INSERT INTO `Справка+` SELECT NULL, "ПОД", "АСУ", "каф. АСУ", "каф. автоматизир. систем упр-я", `Ид` FROM `Справка+` WHERE `Слк`="ПОД" AND `Код`="ИРТ"; SELECT * FROM `Справка+`; DROP TABLE IF EXISTS `Студент%ПЕРС`; CREATE TABLE `Студент%ПЕРС` ( `Ид` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Код` CHAR (10) NOT NULL UNIQUE , `Паспорт` CHAR (10) NOT NULL UNIQUE , `ФИО` VARCHAR (50) NOT NULL , `Сведения` VARCHAR (150) NOT NULL ); INSERT INTO `Студент%ПЕРС` VALUES (NULL, "001-Б", "123456", "Иванов И. И.", "комсомолец"), (NULL, "002-К", "234567", "Сидоров С. С.", "беспартийный"); SELECT * FROM `Студент%ПЕРС`; DROP TABLE IF EXISTS `Сотрудник%ПЕРС`; CREATE TABLE `Сотрудник%ПЕРС` ( `Ид` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Код` CHAR (10) NOT NULL UNIQUE , `Паспорт` CHAR (10) NOT NULL UNIQUE , `ФИО` VARCHAR (50) NOT NULL , `Сведения` VARCHAR (150) NOT NULL , `Ид_ДОЛ\СОТ^СПР` INT NOT NULL REFERENCES `Справка+` (`Ид`) , `Ид_ПОД\СОТ^СПР` INT NOT NULL REFERENCES `Справка+` (`Ид`) ); INSERT INTO `Сотрудник%ПЕРС` SET `Ид` = NULL, `Код` = "001-П", `Паспорт` = "345678", `ФИО` = "Петров А. А.", `Сведения` = "чл. КПСС", `Ид_ДОЛ\СОТ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "ДОЛ" AND `Код` = "про"), `Ид_ПОД\СОТ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "ПОД" AND `Код` = "АСУ"); INSERT INTO `Сотрудник%ПЕРС` SET `Ид` = NULL, `Код` = "002-П", `Паспорт` = "456789", `ФИО` = "Боширов Р. Р.", `Сведения` = "канд. КПСС", `Ид_ДОЛ\СОТ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "ДОЛ" AND `Код` = "доц"), `Ид_ПОД\СОТ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "ПОД" AND `Код` = "АСУ"); INSERT INTO `Сотрудник%ПЕРС` SET `Ид` = NULL, `Код` = "099-В", `Паспорт` = "567890", `ФИО` = "Исаев М. М.", `Сведения` = "чл. ВЛКСМ", `Ид_ДОЛ\СОТ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "ДОЛ" AND `Код` = "лаб"), `Ид_ПОД\СОТ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "ПОД" AND `Код` = "АСУ"); SELECT * FROM `Сотрудник%ПЕРС`; DROP TABLE IF EXISTS `Лаборант:СОТ`; CREATE TABLE `Лаборант:СОТ` ( `Ид\ЛАБ^СОТ` INT NOT NULL PRIMARY KEY REFERENCES `Сотрудник%ПЕРС` (`Ид`) , `Сведения` VARCHAR (150) NULL ); INSERT INTO `Лаборант:СОТ` SELECT `Ид`, "диспл. класс 6-417" FROM `Сотрудник%ПЕРС` WHERE `Код` = "099-В"; SELECT * FROM `Лаборант:СОТ`; DROP TABLE IF EXISTS `Препод:СОТ`; CREATE TABLE `Препод:СОТ` ( `Ид\ПРЕ^СОТ` INT NOT NULL PRIMARY KEY REFERENCES `Сотрудник%ПЕРС` (`Ид`) , `Сведения` VARCHAR (150) NOT NULL , `Ид_СТЕ\ПРЕ^СПР` INT NULL REFERENCES `Справка+` (`Ид`) , `Ид_ЗВА\ПРЕ^СПР` INT NULL REFERENCES `Справка+` (`Ид`) ); INSERT INTO `Препод:СОТ` SET `Ид\ПРЕ^СОТ` = (SELECT `Ид` FROM `Сотрудник%ПЕРС` WHERE `Код` = "001-П"), `Сведения` = "с 1975", `Ид_СТЕ\ПРЕ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "СТЕ" AND `Код` = "дтн"), `Ид_ЗВА\ПРЕ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "ЗВА" AND `Код` = "про"); INSERT INTO `Препод:СОТ` SET `Ид\ПРЕ^СОТ` = (SELECT `Ид` FROM `Сотрудник%ПЕРС` WHERE `Код` = "002-П"), `Сведения` = "с 1990", `Ид_СТЕ\ПРЕ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "СТЕ" AND `Код` = "ктн"), `Ид_ЗВА\ПРЕ^СПР` = (SELECT `Ид` FROM `Справка+` WHERE `Слк` = "ЗВА" AND `Код` = "доц"); SELECT * FROM `Препод:СОТ`; DROP VIEW IF EXISTS `вирт_Кафедра`; CREATE VIEW `вирт_Кафедра` AS SELECT `СПР1`.`Код` AS `Код кафедры`, `СПР1`.`Название` AS `Название кафедры`, `СПР2`.`Код` AS `Код факультета`, `СПР2`.`Название` AS `Название факультета`, `СПР1`.`Ид` AS `Ид\Кафедра` FROM `Справка+` AS `СПР1` LEFT JOIN `Справка+` AS `СПР2` ON `СПР1`.`Ид_родитель\СПР^СПР` = `СПР2`.`Ид` WHERE `СПР1`.`Слк` = "ПОД" AND `СПР1`.`Название` LIKE "каф%" ; SELECT * FROM `вирт_Кафедра`; DROP PROCEDURE IF EXISTS `проц_Преподаватель`; CREATE PROCEDURE `проц_Преподаватель` (IN `Вх:Ид\Кафедра` INT) SELECT `СОТ`.`Код` AS `Код преподавателя`, `СОТ`.`ФИО` AS `ФИО преподавателя`, `СПР1`.`Код` AS `Код должности`, `СПР1`.`Название` AS `Название должности` FROM `Препод:СОТ` AS `ПРЕ` INNER JOIN `Сотрудник%ПЕРС` AS `СОТ` ON `ПРЕ`.`Ид\ПРЕ^СОТ` = `СОТ`.`Ид` INNER JOIN `Справка+` AS `СПР1` ON `СОТ`.`Ид_ДОЛ\СОТ^СПР` = `СПР1`.`Ид` INNER JOIN `Справка+` AS `СПР2` ON `СОТ`.`Ид_ПОД\СОТ^СПР` = `СПР2`.`Ид` WHERE `СПР2`.`Ид` = `Вх:Ид\Кафедра` ; CALL `проц_Преподаватель` (9); DROP VIEW IF EXISTS `вирт_Препод%Кафедра`; CREATE VIEW `вирт_Препод%Кафедра` AS SELECT `СПР1`.`Код` AS `Код факультета`, `СПР1`.`Название` AS `Название факультета`, `СПР2`.`Код` AS `Код кафедры`, `СПР2`.`Название` AS `Название кафедры`, `СОТ`.`Код` AS `Код преподавателя`, `СОТ`.`ФИО` AS `ФИО преподавателя`, `СПР3`.`Код` AS `Код должности`, `СПР3`.`Название` AS `Название должности` FROM `Справка+` AS `СПР1` LEFT JOIN `Справка+` AS `СПР2` ON `СПР1`.`Ид` = `СПР2`.`Ид_родитель\СПР^СПР` LEFT JOIN `Сотрудник%ПЕРС` AS `СОТ` ON `СПР2`.`Ид` = `СОТ`.`Ид_ПОД\СОТ^СПР` INNER JOIN `Препод:СОТ` AS `ПРЕ` ON `СОТ`.`Ид` = `ПРЕ`.`Ид\ПРЕ^СОТ` INNER JOIN `Справка+` AS `СПР3` ON `СОТ`.`Ид_ДОЛ\СОТ^СПР` = `СПР3`.`Ид` WHERE `СПР1`.`Слк` = "ПОД" AND `СПР1`.`Название` LIKE "ф-т%" ; SELECT * FROM `вирт_Препод%Кафедра`; DROP VIEW IF EXISTS `вирт_Кафедра_преп_лаб`; CREATE VIEW `вирт_Кафедра_преп_лаб` AS WITH `ЧПРЕ` AS ( SELECT `СОТ`.`Ид_ПОД\СОТ^СПР` AS `Ид_ПОД`, COUNT(*) AS `Кол_ПРЕ` FROM `Сотрудник%ПЕРС` AS `СОТ` INNER JOIN `Препод:СОТ` AS `ПРЕ` ON `СОТ`.`Ид` = `ПРЕ`.`Ид\ПРЕ^СОТ` GROUP BY `Ид_ПОД\СОТ^СПР`), `ЧЛАБ` AS ( SELECT `СОТ`.`Ид_ПОД\СОТ^СПР` AS `Ид_ПОД`, COUNT(*) AS `Кол_ЛАБ` FROM `Сотрудник%ПЕРС` AS `СОТ` INNER JOIN `Лаборант:СОТ` AS `ЛАБ` ON `СОТ`.`Ид` = `ЛАБ`.`Ид\ЛАБ^СОТ` GROUP BY `Ид_ПОД\СОТ^СПР`) SELECT `СПР1`.`Код` AS `Код кафедры`, `СПР1`.`Название` AS `Название кафедры`, `СПР2`.`Код` AS `Код факультета`, `СПР2`.`Название` AS `Название факультета`, `ЧПРЕ`.`Кол_ПРЕ` AS `Число преподавателей`, `ЧЛАБ`.`Кол_ЛАБ` AS `Число лаборантов` FROM `Справка+` AS `СПР1` LEFT JOIN `Справка+` AS `СПР2` ON `СПР1`.`Ид_родитель\СПР^СПР` = `СПР2`.`Ид` LEFT JOIN `ЧПРЕ` ON `СПР1`.`Ид` = `ЧПРЕ`.`Ид_ПОД` LEFT JOIN `ЧЛАБ` ON `СПР1`.`Ид` = `ЧЛАБ`.`Ид_ПОД` WHERE `СПР1`.`Слк` = "ПОД" AND `СПР1`.`Название` LIKE "каф%"; -- SELECT * FROM `вирт_Кафедра_преп_лаб`; DROP VIEW IF EXISTS `вирт_Кафедра_сотрудники`; CREATE VIEW `вирт_Кафедра_сотрудники` AS SELECT `СПР1`.`Код` AS `Код кафедры`, `СПР1`.`Название` AS `Название кафедры`, `СПР2`.`Код` AS `Код факультета`, `СПР2`.`Название` AS `Название факультета`, COUNT(*) AS `Число сотрудников` FROM `Справка+` AS `СПР1` LEFT JOIN `Справка+` AS `СПР2` ON `СПР1`.`Ид_родитель\СПР^СПР` = `СПР2`.`Ид` INNER JOIN `Сотрудник%ПЕРС` AS `СОТ` ON `СПР1`.`Ид` = `СОТ`.`Ид_ПОД\СОТ^СПР` WHERE `СПР1`.`Слк` = "ПОД" AND `СПР1`.`Название` LIKE "каф%" GROUP BY `СПР1`.`Ид` ; SELECT * FROM `вирт_Кафедра_сотрудники`; DROP VIEW IF EXISTS `вирт_Кафедра_преп`; CREATE VIEW `вирт_Кафедра_преп` AS WITH `ЧПРЕ` AS ( SELECT `СОТ`.`Ид_ПОД\СОТ^СПР` AS `Ид_ПОД`, COUNT(*) AS `Кол_ПРЕ` FROM `Сотрудник%ПЕРС` AS `СОТ` INNER JOIN `Препод:СОТ` AS `ПРЕ` ON `СОТ`.`Ид` = `ПРЕ`.`Ид\ПРЕ^СОТ` GROUP BY `Ид_ПОД\СОТ^СПР`) SELECT `СПР1`.`Код` AS `Код кафедры`, `СПР1`.`Название` AS `Название кафедры`, `СПР2`.`Код` AS `Код факультета`, `СПР2`.`Название` AS `Название факультета`, `ЧПРЕ`.`Кол_ПРЕ` AS `Число преподавателей` FROM `Справка+` AS `СПР1` LEFT JOIN `Справка+` AS `СПР2` ON `СПР1`.`Ид_родитель\СПР^СПР` = `СПР2`.`Ид` LEFT JOIN ( SELECT `СОТ`.`Ид_ПОД\СОТ^СПР` AS `Ид_ПОД`, COUNT(*) AS `Кол_ПРЕ` FROM `Сотрудник%ПЕРС` AS `СОТ` INNER JOIN `Препод:СОТ` AS `ПРЕ` ON `СОТ`.`Ид` = `ПРЕ`.`Ид\ПРЕ^СОТ` GROUP BY `Ид_ПОД\СОТ^СПР` ) AS `ЧПРЕ` ON `СПР1`.`Ид` = `ЧПРЕ`.`Ид_ПОД` WHERE `СПР1`.`Слк` = "ПОД" AND `СПР1`.`Название` LIKE "каф%"; -- SELECT "VIEW вирт_Кафедра_преп:" WHERE 1=0; SELECT * FROM `вирт_Кафедра_преп`;