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
What are the monthly rentals of student addresses in Texas state?
assistant
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'
human
Find the id and city of the student address with the highest average monthly rental.
assistant
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
human
Find the maximum and minimum monthly rental for all student addresses.
assistant
SELECT max(monthly_rental) , min(monthly_rental) FROM Student_Addresses
human
What are the line 1 and average monthly rentals of all student addresses?
assistant
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
human
What are the id and first name of the student whose addresses have the highest average monthly rental?
assistant
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
human
What is the cell phone number of the student whose address has the lowest monthly rental?
assistant
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 ;