lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
)

CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
)

CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
)

### Question
For each building, show the name of the building and the number of institutions in it.

### SQL
SELECT T1.name ,  count(*) FROM building AS T1 JOIN institution AS T2 ON T1.building_id  =  T2.building_id GROUP BY T1.building_id
### Database Schema
CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
)

CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
)

CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
)

### Question
Show the names and heights of buildings with at least two institutions founded after 1880.

### SQL
SELECT T1.name ,  T1.height_feet FROM building AS T1 JOIN institution AS T2 ON T1.building_id  =  T2.building_id WHERE T2.founded  >  1880 GROUP BY T1.building_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
)

CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
)

CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
)

### Question
Show all the distinct institution types.

### SQL
SELECT DISTINCT TYPE FROM institution
### Database Schema
CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
)

CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
)

CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
)

### Question
Show institution names along with the number of proteins for each institution.

### SQL
SELECT T1.institution ,  count(*) FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id  =  T2.institution_id GROUP BY T1.institution_id
### Database Schema
CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
)

CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
)

CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
)

### Question
How many proteins are associated with an institution founded after 1880 or an institution with type "Private"?

### SQL
SELECT count(*) FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id  =  T2.institution_id WHERE T1.founded  >  1880 OR T1.type  =  'Private'
### Database Schema
CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
)

CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
)

CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
)

### Question
Show the protein name and the institution name.

### SQL
SELECT T2.protein_name ,  T1.institution FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id  =  T2.institution_id
### Database Schema
CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
)

CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
)

CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
)

### Question
How many proteins are associated with an institution in a building with at least 20 floors?

### SQL
SELECT count(*) FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id  =  T2.institution_id JOIN building AS T3 ON T3.building_id  =  T1.building_id WHERE T3.floors  >=  20
### Database Schema
CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
)

CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
)

CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
)

### Question
How many institutions do not have an associated protein in our record?

### SQL
SELECT count(*) FROM institution WHERE institution_id NOT IN (SELECT institution_id FROM protein)
### Database Schema
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
### Database Schema
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 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
### Database Schema
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
How many cinema do we have?

### SQL
SELECT count(*) FROM cinema
### Database Schema
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
Count the number of cinemas.

### SQL
SELECT count(*) FROM cinema
### Database Schema
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 name, opening year, and capacity for each cinema.

### SQL
SELECT name ,  openning_year ,  capacity FROM cinema
### Database Schema
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 the cinema name and location for cinemas with capacity above average.

### SQL
SELECT name ,  LOCATION FROM cinema WHERE capacity  >  (SELECT avg(capacity) FROM cinema)
### Database Schema
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
What are all the locations with a cinema?

### SQL
SELECT DISTINCT LOCATION FROM cinema
### Database Schema
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
Find the distinct locations that has a cinema.

### SQL
SELECT DISTINCT LOCATION FROM cinema
### Database Schema
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 cinema names and opening years in descending order of opening year.

### SQL
SELECT name ,  openning_year FROM cinema ORDER BY openning_year DESC
### Database Schema
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
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
### Database Schema
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 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
### Database Schema
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 each location and the number of cinemas there.

### SQL
SELECT LOCATION ,  count(*) FROM cinema GROUP BY LOCATION
### Database Schema
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
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
### Database Schema
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 with at least two cinemas with capacity above 300.

### SQL
SELECT LOCATION FROM cinema WHERE capacity  >  300 GROUP BY LOCATION HAVING count(*)  >=  2
### Database Schema
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
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
### Database Schema
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 the title and director for all films.

### SQL
SELECT title ,  directed_by FROM film
### Database Schema
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
What are the title and director of each film?

### SQL
SELECT title ,  directed_by FROM film
### Database Schema
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 directors.

### SQL
SELECT DISTINCT directed_by FROM film
### Database Schema
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
Who are all the directors?

### SQL
SELECT DISTINCT directed_by FROM film
### Database Schema
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
List all directors along with the number of films directed by each director.

### SQL
SELECT directed_by ,  count(*) FROM film GROUP BY directed_by
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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 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
### Database Schema
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
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)
### Database Schema
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 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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
How many films have the word 'Dummy' in their titles?

### SQL
SELECT count(*) FROM film WHERE title LIKE "%Dummy%"
### Database Schema
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
Count the number of films whose title contains the word 'Dummy'.

### SQL
SELECT count(*) FROM film WHERE title LIKE "%Dummy%"
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
Are the customers holding coupons with amount 500 bad or good?

### SQL
SELECT T1.good_or_bad_customer FROM customers AS T1 JOIN discount_coupons AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.coupon_amount  =  500
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
How many bookings did each customer make? List the customer id, first name, and the count.

### SQL
SELECT T1.customer_id ,  T1.first_name ,  count(*) FROM Customers AS T1 JOIN bookings AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What is the maximum total amount paid by a customer? List the customer id and amount.

### SQL
SELECT customer_id ,  sum(amount_paid) FROM Payments GROUP BY customer_id ORDER BY sum(amount_paid) DESC LIMIT 1
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the id and the amount of refund of the booking that incurred the most times of payments?

### SQL
SELECT T1.booking_id ,  T1.amount_of_refund FROM Bookings AS T1 JOIN Payments AS T2 ON T1.booking_id  =  T2.booking_id GROUP BY T1.booking_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What is the id of the product that is booked for 3 times?

### SQL
SELECT product_id FROM products_booked GROUP BY product_id HAVING count(*)  =  3
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What is the product description of the product booked with an amount of 102.76?

### SQL
SELECT T2.product_description FROM products_booked AS T1 JOIN products_for_hire AS T2 ON T1.product_id  =  T2.product_id WHERE T1.booked_amount  =  102.76