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
List all cities of addresses in alphabetical order.
assistant
SELECT city FROM Addresses ORDER BY city
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
human
What is the zip code of the address where the teacher with first name 'Lyla' lives?
assistant
SELECT T1.zip_postcode FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id = T2.address_id WHERE T2.first_name = 'Lyla'
human
What are the email addresses of teachers whose address has zip code '918'?
assistant
SELECT T2.email_address FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id = T2.address_id WHERE T1.zip_postcode = '918'
human
What are the id and zip code of the address with the highest monthly rental?
assistant
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
human
How many addresses have zip code 197?
assistant
SELECT COUNT( * ) FROM ADDRESSES WHERE zip_postcode = "197" ;