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 names of all distinct nurses ordered by alphabetical order?
assistant
SELECT DISTINCT name FROM nurse ORDER BY name
human
List the physicians' employee ids together with their primary affiliation departments' ids.
assistant
SELECT physician , department FROM affiliated_with WHERE 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
what is the name and position of the head whose department has least number of 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 ;