Database: cinema

CREATE TABLE "film" (
"Film_ID" int,
"Rank_in_series" int,
"Number_in_season" int,
"Title" text,
"Directed_by" text,
"Original_air_date" text,
"Production_code" text,
PRIMARY KEY ("Film_ID")
);
CREATE TABLE "cinema" (
"Cinema_ID" int,
"Name" text,
"Openning_year" int,
"Capacity" int,
"Location" text,
PRIMARY KEY ("Cinema_ID"));
CREATE TABLE "schedule" (
"Cinema_ID" int,
"Film_ID" int,
"Date" text,
"Show_times_per_day" int,
"Price" float,
PRIMARY KEY ("Cinema_ID","Film_ID"),
FOREIGN KEY (`Film_ID`) REFERENCES `film`(`Film_ID`),
FOREIGN KEY (`Cinema_ID`) REFERENCES `cinema`(`Cinema_ID`)
);

Question: Show all the locations where no cinema has capacity over 800.
SQL: SELECT LOCATION FROM cinema EXCEPT SELECT LOCATION FROM cinema WHERE capacity  >  800

Question: Show all the locations where some cinemas were opened in both year 2010 and year 2011.
SQL: SELECT LOCATION FROM cinema WHERE openning_year  =  2010 INTERSECT SELECT LOCATION FROM cinema WHERE openning_year  =  2011

Question: How many cinema do we have?
SQL: SELECT count(*) FROM cinema

Question: Count the number of cinemas.
SQL: SELECT count(*) FROM cinema

Question: Show name, opening year, and capacity for each cinema.
SQL: SELECT name ,  openning_year ,  capacity FROM cinema

Question: Show the cinema name and location for cinemas with capacity above average.
SQL: SELECT name ,  LOCATION FROM cinema WHERE capacity  >  (SELECT avg(capacity) FROM cinema)

Question: What are all the locations with a cinema?
SQL: SELECT DISTINCT LOCATION FROM cinema

Question: Find the distinct locations that has a cinema.
SQL: SELECT DISTINCT LOCATION FROM cinema

Question: Show all the cinema names and opening years in descending order of opening year.
SQL: SELECT name ,  openning_year FROM cinema ORDER BY openning_year DESC

Question: What are the name and location of the cinema with the largest capacity?
SQL: SELECT name ,  LOCATION FROM cinema ORDER BY capacity DESC LIMIT 1

Question: Show the average, minimum, and maximum capacity for all the cinemas opened in year 2011 or later.
SQL: SELECT avg(capacity) ,  min(capacity) ,  max(capacity) FROM cinema WHERE openning_year  >=  2011

Question: Show each location and the number of cinemas there.
SQL: SELECT LOCATION ,  count(*) FROM cinema GROUP BY LOCATION

Question: What is the location with the most cinemas opened in year 2010 or later?
SQL: SELECT LOCATION FROM cinema WHERE openning_year  >=  2010 GROUP BY LOCATION ORDER BY count(*) DESC LIMIT 1

Question: Show all the locations with at least two cinemas with capacity above 300.
SQL: SELECT LOCATION FROM cinema WHERE capacity  >  300 GROUP BY LOCATION HAVING count(*)  >=  2

Question: Which locations have 2 or more cinemas with capacity over 300?
SQL: SELECT LOCATION FROM cinema WHERE capacity  >  300 GROUP BY LOCATION HAVING count(*)  >=  2

Question: Show the title and director for all films.
SQL: SELECT title ,  directed_by FROM film

Question: What are the title and director of each film?
SQL: SELECT title ,  directed_by FROM film

Question: Show all directors.
SQL: SELECT DISTINCT directed_by FROM film

Question: Who are all the directors?
SQL: SELECT DISTINCT directed_by FROM film

Question: List all directors along with the number of films directed by each director.
SQL: SELECT directed_by ,  count(*) FROM film GROUP BY directed_by

Question: What is total number of show times per dat for each cinema?
SQL: SELECT T2.name ,  sum(T1.show_times_per_day) FROM schedule AS T1 JOIN cinema AS T2 ON T1.cinema_id  =  T2.cinema_id GROUP BY T1.cinema_id

Question: What are the title and maximum price of each film?
SQL: SELECT T2.title ,  max(T1.price) FROM schedule AS T1 JOIN film AS T2 ON T1.film_id  =  T2.film_id GROUP BY T1.film_id

Question: Give me the title and highest price for each film.
SQL: SELECT T2.title ,  max(T1.price) FROM schedule AS T1 JOIN film AS T2 ON T1.film_id  =  T2.film_id GROUP BY T1.film_id

Question: Show cinema name, film title, date, and price for each record in schedule.
SQL: SELECT T3.name ,  T2.title ,  T1.date ,  T1.price FROM schedule AS T1 JOIN film AS T2 ON T1.film_id  =  T2.film_id JOIN cinema AS T3 ON T1.cinema_id  =  T3.cinema_id

Question: What are the title and director of the films without any schedule?
SQL: SELECT title ,  directed_by FROM film WHERE film_id NOT IN (SELECT film_id FROM schedule)

Question: Show director with the largest number of show times in total.
SQL: SELECT T2.directed_by FROM schedule AS T1 JOIN film AS T2 ON T1.film_id  =  T2.film_id GROUP BY T2.directed_by ORDER BY sum(T1.show_times_per_day) DESC LIMIT 1

Question: Find the locations that have more than one movie theater with capacity above 300.
SQL: SELECT LOCATION FROM cinema WHERE capacity  >  300 GROUP BY LOCATION HAVING count(*)  >  1

Question: In which locations are there more than one movie theater with capacity above 300?
SQL: SELECT LOCATION FROM cinema WHERE capacity  >  300 GROUP BY LOCATION HAVING count(*)  >  1

Question: How many films have the word 'Dummy' in their titles?
SQL: SELECT count(*) FROM film WHERE title LIKE "%Dummy%"

Question: Count the number of films whose title contains the word 'Dummy'.
SQL: SELECT count(*) FROM film WHERE title LIKE "%Dummy%"