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
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 names of all procedures which cost more than 1000 but which physician John Wen was not trained in?
assistant
SELECT name FROM procedures WHERE cost > 1000 EXCEPT SELECT T3.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid = T2.physician JOIN procedures AS T3 ON T3.code = T2.treatment WHERE T1.name = 'John Wen'
human
What is the average cost of procedures that physician John Wen was trained in?
assistant
SELECT avg(T3.cost) FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid = T2.physician JOIN procedures AS T3 ON T3.code = T2.treatment WHERE T1.name = 'John Wen'
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
What are the names of the physician who prescribed the highest dose?
assistant
SELECT T1.name FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician ORDER BY T2.dose DESC LIMIT 1
human
How many patients' prescriptions are made by physician John Dorian?
assistant
SELECT COUNT( T1.SSN ) FROM patient AS T1 JOIN prescribes AS T2 ON T1.SSN = T2.patient JOIN physician AS T3 ON T2.physician = T3.employeeid WHERE T3.name = "John Dorian" ;