sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 party events do we have?
SQL: SELECT count(*) FROM party_events
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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: Count the number of party events.
SQL: SELECT count(*) FROM party_events
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 party names and the number of events for each party.
SQL: SELECT T2.party_name ,  count(*) FROM party_events AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 events are there for each party?
SQL: SELECT T2.party_name ,  count(*) FROM party_events AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 member names who are not in charge of any event.
SQL: SELECT member_name FROM member EXCEPT SELECT T1.member_name FROM member AS T1 JOIN party_events AS T2 ON T1.member_id  =  T2.member_in_charge_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 members who are not in charge of any events?
SQL: SELECT member_name FROM member EXCEPT SELECT T1.member_name FROM member AS T1 JOIN party_events AS T2 ON T1.member_id  =  T2.member_in_charge_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 parties with at least 2 events?
SQL: SELECT T2.party_name FROM party_events AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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: Return the names of parties that have two or more events.
SQL: SELECT T2.party_name FROM party_events AS T1 JOIN party AS T2 ON T1.party_id  =  T2.party_id GROUP BY T1.party_id HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 member in charge of greatest number of events?
SQL: SELECT T1.member_name FROM member AS T1 JOIN party_events AS T2 ON T1.member_id  =  T2.member_in_charge_id GROUP BY T2.member_in_charge_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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: Return the name of the member who is in charge of the most events.
SQL: SELECT T1.member_name FROM member AS T1 JOIN party_events AS T2 ON T1.member_id  =  T2.member_in_charge_id GROUP BY T2.member_in_charge_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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: find the event names that have more than 2 records.
SQL: SELECT event_name FROM party_events GROUP BY event_name HAVING count(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 event names were used more than twice for party events?
SQL: SELECT event_name FROM party_events GROUP BY event_name HAVING count(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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 Annual Meeting events happened in the United Kingdom region?
SQL: SELECT count(*) FROM region AS t1 JOIN party AS t2 ON t1.region_id  =  t2.region_id JOIN party_events AS t3 ON t2.party_id  =  t3.party_id WHERE t1.region_name  =  "United Kingdom" AND t3.Event_Name  =  "Annaual Meeting"
-- Language PostgreSQL
        -- Tables: 
             -- Table: region 
columns : [['region id', 'number'], ['region name', 'text'], ['date', 'text'], ['label', 'text'], ['format', 'text'], ['catalogue', 'text']]

 -- Table: party 
columns : [['party id', 'number'], ['minister', 'text'], ['took office', 'text'], ['left office', 'text'], ['region id', 'number'], ['party name', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['member name', 'text'], ['party id', 'text'], ['in office', 'text']]

 -- Table: party events 
columns : [['event id', 'number'], ['event name', 'text'], ['party id', 'number'], ['member in charge 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: Count the number of Annual Meeting events that took place in the region of the United Kingdom.
SQL: SELECT count(*) FROM region AS t1 JOIN party AS t2 ON t1.region_id  =  t2.region_id JOIN party_events AS t3 ON t2.party_id  =  t3.party_id WHERE t1.region_name  =  "United Kingdom" AND t3.Event_Name  =  "Annaual Meeting"
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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 pilots are there?
SQL: SELECT count(*) FROM pilot
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: List the names of pilots in ascending order of rank.
SQL: SELECT Pilot_name FROM pilot ORDER BY Rank ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: What are the positions and teams of pilots?
SQL: SELECT POSITION ,  Team FROM pilot
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: List the distinct positions of pilots older than 30.
SQL: SELECT DISTINCT POSITION FROM pilot WHERE Age  >  30
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: Show the names of pilots from team "Bradley" or "Fordham".
SQL: SELECT Pilot_name FROM pilot WHERE Team  =  "Bradley" OR Team  =  "Fordham"
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: What is the joined year of the pilot of the highest rank?
SQL: SELECT Join_Year FROM pilot ORDER BY Rank ASC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: What are the different nationalities of pilots? Show each nationality and the number of pilots of each nationality.
SQL: SELECT Nationality ,  COUNT(*) FROM pilot GROUP BY Nationality
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: Show the most common nationality of pilots.
SQL: SELECT Nationality FROM pilot GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: Show the pilot positions that have both pilots joining after year 2005 and pilots joining before 2000.
SQL: SELECT POSITION FROM pilot WHERE Join_Year	 <  2000 INTERSECT SELECT POSITION FROM pilot WHERE Join_Year	 >  2005
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: Show the names of pilots and models of aircrafts they have flied with.
SQL: SELECT T3.Pilot_name ,  T2.Model FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: Show the names of pilots and fleet series of the aircrafts they have flied with in ascending order of the rank of the pilot.
SQL: SELECT T3.Pilot_name ,  T2.Fleet_Series FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID ORDER BY T3.Rank
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: Show the fleet series of the aircrafts flied by pilots younger than 34
SQL: SELECT T2.Fleet_Series FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID WHERE T3.Age  <  34
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: Show the names of pilots and the number of records they have.
SQL: SELECT T2.Pilot_name ,  COUNT(*) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID  =  T2.pilot_ID GROUP BY T2.Pilot_name
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: Show names of pilots that have more than one record.
SQL: SELECT T2.Pilot_name ,  COUNT(*) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID  =  T2.pilot_ID GROUP BY T2.Pilot_name HAVING COUNT(*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: aircraft 
columns : [['aircraft id', 'number'], ['order year', 'number'], ['manufacturer', 'text'], ['model', 'text'], ['fleet series', 'text'], ['powertrain', 'text'], ['fuel propulsion', 'text']]

 -- Table: pilot 
columns : [['pilot id', 'number'], ['pilot name', 'text'], ['rank', 'number'], ['age', 'number'], ['nationality', 'text'], ['position', 'text'], ['join year', 'number'], ['team', 'text']]

 -- Table: pilot record 
columns : [['record id', 'number'], ['pilot id', 'number'], ['aircraft id', 'number'], ['date', '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: List the names of pilots that do not have any record.
SQL: SELECT Pilot_name FROM pilot WHERE Pilot_ID NOT IN (SELECT Pilot_ID FROM pilot_record)
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference document types 
columns : [['document type code', 'text'], ['document type description', 'text']]

 -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: reference document status 
columns : [['document status code', 'text'], ['document status description', 'text']]

 -- Table: reference shipping agents 
columns : [['shipping agent code', 'text'], ['shipping agent name', 'text'], ['shipping agent description', 'text']]

 -- Table: documents 
columns : [['document id', 'number'], ['document status code', 'text'], ['document type code', 'text'], ['shipping agent code', 'text'], ['receipt date', 'time'], ['receipt number', 'text'], ['other details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['role code', 'text'], ['employee name', 'text'], ['other details', 'text']]

 -- Table: document drafts 
columns : [['document id', 'number'], ['draft number', 'number'], ['draft details', 'text']]

 -- Table: draft copies 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number']]

 -- Table: circulation history 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number'], ['employee id', 'number']]

 -- Table: documents mailed 
columns : [['document id', 'number'], ['mailed to address id', 'number'], ['mailing date', 'time']]

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 document status codes do we have?
SQL: SELECT document_status_code FROM Ref_Document_Status;
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference document types 
columns : [['document type code', 'text'], ['document type description', 'text']]

 -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: reference document status 
columns : [['document status code', 'text'], ['document status description', 'text']]

 -- Table: reference shipping agents 
columns : [['shipping agent code', 'text'], ['shipping agent name', 'text'], ['shipping agent description', 'text']]

 -- Table: documents 
columns : [['document id', 'number'], ['document status code', 'text'], ['document type code', 'text'], ['shipping agent code', 'text'], ['receipt date', 'time'], ['receipt number', 'text'], ['other details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['role code', 'text'], ['employee name', 'text'], ['other details', 'text']]

 -- Table: document drafts 
columns : [['document id', 'number'], ['draft number', 'number'], ['draft details', 'text']]

 -- Table: draft copies 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number']]

 -- Table: circulation history 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number'], ['employee id', 'number']]

 -- Table: documents mailed 
columns : [['document id', 'number'], ['mailed to address id', 'number'], ['mailing date', 'time']]

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 description of document status code 'working'?
SQL: SELECT document_status_description FROM Ref_Document_Status WHERE document_status_code = "working";
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference document types 
columns : [['document type code', 'text'], ['document type description', 'text']]

 -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: reference document status 
columns : [['document status code', 'text'], ['document status description', 'text']]

 -- Table: reference shipping agents 
columns : [['shipping agent code', 'text'], ['shipping agent name', 'text'], ['shipping agent description', 'text']]

 -- Table: documents 
columns : [['document id', 'number'], ['document status code', 'text'], ['document type code', 'text'], ['shipping agent code', 'text'], ['receipt date', 'time'], ['receipt number', 'text'], ['other details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['role code', 'text'], ['employee name', 'text'], ['other details', 'text']]

 -- Table: document drafts 
columns : [['document id', 'number'], ['draft number', 'number'], ['draft details', 'text']]

 -- Table: draft copies 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number']]

 -- Table: circulation history 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number'], ['employee id', 'number']]

 -- Table: documents mailed 
columns : [['document id', 'number'], ['mailed to address id', 'number'], ['mailing date', 'time']]

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 document type codes do we have?
SQL: SELECT document_type_code FROM Ref_Document_Types;
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference document types 
columns : [['document type code', 'text'], ['document type description', 'text']]

 -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: reference document status 
columns : [['document status code', 'text'], ['document status description', 'text']]

 -- Table: reference shipping agents 
columns : [['shipping agent code', 'text'], ['shipping agent name', 'text'], ['shipping agent description', 'text']]

 -- Table: documents 
columns : [['document id', 'number'], ['document status code', 'text'], ['document type code', 'text'], ['shipping agent code', 'text'], ['receipt date', 'time'], ['receipt number', 'text'], ['other details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['role code', 'text'], ['employee name', 'text'], ['other details', 'text']]

 -- Table: document drafts 
columns : [['document id', 'number'], ['draft number', 'number'], ['draft details', 'text']]

 -- Table: draft copies 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number']]

 -- Table: circulation history 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number'], ['employee id', 'number']]

 -- Table: documents mailed 
columns : [['document id', 'number'], ['mailed to address id', 'number'], ['mailing date', 'time']]

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 description of document type 'Paper'?
SQL: SELECT document_type_description FROM Ref_Document_Types WHERE document_type_code = "Paper";
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference document types 
columns : [['document type code', 'text'], ['document type description', 'text']]

 -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: reference document status 
columns : [['document status code', 'text'], ['document status description', 'text']]

 -- Table: reference shipping agents 
columns : [['shipping agent code', 'text'], ['shipping agent name', 'text'], ['shipping agent description', 'text']]

 -- Table: documents 
columns : [['document id', 'number'], ['document status code', 'text'], ['document type code', 'text'], ['shipping agent code', 'text'], ['receipt date', 'time'], ['receipt number', 'text'], ['other details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['role code', 'text'], ['employee name', 'text'], ['other details', 'text']]

 -- Table: document drafts 
columns : [['document id', 'number'], ['draft number', 'number'], ['draft details', 'text']]

 -- Table: draft copies 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number']]

 -- Table: circulation history 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number'], ['employee id', 'number']]

 -- Table: documents mailed 
columns : [['document id', 'number'], ['mailed to address id', 'number'], ['mailing date', 'time']]

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 shipping agent names?
SQL: SELECT shipping_agent_name FROM Ref_Shipping_Agents;
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference document types 
columns : [['document type code', 'text'], ['document type description', 'text']]

 -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: reference document status 
columns : [['document status code', 'text'], ['document status description', 'text']]

 -- Table: reference shipping agents 
columns : [['shipping agent code', 'text'], ['shipping agent name', 'text'], ['shipping agent description', 'text']]

 -- Table: documents 
columns : [['document id', 'number'], ['document status code', 'text'], ['document type code', 'text'], ['shipping agent code', 'text'], ['receipt date', 'time'], ['receipt number', 'text'], ['other details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['role code', 'text'], ['employee name', 'text'], ['other details', 'text']]

 -- Table: document drafts 
columns : [['document id', 'number'], ['draft number', 'number'], ['draft details', 'text']]

 -- Table: draft copies 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number']]

 -- Table: circulation history 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number'], ['employee id', 'number']]

 -- Table: documents mailed 
columns : [['document id', 'number'], ['mailed to address id', 'number'], ['mailing date', 'time']]

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 shipping agent code of shipping agent UPS?
SQL: SELECT shipping_agent_code FROM Ref_Shipping_Agents WHERE shipping_agent_name = "UPS";
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference document types 
columns : [['document type code', 'text'], ['document type description', 'text']]

 -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: reference document status 
columns : [['document status code', 'text'], ['document status description', 'text']]

 -- Table: reference shipping agents 
columns : [['shipping agent code', 'text'], ['shipping agent name', 'text'], ['shipping agent description', 'text']]

 -- Table: documents 
columns : [['document id', 'number'], ['document status code', 'text'], ['document type code', 'text'], ['shipping agent code', 'text'], ['receipt date', 'time'], ['receipt number', 'text'], ['other details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['role code', 'text'], ['employee name', 'text'], ['other details', 'text']]

 -- Table: document drafts 
columns : [['document id', 'number'], ['draft number', 'number'], ['draft details', 'text']]

 -- Table: draft copies 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number']]

 -- Table: circulation history 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number'], ['employee id', 'number']]

 -- Table: documents mailed 
columns : [['document id', 'number'], ['mailed to address id', 'number'], ['mailing date', 'time']]

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 all role codes?
SQL: SELECT role_code FROM ROLES;
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference document types 
columns : [['document type code', 'text'], ['document type description', 'text']]

 -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: reference document status 
columns : [['document status code', 'text'], ['document status description', 'text']]

 -- Table: reference shipping agents 
columns : [['shipping agent code', 'text'], ['shipping agent name', 'text'], ['shipping agent description', 'text']]

 -- Table: documents 
columns : [['document id', 'number'], ['document status code', 'text'], ['document type code', 'text'], ['shipping agent code', 'text'], ['receipt date', 'time'], ['receipt number', 'text'], ['other details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['role code', 'text'], ['employee name', 'text'], ['other details', 'text']]

 -- Table: document drafts 
columns : [['document id', 'number'], ['draft number', 'number'], ['draft details', 'text']]

 -- Table: draft copies 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number']]

 -- Table: circulation history 
columns : [['document id', 'number'], ['draft number', 'number'], ['copy number', 'number'], ['employee id', 'number']]

 -- Table: documents mailed 
columns : [['document id', 'number'], ['mailed to address id', 'number'], ['mailing date', 'time']]

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 description of role code ED?
SQL: SELECT role_description FROM ROLES WHERE role_code = "ED";