human
Schema:
Ref_Address_Types (address_type_code, address_type_description)
Ref_Detention_Type (detention_type_code, detention_type_description)
Ref_Incident_Type (incident_type_code, incident_type_description)
Addresses (address_id, line_1, line_2, line_3, city, zip_postcode, state_province_county, country, other_address_details)
Students (student_id, address_id, first_name, middle_name, last_name, cell_mobile_number, email_address, date_first_rental, date_left_university, other_student_details)
Teachers (teacher_id, address_id, first_name, middle_name, last_name, gender, cell_mobile_number, email_address, other_details)
Assessment_Notes (notes_id, student_id, teacher_id, date_of_notes, text_of_notes, other_details)
Behavior_Incident (incident_id, incident_type_code, student_id, date_incident_start, date_incident_end, incident_summary, recommendations, other_details)
Detention (detention_id, detention_type_code, teacher_id, datetime_detention_start, datetime_detention_end, detention_summary, other_details)
Student_Addresses (student_id, address_id, date_address_from, date_address_to, monthly_rental, other_details)
Students_in_Detention (student_id, detention_id, incident_id)
Please answer with SQL.
assistant
Sure, I understand.
human
How many distinct students have been in detention?
assistant
SELECT count(DISTINCT student_id) FROM Students_in_Detention
human
How many distinct incident type codes are there?
assistant
SELECT count(DISTINCT incident_type_code) FROM Behavior_Incident
human
Find the id and last name of the teacher that has the most detentions with detention type code 'AFTER'?
assistant
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
human
Return all detention summaries.
assistant
SELECT detention_summary FROM Detention
human
What are the code and description of the least frequent detention type ?
assistant
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
human
Return all distinct detention type codes.
assistant
SELECT DISTINCT detention_type_code FROM Detention ;