human
Schema:
Apartment_Buildings (building_id, building_short_name, building_full_name, building_description, building_address, building_manager, building_phone)
Apartments (apt_id, building_id, apt_type_code, apt_number, bathroom_count, bedroom_count, room_count)
Apartment_Facilities (apt_id, facility_code)
Guests (guest_id, gender_code, guest_first_name, guest_last_name, date_of_birth)
Apartment_Bookings (apt_booking_id, apt_id, guest_id, booking_status_code, booking_start_date, booking_end_date)
View_Unit_Status (apt_id, apt_booking_id, status_date, available_yn)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the apartment type codes and the corresponding number of apartments sorted by the number of apartments in ascending order.
assistant
SELECT apt_type_code , COUNT(*) FROM Apartments GROUP BY apt_type_code ORDER BY COUNT(*) ASC
human
Show the top 3 apartment type codes sorted by the average number of rooms in descending order.
assistant
SELECT apt_type_code FROM Apartments GROUP BY apt_type_code ORDER BY avg(room_count) DESC LIMIT 3
human
Return the first names and last names of all guests
assistant
SELECT guest_first_name , guest_last_name FROM Guests
human
Show the start dates and end dates of all the apartment bookings made by guests with gender code 'Female'.
assistant
SELECT T1.booking_start_date , T1.booking_start_date FROM Apartment_Bookings AS T1 JOIN Guests AS T2 ON T1.guest_id = T2.guest_id WHERE T2.gender_code = 'Female'
human
Return the date of birth for all the guests with gender code 'Male'.
assistant
SELECT date_of_birth FROM Guests WHERE gender_code = 'Male'
human
Sort the gender codes in descending order of their corresponding number of guests. Return both the gender codes and counts.
assistant
SELECT gender_code , COUNT( * ) FROM Guests GROUP BY gender_code ORDER BY COUNT( * ) DESC ;