lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
What are the distinct cross reference source system codes which are related to the master customer details 'Gottlieb, Becker and Wyman'?

### SQL
SELECT DISTINCT T2.source_system_code FROM customer_master_index AS T1 JOIN cmi_cross_references AS T2 ON T1.master_customer_id  =  T2.master_customer_id WHERE T1.cmi_details  =  'Gottlieb ,  Becker and Wyman'
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
Which cmi cross reference id is not related to any parking taxes?

### SQL
SELECT cmi_cross_ref_id FROM cmi_cross_references EXCEPT SELECT cmi_cross_ref_id FROM parking_fines
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
Which distinct source system code includes the substring 'en'?

### SQL
SELECT DISTINCT source_system_code FROM cmi_cross_references WHERE source_system_code LIKE '%en%'
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
How many parties are there?

### SQL
SELECT count(*) FROM party
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Count the number of parties.

### SQL
SELECT count(*) FROM party
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
List the themes of parties in ascending order of number of hosts.

### SQL
SELECT Party_Theme FROM party ORDER BY Number_of_hosts ASC
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
What are the themes of parties ordered by the number of hosts in ascending manner?

### SQL
SELECT Party_Theme FROM party ORDER BY Number_of_hosts ASC
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
What are the themes and locations of parties?

### SQL
SELECT Party_Theme ,  LOCATION FROM party
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Give me the theme and location of each party.

### SQL
SELECT Party_Theme ,  LOCATION FROM party
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show the first year and last year of parties with theme "Spring" or "Teqnology".

### SQL
SELECT First_year ,  Last_year FROM party WHERE Party_Theme  =  "Spring" OR Party_Theme  =  "Teqnology"
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
What are the first year and last year of the parties whose theme is "Spring" or "Teqnology"?

### SQL
SELECT First_year ,  Last_year FROM party WHERE Party_Theme  =  "Spring" OR Party_Theme  =  "Teqnology"
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
What is the average number of hosts for parties?

### SQL
SELECT avg(Number_of_hosts) FROM party
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Compute the average number of hosts for parties.

### SQL
SELECT avg(Number_of_hosts) FROM party
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
What is the location of the party with the most hosts?

### SQL
SELECT LOCATION FROM party ORDER BY Number_of_hosts DESC LIMIT 1
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Which party had the most hosts? Give me the party location.

### SQL
SELECT LOCATION FROM party ORDER BY Number_of_hosts DESC LIMIT 1
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show different nationalities along with the number of hosts of each nationality.

### SQL
SELECT Nationality ,  COUNT(*) FROM HOST GROUP BY Nationality
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
How many hosts does each nationality have? List the nationality and the count.

### SQL
SELECT Nationality ,  COUNT(*) FROM HOST GROUP BY Nationality
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show the most common nationality of hosts.

### SQL
SELECT Nationality FROM HOST GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Which nationality has the most hosts?

### SQL
SELECT Nationality FROM HOST GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show the nations that have both hosts older than 45 and hosts younger than 35.

### SQL
SELECT Nationality FROM HOST WHERE Age  >  45 INTERSECT SELECT Nationality FROM HOST WHERE Age  <  35
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Which nations have both hosts of age above 45 and hosts of age below 35?

### SQL
SELECT Nationality FROM HOST WHERE Age  >  45 INTERSECT SELECT Nationality FROM HOST WHERE Age  <  35
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show the themes of parties and the names of the party hosts.

### SQL
SELECT T3.Party_Theme ,  T2.Name FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID  =  T2.Host_ID JOIN party AS T3 ON T1.Party_ID  =  T3.Party_ID
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
For each party, return its theme and the name of its host.

### SQL
SELECT T3.Party_Theme ,  T2.Name FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID  =  T2.Host_ID JOIN party AS T3 ON T1.Party_ID  =  T3.Party_ID
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show the locations of parties and the names of the party hosts in ascending order of the age of the host.

### SQL
SELECT T3.Location ,  T2.Name FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID  =  T2.Host_ID JOIN party AS T3 ON T1.Party_ID  =  T3.Party_ID ORDER BY T2.Age
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
For each party, find its location and the name of its host. Sort the result in ascending order of the age of the host.

### SQL
SELECT T3.Location ,  T2.Name FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID  =  T2.Host_ID JOIN party AS T3 ON T1.Party_ID  =  T3.Party_ID ORDER BY T2.Age
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show the locations of parties with hosts older than 50.

### SQL
SELECT T3.Location FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID  =  T2.Host_ID JOIN party AS T3 ON T1.Party_ID  =  T3.Party_ID WHERE T2.Age  >  50
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Which parties have hosts of age above 50? Give me the party locations.

### SQL
SELECT T3.Location FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID  =  T2.Host_ID JOIN party AS T3 ON T1.Party_ID  =  T3.Party_ID WHERE T2.Age  >  50
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show the host names for parties with number of hosts greater than 20.

### SQL
SELECT T2.Name FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID  =  T2.Host_ID JOIN party AS T3 ON T1.Party_ID  =  T3.Party_ID WHERE T3.Number_of_hosts  >  20
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Which parties have more than 20 hosts? Give me the host names for these parties.

### SQL
SELECT T2.Name FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID  =  T2.Host_ID JOIN party AS T3 ON T1.Party_ID  =  T3.Party_ID WHERE T3.Number_of_hosts  >  20
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
Show the name and the nationality of the oldest host.

### SQL
SELECT Name ,  Nationality FROM HOST ORDER BY Age DESC LIMIT 1
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
What are the name and the nationality of the host of the highest age?

### SQL
SELECT Name ,  Nationality FROM HOST ORDER BY Age DESC LIMIT 1
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
List the names of hosts who did not serve as a host of any party in our record.

