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 name of physicians whose position title contains the word 'senior'.
assistant
SELECT name FROM physician WHERE POSITION LIKE '%senior%'
human
Find the names of all physicians and their primary affiliated departments' names.
assistant
SELECT T1.name , T3.name FROM physician AS T1 JOIN affiliated_with AS T2 ON T1.EmployeeID = T2.physician JOIN department AS T3 ON T2.department = T3.DepartmentID WHERE T2.PrimaryAffiliation = 1
human
Which department has the largest number of employees?
assistant
SELECT name FROM department GROUP BY departmentID ORDER BY count(departmentID) DESC LIMIT 1;
human
What is the employee id of the head whose department has the least number of employees?
assistant
SELECT head FROM department GROUP BY departmentID ORDER BY count(departmentID) LIMIT 1;
human
Find the name and position of the head of the department with the least employees.
assistant
SELECT T2.name , T2.position FROM department AS T1 JOIN physician AS T2 ON T1.head = T2.EmployeeID GROUP BY departmentID ORDER BY COUNT( departmentID ) LIMIT 1 ;