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: 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"