sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 schools have students playing in goalie and mid-field positions?
SQL: SELECT COUNT(*) FROM (SELECT cName FROM  tryout WHERE pPos  =  'goalie' INTERSECT SELECT cName FROM  tryout WHERE pPos  =  'mid')
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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: Find the names of schools that have some players in the mid position but not in the goalie position.
SQL: SELECT cName FROM  tryout WHERE pPos  =  'mid' EXCEPT SELECT cName FROM  tryout WHERE pPos  =  'goalie'
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 names of the schools with some players in the mid position but no goalies?
SQL: SELECT cName FROM  tryout WHERE pPos  =  'mid' EXCEPT SELECT cName FROM  tryout WHERE pPos  =  'goalie'
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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: Find the names of states that have some college students playing in the mid position but not in the goalie position.
SQL: SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie'
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 names of all the states with college students playing in the mid position but no goalies?
SQL: SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie'
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 states that have some college students playing in the mid position but not in the goalie position.
SQL: SELECT COUNT(*) FROM (SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie')
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 count of states with college students playing in the mid position but not as goalies?
SQL: SELECT COUNT(*) FROM (SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie')
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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: Find the states where have the colleges whose enrollments are less than the largest size.
SQL: SELECT DISTINCT state FROM college WHERE enr  <  (SELECT max(enr) FROM college)
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 states with colleges that have enrollments less than the some other college?
SQL: SELECT DISTINCT state FROM college WHERE enr  <  (SELECT max(enr) FROM college)
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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: Find names of colleges with enrollment greater than that of some (at least one) college in the FL state.
SQL: SELECT DISTINCT cName FROM college WHERE enr  >  (SELECT min(enr) FROM college WHERE state  =  'FL')
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 names of the colleges that are larger than at least one college in Florida?
SQL: SELECT DISTINCT cName FROM college WHERE enr  >  (SELECT min(enr) FROM college WHERE state  =  'FL')
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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: Find names of all colleges whose enrollment is greater than that of all colleges in the FL state.
SQL: SELECT cName FROM college WHERE enr  >  (SELECT max(enr) FROM college WHERE state  =  'FL')
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 names of all colleges with a larger enrollment than the largest college in Florida?
SQL: SELECT cName FROM college WHERE enr  >  (SELECT max(enr) FROM college WHERE state  =  'FL')
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 total number of enrollment of schools that do not have any goalie player?
SQL: SELECT sum(enr) FROM college WHERE cName NOT IN (SELECT cName FROM tryout WHERE pPos  =  "goalie")
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 total number of students enrolled in schools without any goalies?
SQL: SELECT sum(enr) FROM college WHERE cName NOT IN (SELECT cName FROM tryout WHERE pPos  =  "goalie")
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 number of states that has some college whose enrollment is larger than the average enrollment?
SQL: SELECT count(DISTINCT state) FROM college WHERE enr  >  (SELECT avg(enr) FROM college)
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 states have a college with more students than average?
SQL: SELECT count(DISTINCT state) FROM college WHERE enr  >  (SELECT avg(enr) FROM college)
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 number of states that has some colleges whose enrollment is smaller than the average enrollment?
SQL: SELECT count(DISTINCT state) FROM college WHERE enr  <  (SELECT avg(enr) FROM college)
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college name', 'text'], ['state', 'text'], ['enrollment', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['player name', 'text'], ['yes card', 'text'], ['training hours', 'number']]

 -- Table: tryout 
columns : [['player id', 'number'], ['college name', 'text'], ['player position', 'text'], ['decision', '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 states have smaller colleges than average?
SQL: SELECT count(DISTINCT state) FROM college WHERE enr  <  (SELECT avg(enr) FROM college)
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 devices are there?
SQL: SELECT count(*) FROM device
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 devices.
SQL: SELECT count(*) FROM device
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', 'number']]

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



Question: List the carriers of devices in ascending alphabetical order.
SQL: SELECT Carrier FROM device ORDER BY Carrier ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', 'number']]

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



Question: What are the different carriers for devices, listed in alphabetical order?
SQL: SELECT Carrier FROM device ORDER BY Carrier ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 carriers of devices whose software platforms are not "Android"?
SQL: SELECT Carrier FROM device WHERE Software_Platform != 'Android'
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 device carriers that do not have Android as their software platform.
SQL: SELECT Carrier FROM device WHERE Software_Platform != 'Android'
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 shops in ascending order of open year?
SQL: SELECT Shop_Name FROM shop ORDER BY Open_Year ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 shops, ordered by year of opening ascending.
SQL: SELECT Shop_Name FROM shop ORDER BY Open_Year ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 quantity of stocks?
SQL: SELECT avg(Quantity) FROM stock
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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: Give the average quantity of stocks.
SQL: SELECT avg(Quantity) FROM stock
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', 'number']]

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



Question: What are the names and location of the shops in ascending alphabetical order of name.
SQL: SELECT Shop_Name ,  LOCATION FROM shop ORDER BY Shop_Name ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 and locations of shops, ordered by name in alphabetical order.
SQL: SELECT Shop_Name ,  LOCATION FROM shop ORDER BY Shop_Name ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 different software platforms are there for devices?
SQL: SELECT count(DISTINCT Software_Platform) FROM device
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 different software platforms.
SQL: SELECT count(DISTINCT Software_Platform) FROM device
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', 'number']]

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



Question: List the open date of open year of the shop named "Apple".
SQL: SELECT Open_Date ,  Open_Year FROM shop WHERE Shop_Name  =  "Apple"
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 open dates and years for the shop named Apple?
SQL: SELECT Open_Date ,  Open_Year FROM shop WHERE Shop_Name  =  "Apple"
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', 'number']]

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



Question: List the name of the shop with the latest open year.
SQL: SELECT Shop_Name FROM shop ORDER BY Open_Year DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 shop name corresponding to the shop that opened in the most recent year?
SQL: SELECT Shop_Name FROM shop ORDER BY Open_Year DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 of shops and the carriers of devices they have in stock.
SQL: SELECT T3.Shop_Name ,  T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID JOIN shop AS T3 ON T1.Shop_ID  =  T3.Shop_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 device shops, and what are the carriers that they carry devices in stock for?
SQL: SELECT T3.Shop_Name ,  T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID JOIN shop AS T3 ON T1.Shop_ID  =  T3.Shop_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 of shops that have more than one kind of device in stock.
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID HAVING COUNT(*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 shops that have more than a single kind of device in stock?
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID HAVING COUNT(*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 the shop that has the most kind of devices in stock.
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 shop that has the most different kinds of devices in stock?
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 the shop that have the largest quantity of devices in stock.
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY SUM(T1.quantity) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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 shop that has the greatest quantity of devices in stock?
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY SUM(T1.quantity) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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: Please show different software platforms and the corresponding number of devices using each.
SQL: SELECT Software_Platform ,  COUNT(*) FROM device GROUP BY Software_Platform
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', 'number']]

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



Question: What are the different software platforms for devices, and how many devices have each?
SQL: SELECT Software_Platform ,  COUNT(*) FROM device GROUP BY Software_Platform
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', '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: Please show the software platforms of devices in descending order of the count.
SQL: SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', 'number']]

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



Question: What are the different software platforms for devices, ordered by frequency descending?
SQL: SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: device 
columns : [['device id', 'number'], ['device', 'text'], ['carrier', 'text'], ['package version', 'text'], ['applications', 'text'], ['software platform', 'text']]

 -- Table: shop 
columns : [['shop id', 'number'], ['shop name', 'text'], ['location', 'text'], ['open date', 'text'], ['open year', 'number']]

 -- Table: stock 
columns : [['shop id', 'number'], ['device id', 'number'], ['quantity', 'number']]

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



Question: List the software platform shared by the greatest number of devices.
SQL: SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC LIMIT 1