sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 origin and destination for all flights whose price is higher than 300?
SQL: SELECT origin ,  destination FROM Flight WHERE price  >  300
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 flight number and distance of the flight with maximum price.
SQL: SELECT flno ,  distance FROM Flight ORDER BY price DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 flight number and its distance for the one with the maximum price?
SQL: SELECT flno ,  distance FROM Flight ORDER BY price DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 flight number of flights with three lowest distances.
SQL: SELECT flno FROM Flight ORDER BY distance ASC LIMIT 3
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 numbers of the shortest flights?
SQL: SELECT flno FROM Flight ORDER BY distance ASC LIMIT 3
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 average distance and average price for flights from Los Angeles.
SQL: SELECT avg(distance) ,  avg(price) FROM Flight WHERE origin  =  "Los Angeles"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 average distance and price for all flights from LA?
SQL: SELECT avg(distance) ,  avg(price) FROM Flight WHERE origin  =  "Los Angeles"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 origins and the number of flights from each origin.
SQL: SELECT origin ,  count(*) FROM Flight GROUP BY origin
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 origin, how many flights came from there?
SQL: SELECT origin ,  count(*) FROM Flight GROUP BY origin
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 destinations and the number of flights to each destination.
SQL: SELECT destination ,  count(*) FROM Flight GROUP BY destination
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 destinations and number of flights to each one?
SQL: SELECT destination ,  count(*) FROM Flight GROUP BY destination
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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: Which origin has most number of flights?
SQL: SELECT origin FROM Flight GROUP BY origin ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 place has the most flights coming from there?
SQL: SELECT origin FROM Flight GROUP BY origin ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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: Which destination has least number of flights?
SQL: SELECT destination FROM Flight GROUP BY destination ORDER BY count(*) LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 destination has the fewest number of flights?
SQL: SELECT destination FROM Flight GROUP BY destination ORDER BY count(*) LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 aircraft name for the flight with number 99
SQL: SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid WHERE T1.flno  =  99
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 of the aircraft that was on flight number 99?
SQL: SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid WHERE T1.flno  =  99
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 flight numbers with aircraft Airbus A340-300.
SQL: SELECT T1.flno FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid WHERE T2.name  =  "Airbus A340-300"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 flight numbers for the aircraft Airbus A340-300?
SQL: SELECT T1.flno FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid WHERE T2.name  =  "Airbus A340-300"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 aircraft names and number of flights for each aircraft.
SQL: SELECT T2.name ,  count(*) FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid GROUP BY T1.aid
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 of each aircraft and how many flights does each one complete?
SQL: SELECT T2.name ,  count(*) FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid GROUP BY T1.aid
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 names for all aircraft with at least two flights.
SQL: SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid GROUP BY T1.aid HAVING count(*)  >= 2
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 for all aircrafts with at least 2 flights?
SQL: SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid GROUP BY T1.aid HAVING count(*)  >= 2
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 employees have certificate.
SQL: SELECT count(DISTINCT eid) FROM Certificate
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 count of distinct employees with certificates?
SQL: SELECT count(DISTINCT eid) FROM Certificate
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 ids for all employees who don't have a certificate.
SQL: SELECT eid FROM Employee EXCEPT SELECT eid FROM Certificate
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 ids of all employees that don't have certificates?
SQL: SELECT eid FROM Employee EXCEPT SELECT eid FROM Certificate
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 names for all aircrafts of which John Williams has certificates.
SQL: SELECT T3.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T1.name  =  "John Williams"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 of all aircrafts that John Williams have certificates to be able to fly?
SQL: SELECT T3.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T1.name  =  "John Williams"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 names for all employees who have certificate of Boeing 737-800.
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 of all employees who have a certificate to fly Boeing 737-800?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 names for all employees who have certificates on both Boeing 737-800 and Airbus A340-300.
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800" INTERSECT SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Airbus A340-300"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 of all employees who can fly both the Boeing 737-800 and the Airbus A340-300?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800" INTERSECT SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Airbus A340-300"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 names for all employees who do not have certificate of Boeing 737-800.
SQL: SELECT name FROM Employee EXCEPT SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 of all employees who are not certified to fly Boeing 737-800s?
SQL: SELECT name FROM Employee EXCEPT SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800"
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 name of aircraft which fewest people have its certificate.
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid GROUP BY T1.aid ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 of the aircraft that the least people are certified to fly?
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid GROUP BY T1.aid ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 name and distance of the aircrafts with more than 5000 distance and which at least 5 people have its certificate.
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid WHERE T2.distance  >  5000 GROUP BY T1.aid ORDER BY count(*)  >=  5
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 distance of every aircraft that can cover a distance of more than 5000 and which at least 5 people can fly?
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid WHERE T2.distance  >  5000 GROUP BY T1.aid ORDER BY count(*)  >=  5
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 salary and name of the employee who has the most number of aircraft certificates?
SQL: SELECT T1.name ,  T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 salaray and name of the employee that is certified to fly the most planes?
SQL: SELECT T1.name ,  T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 salary and name of the employee who has the most number of certificates on aircrafts with distance more than 5000?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.distance  >  5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: flight 
columns : [['flight number', 'number'], ['origin', 'text'], ['destination', 'text'], ['distance', 'number'], ['departure date', 'time'], ['arrival date', 'time'], ['price', 'number'], ['airline id', 'number']]

 -- Table: aircraft 
columns : [['airline id', 'number'], ['name', 'text'], ['distance', 'number']]

 -- Table: employee 
columns : [['employee id', 'number'], ['name', 'text'], ['salary', 'number']]

 -- Table: certificate 
columns : [['employee id', 'number'], ['airline id', '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 salaray and name of the employee with the most certificates to fly planes more than 5000?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.distance  >  5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: allergy type 
columns : [['allergy name', 'text'], ['allergy type', 'text']]

 -- Table: has allergy 
columns : [['stuid', 'number'], ['allergy', 'text']]

 -- Table: student 
columns : [['stuid', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

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 allergies are there?
SQL: SELECT count(DISTINCT allergy) FROM Allergy_type
-- Language PostgreSQL
        -- Tables: 
             -- Table: allergy type 
columns : [['allergy name', 'text'], ['allergy type', 'text']]

 -- Table: has allergy 
columns : [['stuid', 'number'], ['allergy', 'text']]

 -- Table: student 
columns : [['stuid', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

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 allergy entries are there?
SQL: SELECT count(DISTINCT allergy) FROM Allergy_type