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
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
Find the locations that have more than one movie theater with capacity above 300.
assistant
SELECT LOCATION FROM cinema WHERE capacity > 300 GROUP BY LOCATION HAVING count(*) > 1
human
Show each location and the number of cinemas there.
assistant
SELECT LOCATION , count(*) FROM cinema GROUP BY LOCATION
human
Show the cinema name and location for cinemas with capacity above average.
assistant
SELECT name , LOCATION FROM cinema WHERE capacity > (SELECT avg(capacity) FROM cinema)
human
Show all the locations with at least two cinemas with capacity above 300.
assistant
SELECT LOCATION FROM cinema WHERE capacity > 300 GROUP BY LOCATION HAVING count(*) >= 2
human
Show all the locations where no cinema has capacity over 800.
assistant
SELECT LOCATION FROM cinema EXCEPT SELECT LOCATION FROM cinema WHERE capacity > 800 ;