sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 unit of measurement and product category code of product named "chervil".
SQL: SELECT t2.unit_of_measure ,  t2.product_category_code FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code WHERE t1.product_name  =  "chervil"
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 unit of measure and category code for the 'chervil' product?
SQL: SELECT t2.unit_of_measure ,  t2.product_category_code FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code WHERE t1.product_name  =  "chervil"
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 product names that are colored 'white' but do not have unit of measurement "Handful".
SQL: SELECT t1.product_name FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code JOIN ref_colors AS t3 ON t1.color_code  =  t3.color_code WHERE t3.color_description  =  "white" AND t2.unit_of_measure != "Handful"
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 products that are not 'white' in color and are not measured by the unit 'Handful'?
SQL: SELECT t1.product_name FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code JOIN ref_colors AS t3 ON t1.color_code  =  t3.color_code WHERE t3.color_description  =  "white" AND t2.unit_of_measure != "Handful"
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 description of the color for most products?
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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: Return the color description that is most common across all products.
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 description of the color used by least products?
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) ASC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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: Give the color description that is least common across products.
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) ASC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 characteristic name used by most number of the products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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: Return the name of the characteristic that is most common across all products.
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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, details and data types of the characteristics which are never used by any product?
SQL: SELECT characteristic_name ,  other_characteristic_details ,  characteristic_data_type FROM CHARACTERISTICS EXCEPT SELECT t1.characteristic_name ,  t1.other_characteristic_details ,  t1.characteristic_data_type FROM CHARACTERISTICS AS t1 JOIN product_characteristics AS t2 ON t1.characteristic_id  =  t2.characteristic_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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: Give the names, details, and data types of characteristics that are not found in any product.
SQL: SELECT characteristic_name ,  other_characteristic_details ,  characteristic_data_type FROM CHARACTERISTICS EXCEPT SELECT t1.characteristic_name ,  t1.other_characteristic_details ,  t1.characteristic_data_type FROM CHARACTERISTICS AS t1 JOIN product_characteristics AS t2 ON t1.characteristic_id  =  t2.characteristic_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 characteristic names used at least twice across all products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name HAVING count(*)  >= 2
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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: Give the names of characteristics that are in two or more products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name HAVING count(*)  >= 2
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 colors are never used by any product?
SQL: SELECT count(*) FROM Ref_colors WHERE color_code NOT IN ( SELECT color_code FROM products )
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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: Count the number of colors that are not used in any products.
SQL: SELECT count(*) FROM Ref_colors WHERE color_code NOT IN ( SELECT color_code FROM products )
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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?
SQL: SELECT count(*) FROM event
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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: List all the event names by year from the most recent to the oldest.
SQL: SELECT name FROM event ORDER BY YEAR DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event id', 'number']]

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



Question: What is the name of the event that happened in the most recent year?
SQL: SELECT name FROM event ORDER BY YEAR DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 stadiums are there?
SQL: SELECT count(*) FROM stadium
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 name of the stadium that has the maximum capacity.
SQL: SELECT name FROM stadium ORDER BY capacity DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 names of stadiums whose capacity is smaller than the average capacity.
SQL: SELECT name FROM stadium WHERE capacity  <  (SELECT avg(capacity) FROM stadium)
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 country that has the most stadiums.
SQL: SELECT country FROM stadium GROUP BY country ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 country has at most 3 stadiums listed?
SQL: SELECT country FROM stadium GROUP BY country HAVING count(*)  <=  3
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 country has both stadiums with capacity greater than 60000 and stadiums with capacity less than 50000?
SQL: SELECT country FROM stadium WHERE capacity  >  60000 INTERSECT SELECT country FROM stadium WHERE capacity  <  50000
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 cities have a stadium that was opened before the year of 2006?
SQL: SELECT count(DISTINCT city) FROM stadium WHERE opening_year  <  2006
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 stadiums does each country have?
SQL: SELECT country ,  count(*) FROM stadium GROUP BY country
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 countries do not have a stadium that was opened after 2006?
SQL: SELECT country FROM stadium EXCEPT SELECT country FROM stadium WHERE opening_year  >  2006
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 stadiums are not in country "Russia"?
SQL: SELECT count(*) FROM stadium WHERE country != 'Russia'
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 names of all swimmers, sorted by their 100 meter scores in ascending order.
SQL: SELECT name FROM swimmer ORDER BY meter_100
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 different countries are all the swimmers from?
SQL: SELECT count(DISTINCT nationality) FROM swimmer
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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: List countries that have more than one swimmer.
SQL: SELECT nationality ,  count(*) FROM swimmer GROUP BY nationality HAVING count(*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 all 200 meter and 300 meter results of swimmers with nationality "Australia".
SQL: SELECT meter_200 ,  meter_300 FROM swimmer WHERE nationality  =  'Australia'
-- Language PostgreSQL
        -- Tables: 
             -- Table: swimmer 
columns : [['id', 'number'], ['name', 'text'], ['nationality', 'text'], ['meter 100', 'number'], ['meter 200', 'text'], ['meter 300', 'text'], ['meter 400', 'text'], ['meter 500', 'text'], ['meter 600', 'text'], ['meter 700', 'text'], ['time', 'text']]

 -- Table: stadium 
columns : [['id', 'number'], ['name', 'text'], ['capacity', 'number'], ['city', 'text'], ['country', 'text'], ['opening year', 'number']]

 -- Table: event 
columns : [['id', 'number'], ['name', 'text'], ['stadium id', 'number'], ['year', 'text']]

 -- Table: record 
columns : [['id', 'number'], ['result', 'text'], ['swimmer id', 'number'], ['event 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 names of swimmers who has a result of "win".
SQL: SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Win'