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
How many cinema do we have?
assistant
SELECT count(*) FROM cinema
human
Show the title and director for all films.
assistant
SELECT title , directed_by FROM film
human
Show name, opening year, and capacity for each cinema.
assistant
SELECT name , openning_year , capacity FROM cinema
human
Show cinema name, film title, date, and price for each record in schedule.
assistant
SELECT T3.name , T2.title , T1.date , T1.price FROM schedule AS T1 JOIN film AS T2 ON T1.film_id = T2.film_id JOIN cinema AS T3 ON T1.cinema_id = T3.cinema_id
human
Give me the title and highest price for each film.
assistant
SELECT T2.title , MAX( T1.price ) FROM schedule AS T1 JOIN film AS T2 ON T1.film_id = T2.film_id GROUP BY T1.film_id ;