Database: behavior_monitoring

CREATE TABLE `Ref_Address_Types` (
`address_type_code` VARCHAR(15) PRIMARY KEY,
`address_type_description` VARCHAR(80)
);
CREATE TABLE `Ref_Detention_Type` (
`detention_type_code` VARCHAR(10) PRIMARY KEY,
`detention_type_description` VARCHAR(80)
);
CREATE TABLE `Ref_Incident_Type` (
`incident_type_code` VARCHAR(10) PRIMARY KEY,
`incident_type_description` VARCHAR(80)
);
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1` VARCHAR(120),
`line_2` VARCHAR(120),
`line_3` VARCHAR(120),
`city` VARCHAR(80),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
);
CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`address_id` INTEGER NOT NULL,
`first_name` VARCHAR(80),
`middle_name` VARCHAR(40),
`last_name` VARCHAR(40),
`cell_mobile_number` VARCHAR(40),
`email_address` VARCHAR(40),
`date_first_rental` DATETIME,
`date_left_university` DATETIME,
`other_student_details` VARCHAR(255),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Teachers` (
`teacher_id` INTEGER PRIMARY KEY,
`address_id` INTEGER NOT NULL,
`first_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender` VARCHAR(1),
`cell_mobile_number` VARCHAR(40),
`email_address` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Assessment_Notes` (
`notes_id` INTEGER NOT NULL ,
`student_id` INTEGER,
`teacher_id` INTEGER NOT NULL,
`date_of_notes` DATETIME,
`text_of_notes` VARCHAR(255),
`other_details` VARCHAR(255),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` ),
FOREIGN KEY (`teacher_id` ) REFERENCES `Teachers`(`teacher_id` )
);
CREATE TABLE `Behavior_Incident` (
`incident_id` INTEGER PRIMARY KEY,
`incident_type_code` VARCHAR(10) NOT NULL,
`student_id` INTEGER NOT NULL,
`date_incident_start` DATETIME,
`date_incident_end` DATETIME,
`incident_summary` VARCHAR(255),
`recommendations` VARCHAR(255),
`other_details` VARCHAR(255),
FOREIGN KEY (`incident_type_code` ) REFERENCES `Ref_Incident_Type`(`incident_type_code` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
);
CREATE TABLE `Detention` (
`detention_id` INTEGER PRIMARY KEY,
`detention_type_code` VARCHAR(10) NOT NULL,
`teacher_id` INTEGER,
`datetime_detention_start` DATETIME,
`datetime_detention_end` DATETIME,
`detention_summary` VARCHAR(255),
`other_details` VARCHAR(255),
FOREIGN KEY (`detention_type_code` ) REFERENCES `Ref_Detention_Type`(`detention_type_code` ),
FOREIGN KEY (`teacher_id` ) REFERENCES `Teachers`(`teacher_id` )
);
CREATE TABLE `Student_Addresses` (
`student_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`date_address_to` DATETIME,
`monthly_rental` DECIMAL(19,4),
`other_details` VARCHAR(255),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
);
CREATE TABLE `Students_in_Detention` (
`student_id` INTEGER NOT NULL,
`detention_id` INTEGER NOT NULL,
`incident_id` INTEGER NOT NULL,
FOREIGN KEY (`incident_id` ) REFERENCES `Behavior_Incident`(`incident_id` ),
FOREIGN KEY (`detention_id` ) REFERENCES `Detention`(`detention_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_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

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