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 title and maximum price of 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
human
Show all directors.
assistant
SELECT DISTINCT directed_by FROM film
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
List all directors along with the number of films directed by each director.
assistant
SELECT directed_by , count(*) FROM film GROUP BY directed_by
human
Show the title and director for all films.
assistant
SELECT title , directed_by FROM film
human
What are the title and director of the films without any schedule?
assistant
SELECT title , directed_by FROM film WHERE film_id NOT IN ( SELECT film_id FROM schedule ) ;