Database: hospital_1

CREATE TABLE Physician (
  EmployeeID INTEGER NOT NULL,
  Name VARCHAR(30) NOT NULL,
  Position VARCHAR(30) NOT NULL,
  SSN INTEGER NOT NULL,
  CONSTRAINT pk_physician PRIMARY KEY(EmployeeID)
);
CREATE TABLE Department (
  DepartmentID INTEGER NOT NULL,
  Name VARCHAR(30) NOT NULL,
  Head INTEGER NOT NULL,
  CONSTRAINT pk_Department PRIMARY KEY(DepartmentID),
  CONSTRAINT fk_Department_Physician_EmployeeID FOREIGN KEY(Head) REFERENCES Physician(EmployeeID)
);
CREATE TABLE Affiliated_With (
  Physician INTEGER NOT NULL,
  Department INTEGER NOT NULL,
  PrimaryAffiliation BOOLEAN NOT NULL,
  CONSTRAINT fk_Affiliated_With_Physician_EmployeeID FOREIGN KEY(Physician) REFERENCES Physician(EmployeeID),
  CONSTRAINT fk_Affiliated_With_Department_DepartmentID FOREIGN KEY(Department) REFERENCES Department(DepartmentID),
  PRIMARY KEY(Physician, Department)
);
CREATE TABLE Procedures (
  Code INTEGER PRIMARY KEY NOT NULL,
  Name VARCHAR(30) NOT NULL,
  Cost REAL NOT NULL
);
CREATE TABLE Trained_In (
  Physician INTEGER NOT NULL,
  Treatment INTEGER NOT NULL,
  CertificationDate DATETIME NOT NULL,
  CertificationExpires DATETIME NOT NULL,
  CONSTRAINT fk_Trained_In_Physician_EmployeeID FOREIGN KEY(Physician) REFERENCES Physician(EmployeeID),
  CONSTRAINT fk_Trained_In_Procedures_Code FOREIGN KEY(Treatment) REFERENCES Procedures(Code),
  PRIMARY KEY(Physician, Treatment)
);
CREATE TABLE Patient (
  SSN INTEGER PRIMARY KEY NOT NULL,
  Name VARCHAR(30) NOT NULL,
  Address VARCHAR(30) NOT NULL,
  Phone VARCHAR(30) NOT NULL,
  InsuranceID INTEGER NOT NULL,
  PCP INTEGER NOT NULL,
  CONSTRAINT fk_Patient_Physician_EmployeeID FOREIGN KEY(PCP) REFERENCES Physician(EmployeeID)
);
CREATE TABLE Nurse (
  EmployeeID INTEGER PRIMARY KEY NOT NULL,
  Name VARCHAR(30) NOT NULL,
  Position VARCHAR(30) NOT NULL,
  Registered BOOLEAN NOT NULL,
  SSN INTEGER NOT NULL
);
CREATE TABLE Appointment (
  AppointmentID INTEGER PRIMARY KEY NOT NULL,
  Patient INTEGER NOT NULL,    
  PrepNurse INTEGER,
  Physician INTEGER NOT NULL,
  Start DATETIME NOT NULL,
  End DATETIME NOT NULL,
  ExaminationRoom TEXT NOT NULL,
  CONSTRAINT fk_Appointment_Patient_SSN FOREIGN KEY(Patient) REFERENCES Patient(SSN),
  CONSTRAINT fk_Appointment_Nurse_EmployeeID FOREIGN KEY(PrepNurse) REFERENCES Nurse(EmployeeID),
  CONSTRAINT fk_Appointment_Physician_EmployeeID FOREIGN KEY(Physician) REFERENCES Physician(EmployeeID)
);
CREATE TABLE Medication (
  Code INTEGER PRIMARY KEY NOT NULL,
  Name VARCHAR(30) NOT NULL,
  Brand VARCHAR(30) NOT NULL,
  Description VARCHAR(30) NOT NULL
);
CREATE TABLE Prescribes (
  Physician INTEGER NOT NULL,
  Patient INTEGER NOT NULL, 
  Medication INTEGER NOT NULL, 
  Date DATETIME NOT NULL,
  Appointment INTEGER,  
  Dose VARCHAR(30) NOT NULL,
  PRIMARY KEY(Physician, Patient, Medication, Date),
  CONSTRAINT fk_Prescribes_Physician_EmployeeID FOREIGN KEY(Physician) REFERENCES Physician(EmployeeID),
  CONSTRAINT fk_Prescribes_Patient_SSN FOREIGN KEY(Patient) REFERENCES Patient(SSN),
  CONSTRAINT fk_Prescribes_Medication_Code FOREIGN KEY(Medication) REFERENCES Medication(Code),
  CONSTRAINT fk_Prescribes_Appointment_AppointmentID FOREIGN KEY(Appointment) REFERENCES Appointment(AppointmentID)
);
CREATE TABLE Block (
  BlockFloor INTEGER NOT NULL,
  BlockCode INTEGER NOT NULL,
  PRIMARY KEY(BlockFloor, BlockCode)
);
CREATE TABLE Room (
  RoomNumber INTEGER PRIMARY KEY NOT NULL,
  RoomType VARCHAR(30) NOT NULL,
  BlockFloor INTEGER NOT NULL,  
  BlockCode INTEGER NOT NULL,  
  Unavailable BOOLEAN NOT NULL,
  CONSTRAINT fk_Room_Block_PK FOREIGN KEY(BlockFloor, BlockCode) REFERENCES Block(BlockFloor, BlockCode)
);
CREATE TABLE On_Call (
  Nurse INTEGER NOT NULL,
  BlockFloor INTEGER NOT NULL, 
  BlockCode INTEGER NOT NULL,
  OnCallStart DATETIME NOT NULL,
  OnCallEnd DATETIME NOT NULL,
  PRIMARY KEY(Nurse, BlockFloor, BlockCode, OnCallStart, OnCallEnd),
  CONSTRAINT fk_OnCall_Nurse_EmployeeID FOREIGN KEY(Nurse) REFERENCES Nurse(EmployeeID),
  CONSTRAINT fk_OnCall_Block_Floor FOREIGN KEY(BlockFloor, BlockCode) REFERENCES Block(BlockFloor, BlockCode) 
);
CREATE TABLE Stay (
  StayID INTEGER PRIMARY KEY NOT NULL,
  Patient INTEGER NOT NULL,
  Room INTEGER NOT NULL,
  StayStart DATETIME NOT NULL,
  StayEnd DATETIME NOT NULL,
  CONSTRAINT fk_Stay_Patient_SSN FOREIGN KEY(Patient) REFERENCES Patient(SSN),
  CONSTRAINT fk_Stay_Room_Number FOREIGN KEY(Room) REFERENCES Room(RoomNumber)
);
CREATE TABLE Undergoes (
  Patient INTEGER NOT NULL,
  Procedures INTEGER NOT NULL,
  Stay INTEGER NOT NULL,
  DateUndergoes DATETIME NOT NULL,
  Physician INTEGER NOT NULL,
  AssistingNurse INTEGER,
  PRIMARY KEY(Patient, Procedures, Stay, DateUndergoes),
  CONSTRAINT fk_Undergoes_Patient_SSN FOREIGN KEY(Patient) REFERENCES Patient(SSN),
  CONSTRAINT fk_Undergoes_Procedures_Code FOREIGN KEY(Procedures) REFERENCES Procedures(Code),
  CONSTRAINT fk_Undergoes_Stay_StayID FOREIGN KEY(Stay) REFERENCES Stay(StayID),
  CONSTRAINT fk_Undergoes_Physician_EmployeeID FOREIGN KEY(Physician) REFERENCES Physician(EmployeeID),
  CONSTRAINT fk_Undergoes_Nurse_EmployeeID FOREIGN KEY(AssistingNurse) REFERENCES Nurse(EmployeeID)
);

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

