CREATE TABLE Physician (
    EmployeeID NUMBER PRIMARY KEY,
    Name TEXT,
    Position TEXT,
    SSN NUMBER
);
CREATE TABLE Department (
    DepartmentID NUMBER PRIMARY KEY,
    Name TEXT,
    Head NUMBER,
    FOREIGN KEY ("Head") REFERENCES Physician("EmployeeID")
);
CREATE TABLE Affiliated_With (
    Physician NUMBER PRIMARY KEY,
    Department NUMBER,
    PrimaryAffiliation BOOLEAN,
    FOREIGN KEY ("Department") REFERENCES Department("DepartmentID"),
    FOREIGN KEY ("Physician") REFERENCES Physician("EmployeeID")
);
CREATE TABLE Procedures (
    Code NUMBER PRIMARY KEY,
    Name TEXT,
    Cost NUMBER
);
CREATE TABLE Trained_In (
    Physician NUMBER PRIMARY KEY,
    Treatment NUMBER,
    CertificationDate TIME,
    CertificationExpires TIME,
    FOREIGN KEY ("Treatment") REFERENCES Procedures("Code"),
    FOREIGN KEY ("Physician") REFERENCES Physician("EmployeeID")
);
CREATE TABLE Patient (
    SSN NUMBER PRIMARY KEY,
    Name TEXT,
    Address TEXT,
    Phone TEXT,
    InsuranceID NUMBER,
    PCP NUMBER,
    FOREIGN KEY ("PCP") REFERENCES Physician("EmployeeID")
);
CREATE TABLE Nurse (
    EmployeeID NUMBER PRIMARY KEY,
    Name TEXT,
    Position TEXT,
    Registered BOOLEAN,
    SSN NUMBER
);
CREATE TABLE Appointment (
    AppointmentID NUMBER PRIMARY KEY,
    Patient NUMBER,
    PrepNurse NUMBER,
    Physician NUMBER,
    Start TIME,
    End TIME,
    ExaminationRoom TEXT,
    FOREIGN KEY ("Physician") REFERENCES Physician("EmployeeID"),
    FOREIGN KEY ("PrepNurse") REFERENCES Nurse("EmployeeID"),
    FOREIGN KEY ("Patient") REFERENCES Patient("SSN")
);
CREATE TABLE Medication (
    Code NUMBER PRIMARY KEY,
    Name TEXT,
    Brand TEXT,
    Description TEXT
);
CREATE TABLE Prescribes (
    Physician NUMBER PRIMARY KEY,
    Patient NUMBER,
    Medication NUMBER,
    Date TIME,
    Appointment NUMBER,
    Dose TEXT,
    FOREIGN KEY ("Appointment") REFERENCES Appointment("AppointmentID"),
    FOREIGN KEY ("Medication") REFERENCES Medication("Code"),
    FOREIGN KEY ("Patient") REFERENCES Patient("SSN"),
    FOREIGN KEY ("Physician") REFERENCES Physician("EmployeeID")
);
CREATE TABLE Block (
    BlockFloor NUMBER PRIMARY KEY,
    BlockCode NUMBER
);
CREATE TABLE Room (
    RoomNumber NUMBER PRIMARY KEY,
    RoomType TEXT,
    BlockFloor NUMBER,
    BlockCode NUMBER,
    Unavailable BOOLEAN,
    FOREIGN KEY ("BlockFloor") REFERENCES Block("BlockFloor"),
    FOREIGN KEY ("BlockCode") REFERENCES Block("BlockCode")
);
CREATE TABLE On_Call (
    Nurse NUMBER PRIMARY KEY,
    BlockFloor NUMBER,
    BlockCode NUMBER,
    OnCallStart TIME,
    OnCallEnd TIME,
    FOREIGN KEY ("BlockFloor") REFERENCES Block("BlockFloor"),
    FOREIGN KEY ("BlockCode") REFERENCES Block("BlockCode"),
    FOREIGN KEY ("Nurse") REFERENCES Nurse("EmployeeID")
);
CREATE TABLE Stay (
    StayID NUMBER PRIMARY KEY,
    Patient NUMBER,
    Room NUMBER,
    StayStart TIME,
    StayEnd TIME,
    FOREIGN KEY ("Room") REFERENCES Room("RoomNumber"),
    FOREIGN KEY ("Patient") REFERENCES Patient("SSN")
);
CREATE TABLE Undergoes (
    Patient NUMBER PRIMARY KEY,
    Procedures NUMBER,
    Stay NUMBER,
    DateUndergoes TIME,
    Physician NUMBER,
    AssistingNurse NUMBER,
    FOREIGN KEY ("AssistingNurse") REFERENCES Nurse("EmployeeID"),
    FOREIGN KEY ("Physician") REFERENCES Physician("EmployeeID"),
    FOREIGN KEY ("Stay") REFERENCES Stay("StayID"),
    FOREIGN KEY ("Procedures") REFERENCES Procedures("Code"),
    FOREIGN KEY ("Patient") REFERENCES Patient("SSN")
);

