Based on the following SQL Hospital Schema return 15 Queries Return the description, body, and execution result
Question:
Based on the following SQL Hospital Schema return 15 Queries
Return the description, body, and execution result of your SQL queries (The set of queries must contain at least three join queries, three aggregation queries, and three nested queries)
SQL query needs to have variety and contain more complex SQL queries. Queries such as the followings are too simple:
select * from Room; select * from Nurse; select * from Physician;
-- ----------------------------------------------------- -- Schema hospital -- -----------------------------------------------------
-- ----------------------------------------------------- -- Schema hospital -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `hospital` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ; USE `hospital` ;
-- ----------------------------------------------------- -- Table `hospital`.`Physician` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`Physician` ( `Physician_id` INT NOT NULL, `Name` VARCHAR(45) NULL, `Field_of_expertise` VARCHAR(45) NULL, `address` VARCHAR(45) NULL, `Certificate_No` VARCHAR(45) NULL, `Phone_number` INT NULL, PRIMARY KEY (`Physician_id`)) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `hospital`.`Rooms` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`Rooms` ( `Room_id` INT NOT NULL, `Fee_per_night` VARCHAR(45) NULL, `Capacity` INT NULL, PRIMARY KEY (`Room_id`)) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `hospital`.`Patient` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`Patient` ( `Patient_id` INT NOT NULL, `Phone_number` VARCHAR(45) NULL, `Address` VARCHAR(45) NULL, `Name` VARCHAR(45) NULL, `Rooms_Room_id` INT NOT NULL, PRIMARY KEY (`Patient_id`), INDEX `fk_Patient_Rooms1_idx` (`Rooms_Room_id` ASC) VISIBLE, CONSTRAINT `fk_Patient_Rooms1` FOREIGN KEY (`Rooms_Room_id`) REFERENCES `hospital`.`Rooms` (`Room_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `hospital`.`HealthRecord` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`HealthRecord` ( `Patient_id` INT NOT NULL, `status` VARCHAR(45) NULL, `Date` DATE NULL, `description` VARCHAR(45) NULL, `Disease` VARCHAR(45) NULL, `Patient_Patient_id` INT NOT NULL, PRIMARY KEY (`Patient_id`), INDEX `fk_HealthRecord_Patient1_idx` (`Patient_Patient_id` ASC) VISIBLE, CONSTRAINT `fk_HealthRecord_Patient1` FOREIGN KEY (`Patient_Patient_id`) REFERENCES `hospital`.`Patient` (`Patient_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `hospital`.`Nurse` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`Nurse` ( `Nurse_id` INT NOT NULL, `Name` VARCHAR(45) NULL, `Certificate_number` VARCHAR(45) NULL, `phone_number` VARCHAR(45) NULL, `address` VARCHAR(45) NULL, `Physician_Physician_id` INT NOT NULL, PRIMARY KEY (`Nurse_id`), INDEX `fk_Nurse_Physician1_idx` (`Physician_Physician_id` ASC) VISIBLE, CONSTRAINT `fk_Nurse_Physician1` FOREIGN KEY (`Physician_Physician_id`) REFERENCES `hospital`.`Physician` (`Physician_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `hospital`.`Payment` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`Payment` ( `Patient_id` INT NOT NULL, `date` DATE NULL, `amount` DECIMAL(10,2) NULL, `Patient_Patient_id` INT NOT NULL, `Rooms_Room_id` INT NOT NULL, PRIMARY KEY (`Patient_id`), INDEX `fk_Payment_Patient1_idx` (`Patient_Patient_id` ASC) VISIBLE, INDEX `fk_Payment_Rooms1_idx` (`Rooms_Room_id` ASC) VISIBLE, CONSTRAINT `fk_Payment_Patient1` FOREIGN KEY (`Patient_Patient_id`) REFERENCES `hospital`.`Patient` (`Patient_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Payment_Rooms1` FOREIGN KEY (`Rooms_Room_id`) REFERENCES `hospital`.`Rooms` (`Room_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `hospital`.`Instruction` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`Instruction` ( `Instruction_id` INT NOT NULL, `execution_status` VARCHAR(45) NULL, `Fee` DECIMAL(10,2) NULL, `Description` VARCHAR(45) NULL, `Payment_Patient_id` INT NOT NULL, PRIMARY KEY (`Instruction_id`), INDEX `fk_Instruction_Payment1_idx` (`Payment_Patient_id` ASC) VISIBLE, CONSTRAINT `fk_Instruction_Payment1` FOREIGN KEY (`Payment_Patient_id`) REFERENCES `hospital`.`Payment` (`Patient_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `hospital`.`Medicateion` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`Medicateion` ( `Nurse_Nurse_id` INT NOT NULL, `Patient_Patient_id` INT NOT NULL, `Name` VARCHAR(45) NULL, `Quantity` INT NULL, PRIMARY KEY (`Nurse_Nurse_id`, `Patient_Patient_id`), INDEX `fk_Nurse_has_Patient_Patient1_idx` (`Patient_Patient_id` ASC) VISIBLE, INDEX `fk_Nurse_has_Patient_Nurse_idx` (`Nurse_Nurse_id` ASC) VISIBLE, CONSTRAINT `fk_Nurse_has_Patient_Nurse` FOREIGN KEY (`Nurse_Nurse_id`) REFERENCES `hospital`.`Nurse` (`Nurse_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Nurse_has_Patient_Patient1` FOREIGN KEY (`Patient_Patient_id`) REFERENCES `hospital`.`Patient` (`Patient_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `hospital`.`Physician_has_Patient` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `hospital`.`Physician_has_Patient` ( `Physician_Physician_id` INT NOT NULL, `Patient_Patient_id` INT NOT NULL, PRIMARY KEY (`Physician_Physician_id`, `Patient_Patient_id`), INDEX `fk_Physician_has_Patient_Patient1_idx` (`Patient_Patient_id` ASC) VISIBLE, INDEX `fk_Physician_has_Patient_Physician1_idx` (`Physician_Physician_id` ASC) VISIBLE, CONSTRAINT `fk_Physician_has_Patient_Physician1` FOREIGN KEY (`Physician_Physician_id`) REFERENCES `hospital`.`Physician` (`Physician_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Physician_has_Patient_Patient1` FOREIGN KEY (`Patient_Patient_id`) REFERENCES `hospital`.`Patient` (`Patient_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Operations Management Creating Value Along the Supply Chain
ISBN: 978-0470525906
7th Edition
Authors: Roberta S. Russell, Bernard W. Taylor