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
List the distinct director of all films.
assistant
SELECT DISTINCT Director FROM film
human
Please list the years of film market estimations when the market is in country 'Japan' in descending order.
assistant
SELECT T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID WHERE T2.Country = 'Japan' ORDER BY T1.Year DESC
human
Please list the countries and years of film market estimations.
assistant
SELECT T2.Country , T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID
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
What are the types of film market estimations in year 1995?
assistant
SELECT TYPE FROM film_market_estimation WHERE YEAR = 1995
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 ;