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 name of physicians who are in charge of more than one patient.
assistant
SELECT T1.name FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid = T2.PCP GROUP BY T1.employeeid HAVING count(*) > 1
human
Find the names of nurses who are on call.
assistant
SELECT DISTINCT T1.name FROM nurse AS T1 JOIN on_call AS T2 ON T1.EmployeeID = T2.nurse
human
What is the name of the nurse has the most appointments?
assistant
SELECT T1.name FROM nurse AS T1 JOIN appointment AS T2 ON T1.employeeid = T2.prepnurse GROUP BY T1.employeeid ORDER BY count(*) DESC LIMIT 1
human
Find the names of nurses who are nursing an undergoing treatment.
assistant
SELECT DISTINCT T2.name FROM undergoes AS T1 JOIN nurse AS T2 ON T1.AssistingNurse = T2.EmployeeID
human
List the names of all distinct medications, ordered in an alphabetical order.
assistant
SELECT DISTINCT name FROM medication ORDER BY name
human
List the names of all distinct nurses ordered by alphabetical order?
assistant
SELECT DISTINCT name FROM nurse ORDER BY name ;