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 all countries of markets in descending order of number of cities.
assistant
SELECT Country FROM market ORDER BY Number_cities DESC
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
List all countries of markets in ascending alphabetical order.
assistant
SELECT Country FROM market ORDER BY Country ASC
human
What are the types of film market estimations in year 1995?
assistant
SELECT TYPE FROM film_market_estimation WHERE YEAR = 1995
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
What are the countries of markets and their corresponding years of market estimation?
assistant
SELECT T2.Country , T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID ;