human
Schema:
actor (actor_id, first_name, last_name, last_update)
address (address_id, address, address2, district, city_id, postal_code, phone, last_update)
category (category_id, name, last_update)
city (city_id, city, country_id, last_update)
country (country_id, country, last_update)
customer (customer_id, store_id, first_name, last_name, email, address_id, active, create_date, last_update)
film (film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update)
film_actor (actor_id, film_id, last_update)
film_category (film_id, category_id, last_update)
film_text (film_id, title, description)
inventory (inventory_id, film_id, store_id, last_update)
language (language_id, name, last_update)
payment (payment_id, customer_id, staff_id, rental_id, amount, payment_date, last_update)
rental (rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update)
staff (staff_id, first_name, last_name, address_id, picture, email, store_id, active, username, password, last_update)
store (store_id, manager_staff_id, address_id, last_update)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the phone number and postal code of the address 1031 Daugavpils Parkway?
assistant
SELECT phone , postal_code FROM address WHERE address = '1031 Daugavpils Parkway'
human
How many cities are in Australia?
assistant
SELECT count(*) FROM city AS T1 JOIN country AS T2 ON T1.country_id = T2.country_id WHERE T2.country = 'Australia'
human
Which countries have at least 3 cities?
assistant
SELECT T2.country FROM city AS T1 JOIN country AS T2 ON T1.country_id = T2.country_id GROUP BY T2.country_id HAVING count(*) >= 3
human
How many addresses are in the district of California?
assistant
SELECT count(*) FROM address WHERE district = 'California'
human
Which districts have at least two addresses?
assistant
SELECT district FROM address GROUP BY district HAVING count(*) >= 2
human
What are the city name, id, and number of addresses corresponding to the city with the most addressed?
assistant
SELECT T2.city , COUNT( * ) , T1.city_id FROM address AS T1 JOIN city AS T2 ON T1.city_id = T2.city_id GROUP BY T1.city_id ORDER BY COUNT( * ) DESC LIMIT 1 ;