human
Schema:
film (Film_ID, Rank_in_series, Number_in_season, Title, Directed_by, Original_air_date, Production_code)
cinema (Cinema_ID, Name, Openning_year, Capacity, Location)
schedule (Cinema_ID, Film_ID, Date, Show_times_per_day, Price)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the average, minimum, and maximum capacity for all the cinemas opened in year 2011 or later.
assistant
SELECT avg(capacity) , min(capacity) , max(capacity) FROM cinema WHERE openning_year >= 2011
human
Show name, opening year, and capacity for each cinema.
assistant
SELECT name , openning_year , capacity FROM cinema
human
What are all the locations with a cinema?
assistant
SELECT DISTINCT LOCATION FROM cinema
human
What are the name and location of the cinema with the largest capacity?
assistant
SELECT name , LOCATION FROM cinema ORDER BY capacity DESC LIMIT 1
human
Show all the locations where some cinemas were opened in both year 2010 and year 2011.
assistant
SELECT LOCATION FROM cinema WHERE openning_year = 2010 INTERSECT SELECT LOCATION FROM cinema WHERE openning_year = 2011
human
What is the location with the most cinemas opened in year 2010 or later?
assistant
SELECT LOCATION FROM cinema WHERE openning_year >= 2010 GROUP BY LOCATION ORDER BY COUNT( * ) DESC LIMIT 1 ;