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 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
What are names of patients who made an appointment?
assistant
SELECT name FROM appointment AS T1 JOIN patient AS T2 ON T1.patient = T2.ssn
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 patient who has the most recent undergoing treatment?
assistant
SELECT patient FROM undergoes ORDER BY dateundergoes LIMIT 1
human
What is the name of the patient who made the most recent appointment?
assistant
SELECT T1.name FROM patient AS T1 JOIN appointment AS T2 ON T1.ssn = T2.patient ORDER BY T2.start DESC LIMIT 1
human
Find the id of the appointment with the most recent start date?
assistant
SELECT appointmentid FROM appointment ORDER BY START DESC LIMIT 1 ;