lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What is the minimum, maximum, and average market value for every company?

### SQL
SELECT min(market_value) ,  max(market_value) ,  avg(market_value) FROM company
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show all main industry for all companies.

### SQL
SELECT DISTINCT main_industry FROM company
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the different main industries for all companies?

### SQL
SELECT DISTINCT main_industry FROM company
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
List all headquarters and the number of companies in each headquarter.

### SQL
SELECT headquarters ,  count(*) FROM company GROUP BY headquarters
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
For each headquarter, what are the headquarter and how many companies are centered there?

### SQL
SELECT headquarters ,  count(*) FROM company GROUP BY headquarters
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show all main industry and total market value in each industry.

### SQL
SELECT main_industry ,  sum(market_value) FROM company GROUP BY main_industry
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the main indstries and total market value for each industry?

### SQL
SELECT main_industry ,  sum(market_value) FROM company GROUP BY main_industry
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
List the main industry with highest total market value and its number of companies.

### SQL
SELECT main_industry ,  count(*) FROM company GROUP BY main_industry ORDER BY sum(market_value) DESC LIMIT 1
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
For each main industry, what is the total number of companies for the industry with the highest total market value?

### SQL
SELECT main_industry ,  count(*) FROM company GROUP BY main_industry ORDER BY sum(market_value) DESC LIMIT 1
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show headquarters with at least two companies in the banking industry.

### SQL
SELECT headquarters FROM company WHERE main_industry  =  'Banking' GROUP BY headquarters HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the headquarters with at least two companies in the banking industry?

### SQL
SELECT headquarters FROM company WHERE main_industry  =  'Banking' GROUP BY headquarters HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show gas station id, location, and manager_name for all gas stations ordered by open year.

### SQL
SELECT station_id ,  LOCATION ,  manager_name FROM gas_station ORDER BY open_year
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the gas station ids, locations, and manager names for the gas stations ordered by opening year?

### SQL
SELECT station_id ,  LOCATION ,  manager_name FROM gas_station ORDER BY open_year
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
How many gas station are opened between 2000 and 2005?

### SQL
SELECT count(*) FROM gas_station WHERE open_year BETWEEN 2000 AND 2005
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What is the total number of gas stations that opened between 2000 and 2005?

### SQL
SELECT count(*) FROM gas_station WHERE open_year BETWEEN 2000 AND 2005
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show all locations and the number of gas stations in each location ordered by the count.

### SQL
SELECT LOCATION ,  count(*) FROM gas_station GROUP BY LOCATION ORDER BY count(*)
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
For each location, how many gas stations are there in order?

### SQL
SELECT LOCATION ,  count(*) FROM gas_station GROUP BY LOCATION ORDER BY count(*)
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show all headquarters with both a company in banking industry and a company in Oil and gas.

### SQL
SELECT headquarters FROM company WHERE main_industry  =  'Banking' INTERSECT SELECT headquarters FROM company WHERE main_industry  =  'Oil and gas'
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the headquarters that have both a company in the banking and 'oil and gas' industries?

### SQL
SELECT headquarters FROM company WHERE main_industry  =  'Banking' INTERSECT SELECT headquarters FROM company WHERE main_industry  =  'Oil and gas'
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show all headquarters without a company in banking industry.

### SQL
SELECT headquarters FROM company EXCEPT SELECT headquarters FROM company WHERE main_industry  =  'Banking'
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the headquarters without companies that are in the banking industry?

### SQL
SELECT headquarters FROM company EXCEPT SELECT headquarters FROM company WHERE main_industry  =  'Banking'
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show the company name with the number of gas station.

### SQL
SELECT T2.company ,  count(*) FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id GROUP BY T1.company_id
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
For each company id, what are the companies and how many gas stations does each one operate?

### SQL
SELECT T2.company ,  count(*) FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id GROUP BY T1.company_id
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show company name and main industry without a gas station.

### SQL
SELECT company ,  main_industry FROM company WHERE company_id NOT IN (SELECT company_id FROM station_company)
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the main industries of the companies without gas stations and what are the companies?

### SQL
SELECT company ,  main_industry FROM company WHERE company_id NOT IN (SELECT company_id FROM station_company)
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show the manager name for gas stations belonging to the ExxonMobil company.

### SQL
SELECT T3.manager_name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id WHERE T2.company  =  'ExxonMobil'
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the names of the managers for gas stations that are operated by the ExxonMobil company?

### SQL
SELECT T3.manager_name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id WHERE T2.company  =  'ExxonMobil'
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show all locations where a gas station for company with market value greater than 100 is located.

