Database: film_rank

CREATE TABLE "film" (
"Film_ID" int,
"Title" text,
"Studio" text,
"Director" text,
"Gross_in_dollar" int,
PRIMARY KEY ("Film_ID")
);
CREATE TABLE "market" (
"Market_ID" int,
"Country" text,
"Number_cities" int,
PRIMARY KEY ("Market_ID")
);
CREATE TABLE "film_market_estimation" (
"Estimation_ID" int,
"Low_Estimate" real,
"High_Estimate" real,
"Film_ID" int,
"Type" text,
"Market_ID" int,
"Year" int,
PRIMARY KEY ("Estimation_ID"),
FOREIGN KEY ("Film_ID") REFERENCES film("Film_ID"),
FOREIGN KEY ("Market_ID") REFERENCES market("Market_ID")
);

Question: How many film are there?
SQL: SELECT count(*) FROM film

Question: Count the number of films.
SQL: SELECT count(*) FROM film

Question: List the distinct director of all films.
SQL: SELECT DISTINCT Director FROM film

Question: What are the different film Directors?
SQL: SELECT DISTINCT Director FROM film

Question: What is the average ticket sales gross in dollars of films?
SQL: SELECT avg(Gross_in_dollar) FROM film

Question: Return the average gross sales in dollars across all films.
SQL: SELECT avg(Gross_in_dollar) FROM film

Question: What are the low and high estimates of film markets?
SQL: SELECT Low_Estimate ,  High_Estimate FROM film_market_estimation

Question: Return the low and high estimates for all film markets.
SQL: SELECT Low_Estimate ,  High_Estimate FROM film_market_estimation

Question: What are the types of film market estimations in year 1995?
SQL: SELECT TYPE FROM film_market_estimation WHERE YEAR  =  1995

Question: Return the types of film market estimations in 1995.
SQL: SELECT TYPE FROM film_market_estimation WHERE YEAR  =  1995

Question: What are the maximum and minimum number of cities in all markets.
SQL: SELECT max(Number_cities) ,  min(Number_cities) FROM market

Question: Return the maximum and minimum number of cities across all markets.
SQL: SELECT max(Number_cities) ,  min(Number_cities) FROM market

Question: How many markets have number of cities smaller than 300?
SQL: SELECT count(*) FROM market WHERE Number_cities  <  300

Question: Count the number of markets that have a number of cities lower than 300.
SQL: SELECT count(*) FROM market WHERE Number_cities  <  300

Question: List all countries of markets in ascending alphabetical order.
SQL: SELECT Country FROM market ORDER BY Country ASC

Question: What are the countries for each market, ordered alphabetically?
SQL: SELECT Country FROM market ORDER BY Country ASC

Question: List all countries of markets in descending order of number of cities.
SQL: SELECT Country FROM market ORDER BY Number_cities DESC

Question: What are the countries for each market ordered by decreasing number of cities?
SQL: SELECT Country FROM market ORDER BY Number_cities DESC

Question: Please show the titles of films and the types of market estimations.
SQL: SELECT T1.Title ,  T2.Type FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID  =  T2.Film_ID

Question: What are the titles of films and corresponding types of market estimations?
SQL: SELECT T1.Title ,  T2.Type FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID  =  T2.Film_ID

Question: Show the distinct director of films with market estimation in the year of 1995.
SQL: 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

Question: Who are the different directors of films which had market estimation in 1995?
SQL: 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

Question: What is the average number of cities of markets with low film market estimate bigger than 10000?
SQL: 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

Question: Give the average number of cities within markets that had a low market estimation larger than 10000?
SQL: 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

Question: Please list the countries and years of film market estimations.
SQL: SELECT T2.Country ,  T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID

Question: What are the countries of markets and their corresponding years of market estimation?
SQL: SELECT T2.Country ,  T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID

Question: Please list the years of film market estimations when the market is in country "Japan" in descending order.
SQL: 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

