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
What are the types of film market estimations in year 1995?
assistant
SELECT TYPE FROM film_market_estimation WHERE YEAR = 1995
human
What are the low and high estimates of film markets?
assistant
SELECT Low_Estimate , High_Estimate FROM film_market_estimation
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 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
Give the average number of cities within markets that had a low market estimation larger than 10000?
assistant
SELECT avg ( T2.Number_cities ) FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID WHERE T1.Low_Estimate > 10000 ;