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 whose position title contains the word 'senior'.
assistant
SELECT name FROM physician WHERE POSITION LIKE '%senior%'
human
List the name of physicians who took some appointment.
assistant
SELECT T2.name FROM appointment AS T1 JOIN physician AS T2 ON T1.Physician = T2.EmployeeID
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
What is the names of the physicians who prescribe medication Thesisin?
assistant
SELECT DISTINCT T1.name FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician JOIN medication AS T3 ON T3.code = T2.medication WHERE T3.name = 'Thesisin'
human
Find the name and position of physicians who prescribe some medication whose brand is X?
assistant
SELECT DISTINCT T1.name , T1.position FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician JOIN medication AS T3 ON T3.code = T2.medication WHERE T3.Brand = 'X'
human
Find the physician who prescribed the highest dose. What is his or her name?
assistant
SELECT T1.name FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician ORDER BY T2.dose DESC LIMIT 1 ;