CREATE TABLE company (
    Company_ID NUMBER PRIMARY KEY,
    Rank NUMBER,
    Company TEXT,
    Headquarters TEXT,
    Main_Industry TEXT,
    Sales_billion NUMBER,
    Profits_billion NUMBER,
    Assets_billion NUMBER,
    Market_Value NUMBER
);
CREATE TABLE gas_station (
    Station_ID NUMBER PRIMARY KEY,
    Open_Year NUMBER,
    Location TEXT,
    Manager_Name TEXT,
    Vice_Manager_Name TEXT,
    Representative_Name TEXT
);
CREATE TABLE station_company (
    Station_ID NUMBER PRIMARY KEY,
    Company_ID NUMBER,
    Rank_of_the_Year NUMBER,
    FOREIGN KEY ("Company_ID") REFERENCES company("Company_ID"),
    FOREIGN KEY ("Station_ID") REFERENCES gas_station("Station_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