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
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
What are the maximum and minimum number of cities in all markets.
assistant
SELECT max(Number_cities) , min(Number_cities) FROM market
human
How many markets have number of cities smaller than 300?
assistant
SELECT count(*) FROM market WHERE Number_cities < 300
human
List all countries of markets in descending order of number of cities.
assistant
SELECT Country FROM market ORDER BY Number_cities DESC
human
What are the countries for each market, ordered alphabetically?
assistant
SELECT Country FROM market ORDER BY Country ASC ;