Question: Which department has the largest number of employees?
SQL: SELECT name FROM department GROUP BY departmentID ORDER BY count(departmentID) DESC LIMIT 1;

Question: Find the department with the most employees.
SQL: SELECT name FROM department GROUP BY departmentID ORDER BY count(departmentID) DESC LIMIT 1;

Question: What is the employee id of the head whose department has the least number of employees?
SQL: SELECT head FROM department GROUP BY departmentID ORDER BY count(departmentID) LIMIT 1;

Question: Tell me the employee id of the head of the department with the least employees.
SQL: SELECT head FROM department GROUP BY departmentID ORDER BY count(departmentID) LIMIT 1;

Question: what is the name and position of the head whose department has least number of employees?
SQL: 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;

Question: Find the name and position of the head of the department with the least employees.
SQL: 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;

Question: What are names of patients who made an appointment?
SQL: SELECT name FROM appointment AS T1 JOIN patient AS T2 ON T1.patient  =  T2.ssn

Question: List the names of patients who have made appointments.
SQL: SELECT name FROM appointment AS T1 JOIN patient AS T2 ON T1.patient  =  T2.ssn

Question: what are name and phone number of patients who had more than one appointment?
SQL: SELECT name ,  phone FROM appointment AS T1 JOIN patient AS T2 ON T1.patient  =  T2.ssn GROUP BY T1.patient HAVING count(*)  >  1

Question: Which patients made more than one appointment? Tell me the name and phone number of these patients.
SQL: SELECT name ,  phone FROM appointment AS T1 JOIN patient AS T2 ON T1.patient  =  T2.ssn GROUP BY T1.patient HAVING count(*)  >  1

Question: Find the id of the appointment with the most recent start date?
SQL: SELECT appointmentid FROM appointment ORDER BY START DESC LIMIT 1

Question: What is the id of the appointment that started most recently?
SQL: SELECT appointmentid FROM appointment ORDER BY START DESC LIMIT 1

Question: List the name of physicians who took some appointment.
SQL: SELECT T2.name FROM appointment AS T1 JOIN physician AS T2 ON T1.Physician  =  T2.EmployeeID

Question: What are the names of all the physicians who took appointments.
SQL: SELECT T2.name FROM appointment AS T1 JOIN physician AS T2 ON T1.Physician  =  T2.EmployeeID

Question: List the name of physicians who never took any appointment.
SQL: SELECT name FROM physician EXCEPT SELECT T2.name FROM appointment AS T1 JOIN physician AS T2 ON T1.Physician  =  T2.EmployeeID

Question: Which physicians have never taken any appointment? Find their names.
SQL: SELECT name FROM physician EXCEPT SELECT T2.name FROM appointment AS T1 JOIN physician AS T2 ON T1.Physician  =  T2.EmployeeID

Question: Find the names of all physicians and their primary affiliated departments' names.
SQL: 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

Question: What are the name and primarily affiliated department name of each physician?
SQL: 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

Question: What is the name of the patient who made the most recent appointment?
SQL: SELECT T1.name FROM patient AS T1 JOIN appointment AS T2 ON T1.ssn = T2.patient ORDER BY T2.start DESC LIMIT 1

Question: Find the name of the patient who made the appointment with the most recent start date.
SQL: SELECT T1.name FROM patient AS T1 JOIN appointment AS T2 ON T1.ssn = T2.patient ORDER BY T2.start DESC LIMIT 1

Question: How many patients stay in room 112?
SQL: SELECT count(patient) FROM stay WHERE room  =  112

Question: Count the number of patients who stayed in room 112.
SQL: SELECT count(patient) FROM stay WHERE room  =  112