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: How many assessment notes are there in total?
SQL: SELECT count(*) FROM ASSESSMENT_NOTES

Question: What are the dates of the assessment notes?
SQL: SELECT date_of_notes FROM Assessment_Notes

Question: How many addresses have zip code 197?
SQL: SELECT count(*) FROM ADDRESSES WHERE zip_postcode  =  "197"

Question: How many distinct incident type codes are there?
SQL: SELECT count(DISTINCT incident_type_code) FROM Behavior_Incident

Question: Return all distinct detention type codes.
SQL: SELECT DISTINCT detention_type_code FROM Detention

Question: What are the start and end dates for incidents with incident type code "NOISE"?
SQL: SELECT date_incident_start ,  date_incident_end FROM Behavior_Incident WHERE incident_type_code  =  "NOISE"

Question: Return all detention summaries.
SQL: SELECT detention_summary FROM Detention

Question: Return the cell phone number and email address for all students.
SQL: SELECT cell_mobile_number ,  email_address FROM STUDENTS

Question: What is the email of the student with first name "Emma" and last name "Rohan"?
SQL: SELECT email_address FROM Students WHERE first_name  =  "Emma" AND last_name  =  "Rohan"

Question: How many distinct students have been in detention?
SQL: SELECT count(DISTINCT student_id) FROM Students_in_Detention

Question: What is the gender of the teacher with last name "Medhurst"?
SQL: SELECT gender FROM TEACHERS WHERE last_name  =  "Medhurst"

Question: What is the incident type description for the incident type with code "VIOLENCE"?
SQL: SELECT incident_type_description FROM Ref_Incident_Type WHERE incident_type_code  =  "VIOLENCE"

Question: Find the maximum and minimum monthly rental for all student addresses.
SQL: SELECT max(monthly_rental) ,  min(monthly_rental) FROM Student_Addresses

Question: Find the first names of teachers whose email address contains the word "man".
SQL: SELECT first_name FROM Teachers WHERE email_address LIKE '%man%'

Question: List all information about the assessment notes sorted by date in ascending order.
SQL: SELECT * FROM Assessment_Notes ORDER BY date_of_notes ASC

Question: List all cities of addresses in alphabetical order.
SQL: SELECT city FROM Addresses ORDER BY city

Question: Find the first names and last names of teachers in alphabetical order of last name.
SQL: SELECT first_name ,  last_name FROM Teachers ORDER BY last_name

Question: Find all information about student addresses, and sort by monthly rental in descending order.
SQL: SELECT * FROM Student_Addresses ORDER BY monthly_rental DESC

Question: Find the id and first name of the student that has the most number of assessment notes?
SQL: SELECT T1.student_id ,  T2.first_name FROM Assessment_Notes AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question: Find the ids and first names of the 3 teachers that have the most number of assessment notes?
SQL: SELECT T1.teacher_id ,  T2.first_name FROM Assessment_Notes AS T1 JOIN Teachers AS T2 ON T1.teacher_id  =  T2.teacher_id GROUP BY T1.teacher_id ORDER BY count(*) DESC LIMIT 3

Question: Find the id and last name of the student that has the most behavior incidents?
SQL: SELECT T1.student_id ,  T2.last_name FROM Behavior_Incident AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question: Find the id and last name of the teacher that has the most detentions with detention type code "AFTER"?
SQL: SELECT T1.teacher_id ,  T2.last_name FROM Detention AS T1 JOIN Teachers AS T2 ON T1.teacher_id  =  T2.teacher_id WHERE T1.detention_type_code  =  "AFTER" GROUP BY T1.teacher_id ORDER BY count(*) DESC LIMIT 1

Question: What are the id and first name of the student whose addresses have the highest average monthly rental?
SQL: SELECT T1.student_id ,  T2.first_name FROM Student_Addresses AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY AVG(monthly_rental) DESC LIMIT 1

Question: Find the id and city of the student address with the highest average monthly rental.
SQL: SELECT T2.address_id ,  T1.city FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id GROUP BY T2.address_id ORDER BY AVG(monthly_rental) DESC LIMIT 1

Question: What are the code and description of the most frequent behavior incident type?
SQL: SELECT T1.incident_type_code ,  T2.incident_type_description FROM Behavior_Incident AS T1 JOIN Ref_Incident_Type AS T2 ON T1.incident_type_code  =  T2.incident_type_code GROUP BY T1.incident_type_code ORDER BY count(*) DESC LIMIT 1

Question: What are the code and description of the least frequent detention type ?
SQL: SELECT T1.detention_type_code ,  T2.detention_type_description FROM Detention AS T1 JOIN Ref_Detention_Type AS T2 ON T1.detention_type_code  =  T2.detention_type_code GROUP BY T1.detention_type_code ORDER BY count(*) ASC LIMIT 1