sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show different carriers of phones together with the number of phones with each carrier.
SQL: SELECT Carrier ,  COUNT(*) FROM phone GROUP BY Carrier
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the most frequently used carrier of the phones.
SQL: SELECT Carrier FROM phone GROUP BY Carrier ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the carriers that have both phones with memory smaller than 32 and phones with memory bigger than 64.
SQL: SELECT Carrier FROM phone WHERE Memory_in_G  <  32 INTERSECT SELECT Carrier FROM phone WHERE Memory_in_G  >  64
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the names of phones and the districts of markets they are on.
SQL: SELECT T3.Name ,  T2.District FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the names of phones and the districts of markets they are on, in ascending order of the ranking of the market.
SQL: SELECT T3.Name ,  T2.District FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID ORDER BY T2.Ranking
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the names of phones that are on market with number of shops greater than 50.
SQL: SELECT T3.Name FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID WHERE T2.Num_of_shops  >  50
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: For each phone, show its names and total number of stocks.
SQL: SELECT T2.Name ,  sum(T1.Num_of_stock) FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID  =  T2.Phone_ID GROUP BY T2.Name
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the names of phones that have total number of stocks bigger than 2000, in descending order of the total number of stocks.
SQL: SELECT T2.Name FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID  =  T2.Phone_ID GROUP BY T2.Name HAVING sum(T1.Num_of_stock)  >=  2000 ORDER BY sum(T1.Num_of_stock) DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: phone 
columns : [['name', 'text'], ['phone id', 'number'], ['memory in g', 'number'], ['carrier', 'text'], ['price', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['district', 'text'], ['num of employees', 'number'], ['num of shops', 'number'], ['ranking', 'number']]

 -- Table: phone market 
columns : [['market id', 'number'], ['phone id', 'text'], ['num of stock', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List the names of phones that are not on any market.
SQL: SELECT Name FROM phone WHERE Phone_id NOT IN (SELECT Phone_ID FROM phone_market)
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many gas companies are there?
SQL: SELECT count(*) FROM company
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the total number of companies?
SQL: SELECT count(*) FROM company
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show minimum, maximum, and average market value for all companies.
SQL: SELECT min(market_value) ,  max(market_value) ,  avg(market_value) FROM company
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show all main industry for all companies.
SQL: SELECT DISTINCT main_industry FROM company
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the different main industries for all companies?
SQL: SELECT DISTINCT main_industry FROM company
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List all headquarters and the number of companies in each headquarter.
SQL: SELECT headquarters ,  count(*) FROM company GROUP BY headquarters
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: For each headquarter, what are the headquarter and how many companies are centered there?
SQL: SELECT headquarters ,  count(*) FROM company GROUP BY headquarters
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many gas station are opened between 2000 and 2005?
SQL: SELECT count(*) FROM gas_station WHERE open_year BETWEEN 2000 AND 2005
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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(*)
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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(*)
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show all headquarters without a company in banking industry.
SQL: SELECT headquarters FROM company EXCEPT SELECT headquarters FROM company WHERE main_industry  =  'Banking'
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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'
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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)
-- Language PostgreSQL
        -- Tables: 
             -- Table: company 
columns : [['company id', 'number'], ['rank', 'number'], ['company', 'text'], ['headquarters', 'text'], ['main industry', 'text'], ['sales billion', 'number'], ['profits billion', 'number'], ['assets billion', 'number'], ['market value', 'number']]

 -- Table: gas station 
columns : [['station id', 'number'], ['open year', 'number'], ['location', 'text'], ['manager name', 'text'], ['vice manager name', 'text'], ['representative name', 'text']]

 -- Table: station company 
columns : [['station id', 'number'], ['company id', 'number'], ['rank of the year', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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)