human
Schema:
Physician (EmployeeID, Name, Position, SSN)
Department (DepartmentID, Name, Head)
Affiliated_With (Physician, Department, PrimaryAffiliation)
Procedures (Code, Name, Cost)
Trained_In (Physician, Treatment, CertificationDate, CertificationExpires)
Patient (SSN, Name, Address, Phone, InsuranceID, PCP)
Nurse (EmployeeID, Name, Position, Registered, SSN)
Appointment (AppointmentID, Patient, PrepNurse, Physician, Start, End, ExaminationRoom)
Medication (Code, Name, Brand, Description)
Prescribes (Physician, Patient, Medication, Date, Appointment, Dose)
Block (BlockFloor, BlockCode)
Room (RoomNumber, RoomType, BlockFloor, BlockCode, Unavailable)
On_Call (Nurse, BlockFloor, BlockCode, OnCallStart, OnCallEnd)
Stay (StayID, Patient, Room, StayStart, StayEnd)
Undergoes (Patient, Procedures, Stay, DateUndergoes, Physician, AssistingNurse)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the number of rooms located on each block floor.
assistant
SELECT count(*) , T1.blockfloor FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor = T2.blockfloor AND T1.blockcode = T2.blockcode GROUP BY T1.blockfloor
human
what are name and phone number of patients who had more than one appointment?
assistant
SELECT name , phone FROM appointment AS T1 JOIN patient AS T2 ON T1.patient = T2.ssn GROUP BY T1.patient HAVING count(*) > 1
human
Find the name of medication used on the patient who stays in room 111?
assistant
SELECT T4.name FROM stay AS T1 JOIN patient AS T2 ON T1.Patient = T2.SSN JOIN Prescribes AS T3 ON T3.Patient = T2.SSN JOIN Medication AS T4 ON T3.Medication = T4.Code WHERE room = 111
human
Find the names of all patients who have an undergoing treatment and are staying in room 111.
assistant
SELECT DISTINCT T2.name FROM undergoes AS T1 JOIN patient AS T2 ON T1.patient = T2.SSN JOIN stay AS T3 ON T1.Stay = T3.StayID WHERE T3.room = 111
human
Find the patient who most recently stayed in room 111.
assistant
SELECT patient FROM stay WHERE room = 111 ORDER BY staystart DESC LIMIT 1
human
Count the number of patients who stayed in room 112.
assistant
SELECT COUNT( patient ) FROM stay WHERE room = 112 ;