Please write the SQL command to create tables you creatre in question MySQL . cr
ID: 3588521 • Letter: P
Question
Please write the SQL command to create tables you creatre in question MySQL.
create table tbldummy (ID Integer, …PRIMARY KEY(ID), FOREIGN KEY (…)REFERENCES …);
4. Please write the SQL commands for the following queries.
*Please create query to get the # of IP test and the # of OP test.
*Please create query to get the # of tests done after 5pm per room (IR Room 1, IR Room 2 and IR Room 3)
*The third question is the test duration. For each of the three rooms, please get the Average, Min, and Max test durations.
Equipment Start time Name Finish time Pager number Capability Room ID Exam ID MD ID Duration nesthesia IR room Assign Exam Treat MD Phone number Specialty Working address Patient ID Name Patient Gender Adress 0 eduled Bay areaIP OP xam timeExplanation / Answer
1) select IP_OP,count(*) as count
from PATIENT
group by IP_OP
selecting #Ip and #OP count from patient table. Group by is used group the distinct values (here to get count)
2) select Room_ID, count(*) as count
from EXAMTABLE
where Start Time>17:00:00 or Start Time>05:00:00pm
group by Room_ID
selecting Room_ID with count of tests from test table where test starts after 5 PM, so again group by is used to group by distinct values to get result for all rooms
3) select Room_ID, avg(Start Time-Finish Time),max(Start Time-Finish Time),min(Start Time-Finish Time)
from EXAMTABLE
group by Room_ID
Selecting room id with avg, max,min test durations (start time -finish time) from test table for individual rooms, so group by is used.