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: What nurses are on call with block floor 1 and block code 1? Tell me their names.
SQL: SELECT nurse FROM on_call WHERE blockfloor  =  1 AND blockcode  =  1

Question: Find the ids of the nurses who are on call in block floor 1 and block code 1.
SQL: SELECT nurse FROM on_call WHERE blockfloor  =  1 AND blockcode  =  1

Question: What are the highest cost, lowest cost and average cost of procedures?
SQL: SELECT MAX(cost) ,  MIN(cost) ,  AVG(cost) FROM procedures

Question: Tell me the highest, lowest, and average cost of procedures.
SQL: SELECT MAX(cost) ,  MIN(cost) ,  AVG(cost) FROM procedures

Question: List the name and cost of all procedures sorted by the cost from the highest to the lowest.
SQL: SELECT name ,  cost FROM procedures ORDER BY cost DESC

Question: Sort the list of names and costs of all procedures in the descending order of cost.
SQL: SELECT name ,  cost FROM procedures ORDER BY cost DESC

Question: Find the three most expensive procedures.
SQL: SELECT name FROM procedures ORDER BY cost LIMIT 3

Question: What are the three most costly procedures?
SQL: SELECT name FROM procedures ORDER BY cost LIMIT 3

Question: Find the physicians who are trained in a procedure that costs more than 5000.
SQL: SELECT T1.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T3.cost  >  5000

Question: Which physicians are trained in procedures that are more expensive than 5000?
SQL: SELECT T1.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T3.cost  >  5000

Question: Find the physician who was trained in the most expensive procedure?
SQL: SELECT T1.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment ORDER BY T3.cost DESC LIMIT 1

Question: Which physician was trained in the procedure that costs the most.
SQL: SELECT T1.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment ORDER BY T3.cost DESC LIMIT 1

Question: What is the average cost of procedures that physician John Wen was trained in?
SQL: SELECT avg(T3.cost) FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T1.name  =  "John Wen"

Question: Compute the mean price of procedures physician John Wen was trained in.
SQL: SELECT avg(T3.cost) FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T1.name  =  "John Wen"

Question: Find the names of procedures which physician John Wen was trained in.
SQL: SELECT T3.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T1.name  =  "John Wen"

Question: What are the names of procedures physician John Wen was trained in?
SQL: SELECT T3.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T1.name  =  "John Wen"

Question: Find all procedures which cost more than 1000 or which physician John Wen was trained in.
SQL: SELECT name FROM procedures WHERE cost  >  1000 UNION SELECT T3.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T1.name  =  "John Wen"

Question: What are the procedures that cost more than 1000 or are specialized in by physician John Wen?
SQL: SELECT name FROM procedures WHERE cost  >  1000 UNION SELECT T3.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T1.name  =  "John Wen"

Question: Find the names of all procedures which cost more than 1000 but which physician John Wen was not trained in?
SQL: SELECT name FROM procedures WHERE cost  >  1000 EXCEPT SELECT T3.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T1.name  =  "John Wen"