CREATE TABLE Ref_Address_Types (
    address_type_code TEXT PRIMARY KEY,
    address_type_description TEXT
);
CREATE TABLE Ref_Detention_Type (
    detention_type_code TEXT PRIMARY KEY,
    detention_type_description TEXT
);
CREATE TABLE Ref_Incident_Type (
    incident_type_code TEXT PRIMARY KEY,
    incident_type_description TEXT
);
CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    line_1 TEXT,
    line_2 TEXT,
    line_3 TEXT,
    city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT,
    other_address_details TEXT
);
CREATE TABLE Students (
    student_id NUMBER PRIMARY KEY,
    address_id NUMBER,
    first_name TEXT,
    middle_name TEXT,
    last_name TEXT,
    cell_mobile_number TEXT,
    email_address TEXT,
    date_first_rental TIME,
    date_left_university TIME,
    other_student_details TEXT,
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Teachers (
    teacher_id NUMBER PRIMARY KEY,
    address_id NUMBER,
    first_name TEXT,
    middle_name TEXT,
    last_name TEXT,
    gender TEXT,
    cell_mobile_number TEXT,
    email_address TEXT,
    other_details TEXT,
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Assessment_Notes (
    notes_id NUMBER,
    student_id NUMBER,
    teacher_id NUMBER,
    date_of_notes TIME,
    text_of_notes TEXT,
    other_details TEXT,
    FOREIGN KEY ("teacher_id") REFERENCES Teachers("teacher_id"),
    FOREIGN KEY ("student_id") REFERENCES Students("student_id")
);
CREATE TABLE Behavior_Incident (
    incident_id NUMBER PRIMARY KEY,
    incident_type_code TEXT,
    student_id NUMBER,
    date_incident_start TIME,
    date_incident_end TIME,
    incident_summary TEXT,
    recommendations TEXT,
    other_details TEXT,
    FOREIGN KEY ("student_id") REFERENCES Students("student_id"),
    FOREIGN KEY ("incident_type_code") REFERENCES Ref_Incident_Type("incident_type_code")
);
CREATE TABLE Detention (
    detention_id NUMBER PRIMARY KEY,
    detention_type_code TEXT,
    teacher_id NUMBER,
    datetime_detention_start TIME,
    datetime_detention_end TIME,
    detention_summary TEXT,
    other_details TEXT,
    FOREIGN KEY ("teacher_id") REFERENCES Teachers("teacher_id"),
    FOREIGN KEY ("detention_type_code") REFERENCES Ref_Detention_Type("detention_type_code")
);
CREATE TABLE Student_Addresses (
    student_id NUMBER,
    address_id NUMBER,
    date_address_from TIME,
    date_address_to TIME,
    monthly_rental NUMBER,
    other_details TEXT,
    FOREIGN KEY ("student_id") REFERENCES Students("student_id"),
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Students_in_Detention (
    student_id NUMBER,
    detention_id NUMBER,
    incident_id NUMBER,
    FOREIGN KEY ("student_id") REFERENCES Students("student_id"),
    FOREIGN KEY ("detention_id") REFERENCES Detention("detention_id"),
    FOREIGN KEY ("incident_id") REFERENCES Behavior_Incident("incident_id")
);

Question: Find the dates of assessment notes for students with first name "Fanny".
SQL: SELECT T1.date_of_notes FROM Assessment_Notes AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.first_name  =  "Fanny"

Question: Find the texts of assessment notes for teachers with last name "Schuster".
SQL: SELECT T1.text_of_notes FROM Assessment_Notes AS T1 JOIN Teachers AS T2 ON T1.teacher_id  =  T2.teacher_id WHERE T2.last_name  =  "Schuster"

Question: Find the start and end dates of behavior incidents of students with last name "Fahey".
SQL: SELECT T1.date_incident_start ,  date_incident_end FROM Behavior_Incident AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.last_name  =  "Fahey"

Question: Find the start and end dates of detentions of teachers with last name "Schultz".
SQL: SELECT T1.datetime_detention_start ,  datetime_detention_end FROM Detention AS T1 JOIN Teachers AS T2 ON T1.teacher_id  =  T2.teacher_id WHERE T2.last_name  =  "Schultz"

Question: What are the id and zip code of the address with the highest monthly rental?
SQL: SELECT T2.address_id ,  T1.zip_postcode FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id ORDER BY monthly_rental DESC LIMIT 1

Question: What is the cell phone number of the student whose address has the lowest monthly rental?
SQL: SELECT T2.cell_mobile_number FROM Student_Addresses AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id ORDER BY T1.monthly_rental ASC LIMIT 1

Question: What are the monthly rentals of student addresses in Texas state?
SQL: SELECT T2.monthly_rental FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id WHERE T1.state_province_county  =  "Texas"

Question: What are the first names and last names of students with address in Wisconsin state?
SQL: SELECT T2.first_name ,  T2.last_name FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.address_id WHERE T1.state_province_county  =  "Wisconsin"

Question: What are the line 1 and average monthly rentals of all student addresses?
SQL: SELECT T1.line_1 ,  avg(T2.monthly_rental) FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id GROUP BY T2.address_id

Question: What is the zip code of the address where the teacher with first name "Lyla" lives?
SQL: SELECT T1.zip_postcode FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id WHERE T2.first_name  =  "Lyla"

Question: What are the email addresses of teachers whose address has zip code "918"?
SQL: SELECT T2.email_address FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id WHERE T1.zip_postcode  =  "918"

Question: How many students are not involved in any behavior incident?
SQL: SELECT count(*) FROM STUDENTS WHERE student_id NOT IN ( SELECT student_id FROM Behavior_Incident )

Question: Find the last names of teachers who are not involved in any detention.
SQL: SELECT last_name FROM Teachers EXCEPT SELECT T1.last_name FROM Teachers AS T1 JOIN Detention AS T2 ON T1.teacher_id  =  T2.teacher_id

Question: What are the line 1 of addresses shared by some students and some teachers?
SQL: SELECT T1.line_1 FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.address_id INTERSECT SELECT T1.line_1 FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id