Database: behavior_monitoring
Schema: Ref_Address_Types: address_type_code (text), address_type_description (text) | Ref_Detention_Type: detention_type_code (text), detention_type_description (text) | Ref_Incident_Type: incident_type_code (text), incident_type_description (text) | Addresses: address_id (number), line_1 (text), line_2 (text), line_3 (text), city (text), zip_postcode (text), state_province_county (text), country (text), other_address_details (text) | Students: student_id (number), 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) | Teachers: teacher_id (number), address_id (number), first_name (text), middle_name (text), last_name (text), gender (text), cell_mobile_number (text), email_address (text), other_details (text) | Assessment_Notes: notes_id (number), student_id (number), teacher_id (number), date_of_notes (time), text_of_notes (text), other_details (text) | Behavior_Incident: incident_id (number), incident_type_code (text), student_id (number), date_incident_start (time), date_incident_end (time), incident_summary (text), recommendations (text), other_details (text) | Detention: detention_id (number), detention_type_code (text), teacher_id (number), datetime_detention_start (time), datetime_detention_end (time), detention_summary (text), other_details (text) | Student_Addresses: student_id (number), address_id (number), date_address_from (time), date_address_to (time), monthly_rental (number), other_details (text) | Students_in_Detention: student_id (number), detention_id (number), incident_id (number)

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

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