Database: gas_company

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 companies are there?
SQL: SELECT count(*) FROM company

Question: What is the total number of companies?
SQL: SELECT count(*) FROM company

Question: List the company name and rank for all companies in the decreasing order of their sales.
SQL: SELECT company ,  rank FROM company ORDER BY Sales_billion DESC

Question: What is the name and rank of every company ordered by descending number of sales?
SQL: SELECT company ,  rank FROM company ORDER BY Sales_billion DESC

Question: Show the company name and the main industry for all companies whose headquarters are not from USA.
SQL: SELECT company ,  main_industry FROM company WHERE headquarters != 'USA'

Question: What are the companies and main industries of all companies that are not headquartered in the United States?
SQL: SELECT company ,  main_industry FROM company WHERE headquarters != 'USA'

Question: Show all company names and headquarters in the descending order of market value.
SQL: SELECT company ,  headquarters FROM company ORDER BY market_value DESC

Question: What are the names and headquarters of all companies ordered by descending market value?
SQL: SELECT company ,  headquarters FROM company ORDER BY market_value DESC

Question: Show minimum, maximum, and average market value for all companies.
SQL: SELECT min(market_value) ,  max(market_value) ,  avg(market_value) FROM company

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

Question: Show all main industry for all companies.
SQL: SELECT DISTINCT main_industry FROM company

Question: What are the different main industries for all companies?
SQL: SELECT DISTINCT main_industry FROM company

Question: List all headquarters and the number of companies in each headquarter.
SQL: SELECT headquarters ,  count(*) FROM company GROUP BY headquarters

Question: For each headquarter, what are the headquarter and how many companies are centered there?
SQL: SELECT headquarters ,  count(*) FROM company GROUP BY headquarters

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

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

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

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

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

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

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

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

Question: How many gas station are opened between 2000 and 2005?
SQL: SELECT count(*) FROM gas_station WHERE open_year BETWEEN 2000 AND 2005

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

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(*)

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(*)

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'

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'

Question: Show all headquarters without a company in banking industry.
SQL: SELECT headquarters FROM company EXCEPT SELECT headquarters FROM company WHERE main_industry  =  'Banking'

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'

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

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

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)

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)

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'

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'

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

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

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

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

Question: order all gas station locations by the opening year.
SQL: SELECT LOCATION FROM gas_station ORDER BY open_year

Question: What are the locations of all the gas stations ordered by opening year?
SQL: SELECT LOCATION FROM gas_station ORDER BY open_year

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

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

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

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