MYSQL Help writing Queries for below code. *create query to get the # of inpatie

Question

MYSQL Help writing Queries for below code.

*create query to get the # of inpatients test and the outpatient test.

*please create query to get the # of test done after 6pm per room (Room 1, Room 2 and Room 3)

*Please create a query to get the Average, Min, and Max exam durations.

-- Table structure for `TEST`

-- ----------------------------

DROP TABLE IF EXISTS `TEST`;

CREATE TABLE `TEST` (

`TEST_ID` int(11) NOT NULL,

`Duration` varchar(25) NOT NULL,

`Finish Time` time DEFAULT NULL,

`Start Time` time DEFAULT NULL,

`Anesthesia` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

PRIMARY KEY (`TEST_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of TEST

-- ----------------------------

-- ----------------------------

-- Table structure for `ip`

-- ----------------------------

DROP TABLE IF EXISTS `ip`;

CREATE TABLE `ip` (

`Patient_ID` char(20) NOT NULL,

`Bay_area` varchar(25) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of ip

-- ----------------------------

-- ----------------------------

-- Table structure for `ir_room`

-- ----------------------------

DROP TABLE IF EXISTS `ir_room`;

CREATE TABLE `ir_room` (

`Room_ID` varchar(100) NOT NULL,

`Equipment` varchar(100) NOT NULL,

`Capability` varchar(100) NOT NULL,

`TEST_ID` int(11) NOT NULL,

PRIMARY KEY (`Room_ID`),

KEY `TEST_ID` (`TEST_ID`),

CONSTRAINT `TEST_ID` FOREIGN KEY (`TEST_ID`) REFERENCES `TEST` (`TEST_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of ir_room

-- ----------------------------

-- ----------------------------

-- Table structure for `md`

-- ----------------------------

DROP TABLE IF EXISTS `md`;

CREATE TABLE `md` (

`MD_ID` varchar(255) NOT NULL,

`Name` varchar(255) NOT NULL,

`Pager Number` varchar(15) NOT NULL,

`Phone Number` varchar(15) NOT NULL,

`Specialty` varchar(255) NOT NULL,

`Work Address` varchar(255) NOT NULL,

`TEST_ID` int(11) NOT NULL,

PRIMARY KEY (`MD_ID`),

KEY `TEST_ID` (`TEST_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of md

-- ----------------------------

-- ----------------------------

-- Table structure for `op`

-- ----------------------------

DROP TABLE IF EXISTS `op`;

CREATE TABLE `op` (

`Scheduled TEST time` time NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of op

-- ----------------------------

-- ----------------------------

-- Table structure for `patient`

-- ----------------------------

DROP TABLE IF EXISTS `patient`;

CREATE TABLE `patient` (

`Patient _ID` varchar(255) NOT NULL,

`Gender` char(10) NOT NULL,

`Address` varchar(255) NOT NULL,

`IP_OP` char(5) NOT NULL,

`BAY_AREA` char(10) NOT NULL,

`Schduled_TEST_Time` time NOT NULL,

`TEST_ID` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of patient

-- ----------------------------

Explanation / Answer

1. select count(*) from patient group by IP_OP;

2. select count(*) from TEST, ir_room where TEST.TEST_ID=ir_room.TEST_ID and time>"18:00:00" group by ROOM_ID;

3.select min(DUR), max(DUR), avg(DUR) from (select cast(stuff(DURATION) as DUR)); --The nested query in from converts DURATION from varchar to time and save it as DUR.

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.