### SQL
SELECT T3.location FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id WHERE T2.market_value  >  100
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the locations that have gas stations owned by a company with a market value greater than 100?

### SQL
SELECT T3.location FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id WHERE T2.market_value  >  100
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show the manager name with most number of gas stations opened after 2000.

### SQL
SELECT manager_name FROM gas_station WHERE open_year  >  2000 GROUP BY manager_name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What is the name of the manager with the most gas stations that opened after 2000?

### SQL
SELECT manager_name FROM gas_station WHERE open_year  >  2000 GROUP BY manager_name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
order all gas station locations by the opening year.

### SQL
SELECT LOCATION FROM gas_station ORDER BY open_year
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the locations of all the gas stations ordered by opening year?

### SQL
SELECT LOCATION FROM gas_station ORDER BY open_year
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
find the rank, company names, market values of the companies in the banking industry order by their sales and profits in billion.

### SQL
SELECT rank ,  company ,  market_value FROM company WHERE main_industry  =  'Banking' ORDER BY sales_billion ,  profits_billion
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What is the rank, company, and market value of every comapny in the banking industry ordered by sales and profits?

### SQL
SELECT rank ,  company ,  market_value FROM company WHERE main_industry  =  'Banking' ORDER BY sales_billion ,  profits_billion
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
find the location and Representative name of the gas stations owned by the companies with top 3 Asset amounts.

### SQL
SELECT T3.location ,  T3.Representative_Name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id ORDER BY T2.Assets_billion DESC LIMIT 3
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the locations and representatives' names of the gas stations owned by the companies with the 3 largest amounts of assets?

### SQL
SELECT T3.location ,  T3.Representative_Name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id ORDER BY T2.Assets_billion DESC LIMIT 3
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
How many regions do we have?

### SQL
SELECT count(*) FROM region
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Count the number of regions.

### SQL
SELECT count(*) FROM region
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show all distinct region names ordered by their labels.

### SQL
SELECT DISTINCT region_name FROM region ORDER BY Label
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
What are the different region names, ordered by labels?

### SQL
SELECT DISTINCT region_name FROM region ORDER BY Label
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
How many parties do we have?

### SQL
SELECT count(DISTINCT party_name) FROM party
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Count the number of different parties.

### SQL
SELECT count(DISTINCT party_name) FROM party
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show the ministers and the time they took and left office, listed by the time they left office.

### SQL
SELECT minister ,  took_office ,  left_office FROM party ORDER BY left_office
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Who are the ministers, when did they take office, and when did they leave office, ordered by when they left office?

### SQL
SELECT minister ,  took_office ,  left_office FROM party ORDER BY left_office
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show the minister who took office after 1961 or before 1959.

### SQL
SELECT minister FROM party WHERE took_office  >  1961 OR took_office  <  1959
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Who are the ministers who took office after 1961 or before 1959?

### SQL
SELECT minister FROM party WHERE took_office  >  1961 OR took_office  <  1959
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show all ministers who do not belong to Progress Party.

### SQL
SELECT minister FROM party WHERE party_name != 'Progress Party'
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Which ministers are not a part of the Progress Party?

### SQL
SELECT minister FROM party WHERE party_name != 'Progress Party'
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Show all ministers and parties they belong to in descending order of the time they took office.

### SQL
SELECT minister ,  party_name FROM party ORDER BY took_office DESC
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Who are the ministers and what parties do they belong to, listed descending by the times they took office?

### SQL
SELECT minister ,  party_name FROM party ORDER BY took_office DESC
### Database Schema
CREATE TABLE "region" (
"Region_ID" int,
"Region_name" text,
"Date" text,
"Label" text,
"Format" text,
"Catalogue" text,
PRIMARY KEY ("Region_ID")
)

CREATE TABLE "party" (
"Party_ID" int,
"Minister" text,
"Took_office" text,
"Left_office" text,
"Region_ID" int,
"Party_name" text,
PRIMARY KEY ("Party_ID"),
FOREIGN KEY (`Region_ID`) REFERENCES `region`(`Region_ID`)
)

CREATE TABLE "member" (
"Member_ID" int,
"Member_Name" text,
"Party_ID" text,
"In_office" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`)
)

CREATE TABLE "party_events" (
"Event_ID" int,
"Event_Name" text,
"Party_ID" int,
"Member_in_charge_ID" int,
PRIMARY KEY ("Event_ID"),
FOREIGN KEY (`Party_ID`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`Member_in_charge_ID`) REFERENCES `member`(`Member_ID`)
)

### Question
Return the minister who left office at the latest time.

### SQL
SELECT minister FROM party ORDER BY left_office DESC LIMIT 1