human
Schema:
film (Film_ID, Title, Studio, Director, Gross_in_dollar)
market (Market_ID, Country, Number_cities)
film_market_estimation (Estimation_ID, Low_Estimate, High_Estimate, Film_ID, Type, Market_ID, Year)
Please answer with SQL.
assistant
Sure, I understand.
human
How many film are there?
assistant
SELECT count(*) FROM film
human
What are the low and high estimates of film markets?
assistant
SELECT Low_Estimate , High_Estimate FROM film_market_estimation
human
List the title of films that do not have any market estimation.
assistant
SELECT Title FROM film WHERE Film_ID NOT IN (SELECT Film_ID FROM film_market_estimation)
human
Show the distinct director of films with market estimation in the year of 1995.
assistant
SELECT DISTINCT T1.Director FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID = T2.Film_ID WHERE T2.Year = 1995
human
Please show the titles of films and the types of market estimations.
assistant
SELECT T1.Title , T2.Type FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID = T2.Film_ID
human
Return the title of the film with the highest high estimate?
assistant
SELECT t1.title FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID = T2.Film_ID ORDER BY high_estimate DESC LIMIT 1 ;