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 total number of rooms of all apartments with facility code 'Gym'.
assistant
SELECT sum(T2.room_count) FROM Apartment_Facilities AS T1 JOIN Apartments AS T2 ON T1.apt_id = T2.apt_id WHERE T1.facility_code = 'Gym'
human
Show the total number of rooms of the apartments in the building with short name 'Columbus Square'.
assistant
SELECT sum(T2.room_count) FROM Apartment_Buildings AS T1 JOIN Apartments AS T2 ON T1.building_id = T2.building_id WHERE T1.building_short_name = 'Columbus Square'
human
Show the addresses and phones of all the buildings managed by 'Brenden'.
assistant
SELECT building_address , building_phone FROM Apartment_Buildings WHERE building_manager = 'Brenden'
human
Show all distinct building descriptions.
assistant
SELECT DISTINCT building_description FROM Apartment_Buildings
human
Show the short names of the buildings managed by 'Emma'.
assistant
SELECT building_short_name FROM Apartment_Buildings WHERE building_manager = 'Emma'
human
Find all the building full names containing the word 'court'.
assistant
SELECT building_full_name FROM Apartment_Buildings WHERE building_full_name LIKE "%court%" ;