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 low and high estimates of film markets?
assistant
SELECT Low_Estimate , High_Estimate FROM film_market_estimation
human
List all countries of markets in ascending alphabetical order.
assistant
SELECT Country FROM market ORDER BY Country ASC
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 is the average number of cities of markets with low film market estimate bigger 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
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 ;