
CREATE TABLE cinema (
	"Cinema_ID" INTEGER, 
	"Name" TEXT, 
	"Openning_year" INTEGER, 
	"Capacity" INTEGER, 
	"Location" TEXT, 
	PRIMARY KEY ("Cinema_ID")
)


CREATE TABLE film (
	"Film_ID" INTEGER, 
	"Rank_in_series" INTEGER, 
	"Number_in_season" INTEGER, 
	"Title" TEXT, 
	"Directed_by" TEXT, 
	"Original_air_date" TEXT, 
	"Production_code" TEXT, 
	PRIMARY KEY ("Film_ID")
)


CREATE TABLE schedule (
	"Cinema_ID" INTEGER, 
	"Film_ID" INTEGER, 
	"Date" TEXT, 
	"Show_times_per_day" INTEGER, 
	"Price" FLOAT, 
	PRIMARY KEY ("Cinema_ID", "Film_ID"), 
	FOREIGN KEY("Cinema_ID") REFERENCES cinema ("Cinema_ID"), 
	FOREIGN KEY("Film_ID") REFERENCES film ("Film_ID")
)

CREATE TABLE cinema (
	"Cinema_ID" INTEGER, 
	"Name" TEXT, 
	"Openning_year" INTEGER, 
	"Capacity" INTEGER, 
	"Location" TEXT, 
	PRIMARY KEY ("Cinema_ID")
)

/*
2 rows from cinema table:
Cinema_ID	Name	Openning_year	Capacity	Location
1	Codling	2010	1100	County Wicklow
2	Carrowleagh	2012	368	County Cork
*/


CREATE TABLE film (
	"Film_ID" INTEGER, 
	"Rank_in_series" INTEGER, 
	"Number_in_season" INTEGER, 
	"Title" TEXT, 
	"Directed_by" TEXT, 
	"Original_air_date" TEXT, 
	"Production_code" TEXT, 
	PRIMARY KEY ("Film_ID")
)

/*
2 rows from film table:
Film_ID	Rank_in_series	Number_in_season	Title	Directed_by	Original_air_date	Production_code
1	26	1	The Case of the Mystery Weekend	Bill Schreiner	September 21–25, 1992	50021–50025
2	27	2	The Case of the Smart Dummy	Bill Schreiner	September 28–October 2, 1992	50231–50235
*/


CREATE TABLE schedule (
	"Cinema_ID" INTEGER, 
	"Film_ID" INTEGER, 
	"Date" TEXT, 
	"Show_times_per_day" INTEGER, 
	"Price" FLOAT, 
	PRIMARY KEY ("Cinema_ID", "Film_ID"), 
	FOREIGN KEY("Cinema_ID") REFERENCES cinema ("Cinema_ID"), 
	FOREIGN KEY("Film_ID") REFERENCES film ("Film_ID")
)

/*
2 rows from schedule table:
Cinema_ID	Film_ID	Date	Show_times_per_day	Price
1	1	21 May	5	12.99
1	2	21 May	3	12.99
*/
Show all directors.
SELECT DISTINCT directed_by FROM film
[{'Directed_by': 'Bill Schreiner'}, {'Directed_by': 'Jesus Salvador Treviño'}]