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: Among the procedures that cost more than 1000, which were not specialized in by physician John Wen?
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"

Question: Find the names of all procedures such that the cost is less than 5000 and physician John Wen was trained in.
SQL: SELECT name FROM procedures WHERE cost  <  5000 INTERSECT 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 procedures cost less than 5000 and have John Wen as a trained physician?
SQL: SELECT name FROM procedures WHERE cost  <  5000 INTERSECT 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 name of physicians who are affiliated with both Surgery and Psychiatry departments.
SQL: SELECT T1.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 T3.name  =  'Surgery' INTERSECT SELECT T1.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 T3.name  =  'Psychiatry'

Question: Which physicians are affiliated with both Surgery and Psychiatry departments? Tell me their names.
SQL: SELECT T1.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 T3.name  =  'Surgery' INTERSECT SELECT T1.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 T3.name  =  'Psychiatry'

Question: Find the name of physicians who are affiliated with Surgery or Psychiatry department.
SQL: SELECT T1.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 T3.name  =  'Surgery' OR T3.name  =  'Psychiatry'

Question: Which physicians are affiliated with either Surgery or Psychiatry department? Give me their names.
SQL: SELECT T1.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 T3.name  =  'Surgery' OR T3.name  =  'Psychiatry'

Question: Find the names of patients who are not using the medication of Procrastin-X.
SQL: SELECT name FROM patient EXCEPT SELECT T1.name FROM patient AS T1 JOIN Prescribes AS T2 ON T2.Patient  =  T1.SSN JOIN Medication AS T3 ON T2.Medication  =  T3.Code WHERE T3.name  =  'Procrastin-X'

Question: What are the names of patients who are not taking the medication of Procrastin-X.
SQL: SELECT name FROM patient EXCEPT SELECT T1.name FROM patient AS T1 JOIN Prescribes AS T2 ON T2.Patient  =  T1.SSN JOIN Medication AS T3 ON T2.Medication  =  T3.Code WHERE T3.name  =  'Procrastin-X'

Question: Find the number of patients who are not using the medication of Procrastin-X.
SQL: SELECT count(*) FROM patient WHERE SSN NOT IN ( SELECT T1.patient FROM Prescribes AS T1 JOIN Medication AS T2 ON T1.Medication  =  T2.Code WHERE T2.name  =  'Procrastin-X' )

Question: How many patients are not using Procrastin-X as medication?
SQL: SELECT count(*) FROM patient WHERE SSN NOT IN ( SELECT T1.patient FROM Prescribes AS T1 JOIN Medication AS T2 ON T1.Medication  =  T2.Code WHERE T2.name  =  'Procrastin-X' )

Question: How many appointments are there?
SQL: SELECT count(*) FROM appointment

Question: Count how many appointments have been made in total.
SQL: SELECT count(*) FROM appointment

Question: Find the names of nurses who are on call.
SQL: SELECT DISTINCT T1.name FROM nurse AS T1 JOIN on_call AS T2 ON T1.EmployeeID  =  T2.nurse

Question: What are the distinct names of nurses on call?
SQL: SELECT DISTINCT T1.name FROM nurse AS T1 JOIN on_call AS T2 ON T1.EmployeeID  =  T2.nurse