
CREATE TABLE film (
	"Film_ID" INTEGER, 
	"Title" TEXT, 
	"Studio" TEXT, 
	"Director" TEXT, 
	"Gross_in_dollar" INTEGER, 
	PRIMARY KEY ("Film_ID")
)


CREATE TABLE film_market_estimation (
	"Estimation_ID" INTEGER, 
	"Low_Estimate" REAL, 
	"High_Estimate" REAL, 
	"Film_ID" INTEGER, 
	"Type" TEXT, 
	"Market_ID" INTEGER, 
	"Year" INTEGER, 
	PRIMARY KEY ("Estimation_ID"), 
	FOREIGN KEY("Film_ID") REFERENCES film ("Film_ID"), 
	FOREIGN KEY("Market_ID") REFERENCES market ("Market_ID")
)


CREATE TABLE market (
	"Market_ID" INTEGER, 
	"Country" TEXT, 
	"Number_cities" INTEGER, 
	PRIMARY KEY ("Market_ID")
)

CREATE TABLE film (
	"Film_ID" INTEGER, 
	"Title" TEXT, 
	"Studio" TEXT, 
	"Director" TEXT, 
	"Gross_in_dollar" INTEGER, 
	PRIMARY KEY ("Film_ID")
)

/*
2 rows from film table:
Film_ID	Title	Studio	Director	Gross_in_dollar
1	ET the Extra-Terrestrial	Universal	Steven Spielberg	435110554
2	Tootsie	Columbia	Sydney Pollack	177200000
*/


CREATE TABLE film_market_estimation (
	"Estimation_ID" INTEGER, 
	"Low_Estimate" REAL, 
	"High_Estimate" REAL, 
	"Film_ID" INTEGER, 
	"Type" TEXT, 
	"Market_ID" INTEGER, 
	"Year" INTEGER, 
	PRIMARY KEY ("Estimation_ID"), 
	FOREIGN KEY("Film_ID") REFERENCES film ("Film_ID"), 
	FOREIGN KEY("Market_ID") REFERENCES market ("Market_ID")
)

/*
2 rows from film_market_estimation table:
Estimation_ID	Low_Estimate	High_Estimate	Film_ID	Type	Market_ID	Year
1	80000.0	80400.0	1	Mass suicide murder	1	1945
2	8000.0	8000.0	2	Mass suicide	2	1944
*/


CREATE TABLE market (
	"Market_ID" INTEGER, 
	"Country" TEXT, 
	"Number_cities" INTEGER, 
	PRIMARY KEY ("Market_ID")
)

/*
2 rows from market table:
Market_ID	Country	Number_cities
1	Japan	209
2	China	540
*/
Return the titles and directors of films that were never in the market of China.
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')
[{'Title': 'An Officer and a Gentleman', 'Director': 'Taylor Hackford'}, {'Title': "Porky's", 'Director': 'Bob Clark'}, {'Title': 'Star Trek II: The Wrath of Khan', 'Director': 'Nicholas Meyer'}, {'Title': '48 Hrs', 'Director': 'Walter Hill'}, {'Title': 'Poltergeist', 'Director': 'Tobe Hooper'}, {'Title': 'The Best Little Whorehouse in Texas', 'Director': 'Colin Higgins'}, {'Title': 'Annie', 'Director': 'John Huston'}, {'Title': 'The Verdict', 'Director': 'Sidney Lumet'}, {'Title': 'Gandhi', 'Director': 'Richard Attenborough'}, {'Title': 'First Blood', 'Director': 'Ted Kotcheff'}]