Question: How many patients' prescriptions are made by physician John Dorian?
SQL: SELECT count(T1.SSN) FROM patient AS T1 JOIN prescribes AS T2 ON T1.SSN  =  T2.patient JOIN physician AS T3 ON T2.physician  =  T3.employeeid WHERE T3.name = "John Dorian"

Question: Find the number of patients' prescriptions physician John Dorian made.
SQL: SELECT count(T1.SSN) FROM patient AS T1 JOIN prescribes AS T2 ON T1.SSN  =  T2.patient JOIN physician AS T3 ON T2.physician  =  T3.employeeid WHERE T3.name = "John Dorian"

Question: Find the name of medication used on the patient who stays in room 111?
SQL: SELECT T4.name FROM stay AS T1 JOIN patient AS T2 ON T1.Patient  =  T2.SSN JOIN Prescribes AS T3 ON T3.Patient  =  T2.SSN JOIN Medication AS T4 ON T3.Medication  =  T4.Code WHERE room  =  111

Question: What is the name of the medication used for the patient staying in room 111?
SQL: SELECT T4.name FROM stay AS T1 JOIN patient AS T2 ON T1.Patient  =  T2.SSN JOIN Prescribes AS T3 ON T3.Patient  =  T2.SSN JOIN Medication AS T4 ON T3.Medication  =  T4.Code WHERE room  =  111

