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 the departments? | Do you want the names of all department? | yes
assistant
SELECT name FROM department;
human
which department has the most employees?
assistant
SELECT name FROM department GROUP BY departmentID ORDER BY COUNT( departmentID ) DESC LIMIT 1;
human
how many employees does Psychiatry have?
assistant
SELECT head FROM department WHERE name = 'Psychiatry';
human
how many employees does surgery have?
assistant
SELECT head FROM department WHERE name = 'Surgery';
human
what about general medicine?
assistant
SELECT head FROM department WHERE name = "General Medicine" ;