Question: What are the years of film market estimation for the market of Japan, ordered by year descending?
SQL: 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

Question: List the studios of each film and the number of films produced by that studio.
SQL: SELECT Studio ,  COUNT(*) FROM film GROUP BY Studio

Question: How films are produced by each studio?
SQL: SELECT Studio ,  COUNT(*) FROM film GROUP BY Studio

Question: List the name of film studio that have the most number of films.
SQL: SELECT Studio FROM film GROUP BY Studio ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the name of teh studio that created the most films?
SQL: SELECT Studio FROM film GROUP BY Studio ORDER BY COUNT(*) DESC LIMIT 1

Question: List the names of studios that have at least two films.
SQL: SELECT Studio FROM film GROUP BY Studio HAVING COUNT(*)  >=  2

Question: What are the names of studios that have made two or more films?
SQL: SELECT Studio FROM film GROUP BY Studio HAVING COUNT(*)  >=  2

Question: List the title of films that do not have any market estimation.
SQL: SELECT Title FROM film WHERE Film_ID NOT IN (SELECT Film_ID FROM film_market_estimation)

Question: What are the titles of films that do not have a film market estimation?
SQL: SELECT Title FROM film WHERE Film_ID NOT IN (SELECT Film_ID FROM film_market_estimation)

Question: Show the studios that have produced films with director "Nicholas Meyer" and "Walter Hill".
SQL: SELECT Studio FROM film WHERE Director  =  "Nicholas Meyer" INTERSECT SELECT Studio FROM film WHERE Director  =  "Walter Hill"

Question: What are the names of studios that have produced films with both Nicholas Meyer and Walter Hill?
SQL: SELECT Studio FROM film WHERE Director  =  "Nicholas Meyer" INTERSECT SELECT Studio FROM film WHERE Director  =  "Walter Hill"

Question: Find the titles and studios of the films that are produced by some film studios that contained the word "Universal".
SQL: SELECT title ,  Studio FROM film WHERE Studio LIKE "%Universal%"

Question: What are the titles and studios of films that have been produced by a studio whose name contains "Universal"?
SQL: SELECT title ,  Studio FROM film WHERE Studio LIKE "%Universal%"

Question: Show the studios that have not produced films with director "Walter Hill".
SQL: SELECT Studio FROM film EXCEPT SELECT Studio FROM film WHERE Director  =  "Walter Hill"

Question: Which studios have never worked with the director Walter Hill?
SQL: SELECT Studio FROM film EXCEPT SELECT Studio FROM film WHERE Director  =  "Walter Hill"

Question: List the studios which average gross is above 4500000.
SQL: SELECT Studio FROM film GROUP BY Studio HAVING avg(Gross_in_dollar)  >=  4500000

Question: Which studios have an average gross of over 4500000?
SQL: SELECT Studio FROM film GROUP BY Studio HAVING avg(Gross_in_dollar)  >=  4500000

Question: What is the title of the film that has the highest high market estimation.
SQL: SELECT t1.title FROM film AS T1 JOIN film_market_estimation AS T2  ON T1.Film_ID  =  T2.Film_ID ORDER BY high_estimate DESC LIMIT 1

Question: Return the title of the film with the highest high estimate?
SQL: SELECT t1.title FROM film AS T1 JOIN film_market_estimation AS T2  ON T1.Film_ID  =  T2.Film_ID ORDER BY high_estimate DESC LIMIT 1

Question: What are the titles and directors of the films were never presented in China?
SQL: SELECT title ,  director FROM film WHERE film_id NOT IN (SELECT film_id FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.market_id  =  T2.Market_ID WHERE country  =  'China')

Question: Return the titles and directors of films that were never in the market of China.
SQL: SELECT title ,  director FROM film WHERE film_id NOT IN (SELECT film_id FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.market_id  =  T2.Market_ID WHERE country  =  'China')