Question: Find the patient who most recently stayed in room 111.
SQL: SELECT patient FROM stay WHERE room  =  111 ORDER BY staystart DESC LIMIT 1

Question: What is the id of the patient who stayed in room 111 most recently?
SQL: SELECT patient FROM stay WHERE room  =  111 ORDER BY staystart DESC LIMIT 1

Question: What is the name of the nurse has the most appointments?
SQL: SELECT T1.name FROM nurse AS T1 JOIN appointment AS T2 ON T1.employeeid  =  T2.prepnurse GROUP BY T1.employeeid ORDER BY count(*) DESC LIMIT 1

Question: Find the name of the nurse who has the largest number of appointments.
SQL: SELECT T1.name FROM nurse AS T1 JOIN appointment AS T2 ON T1.employeeid  =  T2.prepnurse GROUP BY T1.employeeid ORDER BY count(*) DESC LIMIT 1

Question: How many patients do each physician take care of? List their names and number of patients they take care of.
SQL: SELECT T1.name ,  count(*) FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid  =  T2.PCP GROUP BY T1.employeeid

Question: Return the name of each physician and the number of patients he or she treats.
SQL: SELECT T1.name ,  count(*) FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid  =  T2.PCP GROUP BY T1.employeeid

Question: Find the name of physicians who are in charge of more than one patient.
SQL: SELECT T1.name FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid  =  T2.PCP GROUP BY T1.employeeid HAVING count(*)  >  1

Question: Which physicians are in charge of more than one patient? Give me their names.
SQL: SELECT T1.name FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid  =  T2.PCP GROUP BY T1.employeeid HAVING count(*)  >  1

Question: Find the number of rooms located on each block floor.
SQL: SELECT count(*) ,  T1.blockfloor FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockfloor

Question: How many rooms does each block floor have?
SQL: SELECT count(*) ,  T1.blockfloor FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockfloor

Question: Find the number of rooms for different block code?
SQL: SELECT count(*) ,  T1.blockcode FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockcode

Question: How many rooms are located for each block code?
SQL: SELECT count(*) ,  T1.blockcode FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockcode

Question: What are the unique block codes that have available rooms?
SQL: SELECT DISTINCT blockcode FROM room WHERE unavailable  =  0

Question: Tell me the distinct block codes where some rooms are available.
SQL: SELECT DISTINCT blockcode FROM room WHERE unavailable  =  0

Question: How many different types of rooms are there?
SQL: SELECT count(DISTINCT roomtype) FROM room

Question: Find the number of distinct room types available.
SQL: SELECT count(DISTINCT roomtype) FROM room

Question: What is the names of the physicians who prescribe medication Thesisin?
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: 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

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"

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