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: List the names of all the physicians who prescribe Thesisin as medication.
SQL: SELECT DISTINCT T1.name FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician JOIN medication AS T3 ON T3.code = T2.medication WHERE T3.name  =  "Thesisin"

Question: Find the name and position of physicians who prescribe some medication whose brand is X?
SQL: SELECT DISTINCT T1.name ,  T1.position FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician JOIN medication AS T3 ON T3.code = T2.medication WHERE T3.Brand  =  "X"

Question: Which physicians prescribe a medication of brand X? Tell me the name and position of those physicians.
SQL: SELECT DISTINCT T1.name ,  T1.position FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician JOIN medication AS T3 ON T3.code = T2.medication WHERE T3.Brand  =  "X"

Question: Find the number of medications prescribed for each brand.
SQL: SELECT count(*) ,  T1.name FROM medication AS T1 JOIN prescribes AS T2 ON T1.code = T2.medication GROUP BY T1.brand

Question: How many medications are prescribed for each brand?
SQL: SELECT count(*) ,  T1.name FROM medication AS T1 JOIN prescribes AS T2 ON T1.code = T2.medication GROUP BY T1.brand

Question: Find the name of physicians whose position title contains the word 'senior'.
SQL: SELECT name FROM physician WHERE POSITION LIKE '%senior%'

Question: What are the names of the physicians who have 'senior' in their titles.
SQL: SELECT name FROM physician WHERE POSITION LIKE '%senior%'

Question: Find the patient who has the most recent undergoing treatment?
SQL: SELECT patient FROM undergoes ORDER BY dateundergoes LIMIT 1

Question: Which patient is undergoing the most recent treatment?
SQL: SELECT patient FROM undergoes ORDER BY dateundergoes LIMIT 1

Question: Find the names of all patients who have an undergoing treatment and are staying in room 111.
SQL: SELECT DISTINCT T2.name FROM undergoes AS T1 JOIN patient AS T2 ON T1.patient = T2.SSN JOIN stay AS T3 ON T1.Stay  =  T3.StayID WHERE T3.room  =  111

Question: What are the names of patients who are staying in room 111 and have an undergoing treatment?
SQL: SELECT DISTINCT T2.name FROM undergoes AS T1 JOIN patient AS T2 ON T1.patient = T2.SSN JOIN stay AS T3 ON T1.Stay  =  T3.StayID WHERE T3.room  =  111

Question: List the names of all distinct nurses ordered by alphabetical order?
SQL: SELECT DISTINCT name FROM nurse ORDER BY name

Question: What is the alphabetically ordered list of all the distinct names of nurses?
SQL: SELECT DISTINCT name FROM nurse ORDER BY name

Question: Find the names of nurses who are nursing an undergoing treatment.
SQL: SELECT DISTINCT T2.name FROM undergoes AS T1 JOIN nurse AS T2 ON T1.AssistingNurse  =  T2.EmployeeID

Question: Which nurses are in charge of patients undergoing treatments?
SQL: SELECT DISTINCT T2.name FROM undergoes AS T1 JOIN nurse AS T2 ON T1.AssistingNurse  =  T2.EmployeeID

Question: List the names of all distinct medications, ordered in an alphabetical order.
SQL: SELECT DISTINCT name FROM medication ORDER BY name

Question: What is the alphabetically ordered list of all distinct medications?
SQL: SELECT DISTINCT name FROM medication ORDER BY name

Question: What are the names of the physician who prescribed the highest dose?
SQL: SELECT T1.name FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician ORDER BY T2.dose DESC LIMIT 1

Question: Find the physician who prescribed the highest dose. What is his or her name?
SQL: SELECT T1.name FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician ORDER BY T2.dose DESC LIMIT 1

Question: List the physicians' employee ids together with their primary affiliation departments' ids.
SQL: SELECT physician ,  department FROM affiliated_with WHERE primaryaffiliation  =  1

Question: What are each physician's employee id and department id primarily affiliated.
SQL: SELECT physician ,  department FROM affiliated_with WHERE primaryaffiliation  =  1

Question: List the names of departments where some physicians are primarily affiliated with.
SQL: SELECT DISTINCT T2.name FROM affiliated_with AS T1 JOIN department AS T2 ON T1.department = T2.departmentid WHERE PrimaryAffiliation  =  1

Question: What are the names of departments that have primarily affiliated physicians.
SQL: SELECT DISTINCT T2.name FROM affiliated_with AS T1 JOIN department AS T2 ON T1.department = T2.departmentid WHERE PrimaryAffiliation  =  1