### SQL
SELECT Name FROM HOST WHERE Host_ID NOT IN (SELECT Host_ID FROM party_host)
### Database Schema
CREATE TABLE "party" (
"Party_ID" int,
"Party_Theme" text,
"Location" text,
"First_year" text,
"Last_year" text,
"Number_of_hosts" int,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "host" (
"Host_ID" int,
"Name" text,
"Nationality" text,
"Age" text,
PRIMARY KEY ("Host_ID")
)

CREATE TABLE "party_host" (
"Party_ID" int,
"Host_ID" int,
"Is_Main_in_Charge" bool,
PRIMARY KEY ("Party_ID","Host_ID"),
FOREIGN KEY ("Host_ID") REFERENCES `host`("Host_ID"),
FOREIGN KEY ("Party_ID") REFERENCES `party`("Party_ID")
)

### Question
What are the names of hosts who did not host any party in our record?

### SQL
SELECT Name FROM HOST WHERE Host_ID NOT IN (SELECT Host_ID FROM party_host)
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
How many regions do we have?

### SQL
SELECT count(*) FROM region
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Count the number of regions.

### SQL
SELECT count(*) FROM region
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Show all region code and region name sorted by the codes.

### SQL
SELECT region_code ,  region_name FROM region ORDER BY region_code
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What are the codes and names for all regions, sorted by codes?

### SQL
SELECT region_code ,  region_name FROM region ORDER BY region_code
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
List all region names in alphabetical order.

### SQL
SELECT region_name FROM region ORDER BY region_name
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What are the names of the regions in alphabetical order?

### SQL
SELECT region_name FROM region ORDER BY region_name
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Show names for all regions except for Denmark.

### SQL
SELECT region_name FROM region WHERE region_name != 'Denmark'
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Return the names of all regions other than Denmark.

### SQL
SELECT region_name FROM region WHERE region_name != 'Denmark'
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
How many storms had death records?

### SQL
SELECT count(*) FROM storm WHERE Number_Deaths  >  0
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Count the number of storms in which at least 1 person died.

### SQL
SELECT count(*) FROM storm WHERE Number_Deaths  >  0
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
List name, dates active, and number of deaths for all storms with at least 1 death.

### SQL
SELECT name ,  dates_active ,  number_deaths FROM storm WHERE number_deaths  >=  1
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What are the names, dates active, and number of deaths for storms that had 1 or more death?

### SQL
SELECT name ,  dates_active ,  number_deaths FROM storm WHERE number_deaths  >=  1
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Show the average and maximum damage for all storms with max speed higher than 1000.

### SQL
SELECT avg(damage_millions_USD) ,  max(damage_millions_USD) FROM storm WHERE max_speed  >  1000
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What is the average and maximum damage in millions for storms that had a max speed over 1000?

### SQL
SELECT avg(damage_millions_USD) ,  max(damage_millions_USD) FROM storm WHERE max_speed  >  1000
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What is the total number of deaths and damage for all storms with a max speed greater than the average?

### SQL
SELECT sum(number_deaths) ,  sum(damage_millions_USD) FROM storm WHERE max_speed  >  (SELECT avg(max_speed) FROM storm)
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Return the total number of deaths and total damange in millions for storms that had a max speed greater than the average.

### SQL
SELECT sum(number_deaths) ,  sum(damage_millions_USD) FROM storm WHERE max_speed  >  (SELECT avg(max_speed) FROM storm)
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
List name and damage for all storms in a descending order of max speed.

### SQL
SELECT name ,  damage_millions_USD FROM storm ORDER BY max_speed DESC
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What are the names and damage in millions for storms, ordered by their max speeds descending?

### SQL
SELECT name ,  damage_millions_USD FROM storm ORDER BY max_speed DESC
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
How many regions are affected?

### SQL
SELECT count(DISTINCT region_id) FROM affected_region
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Count the number of different affected regions.

### SQL
SELECT count(DISTINCT region_id) FROM affected_region
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Show the name for regions not affected.

### SQL
SELECT region_name FROM region WHERE region_id NOT IN (SELECT region_id FROM affected_region)
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What are the names of regions that were not affected?

### SQL
SELECT region_name FROM region WHERE region_id NOT IN (SELECT region_id FROM affected_region)
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Show the name for regions and the number of storms for each region.

### SQL
SELECT T1.region_name ,  count(*) FROM region AS T1 JOIN affected_region AS T2 ON T1.region_id = T2.region_id GROUP BY T1.region_id
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
How many storms occured in each region?

### SQL
SELECT T1.region_name ,  count(*) FROM region AS T1 JOIN affected_region AS T2 ON T1.region_id = T2.region_id GROUP BY T1.region_id
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
List the name for storms and the number of affected regions for each storm.

### SQL
SELECT T1.name ,  count(*) FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id  =  T2.storm_id GROUP BY T1.storm_id
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
How many regions were affected by each storm?

### SQL
SELECT T1.name ,  count(*) FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id  =  T2.storm_id GROUP BY T1.storm_id
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What is the storm name and max speed which affected the greatest number of regions?

### SQL
SELECT T1.name ,  T1.max_speed FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id  =  T2.storm_id GROUP BY T1.storm_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Return the name and max speed of the storm that affected the most regions.

### SQL
SELECT T1.name ,  T1.max_speed FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id  =  T2.storm_id GROUP BY T1.storm_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
Show the name of storms which don't have affected region in record.

### SQL
SELECT name FROM storm WHERE storm_id NOT IN (SELECT storm_id FROM affected_region)
### Database Schema
CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
)

CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
)

CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
)

### Question
What are the names of storms that did not affect any regions?

### SQL
SELECT name FROM storm WHERE storm_id NOT IN (SELECT storm_id FROM affected_region)