sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 nurses are in charge of patients undergoing treatments?
SQL: SELECT DISTINCT T2.name FROM undergoes AS T1 JOIN nurse AS T2 ON T1.AssistingNurse  =  T2.EmployeeID
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', 'number']]

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



Question: List the names of all distinct medications, ordered in an alphabetical order.
SQL: SELECT DISTINCT name FROM medication ORDER BY name
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 alphabetically ordered list of all distinct medications?
SQL: SELECT DISTINCT name FROM medication ORDER BY name
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', 'number']]

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



Question: What are the names of the physician who prescribed the highest dose?
SQL: SELECT T1.name FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician ORDER BY T2.dose DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 physician who prescribed the highest dose. What is his or her name?
SQL: SELECT T1.name FROM physician AS T1 JOIN prescribes AS T2 ON T1.employeeid = T2.physician ORDER BY T2.dose DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 physicians' employee ids together with their primary affiliation departments' ids.
SQL: SELECT physician ,  department FROM affiliated_with WHERE primaryaffiliation  =  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 each physician's employee id and department id primarily affiliated.
SQL: SELECT physician ,  department FROM affiliated_with WHERE primaryaffiliation  =  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', 'number']]

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



Question: List the names of departments where some physicians are primarily affiliated with.
SQL: SELECT DISTINCT T2.name FROM affiliated_with AS T1 JOIN department AS T2 ON T1.department = T2.departmentid WHERE PrimaryAffiliation  =  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 departments that have primarily affiliated physicians.
SQL: SELECT DISTINCT T2.name FROM affiliated_with AS T1 JOIN department AS T2 ON T1.department = T2.departmentid WHERE PrimaryAffiliation  =  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 nurses are on call with block floor 1 and block code 1? Tell me their names.
SQL: SELECT nurse FROM on_call WHERE blockfloor  =  1 AND blockcode  =  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 ids of the nurses who are on call in block floor 1 and block code 1.
SQL: SELECT nurse FROM on_call WHERE blockfloor  =  1 AND blockcode  =  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 highest cost, lowest cost and average cost of procedures?
SQL: SELECT MAX(cost) ,  MIN(cost) ,  AVG(cost) FROM procedures
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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: Tell me the highest, lowest, and average cost of procedures.
SQL: SELECT MAX(cost) ,  MIN(cost) ,  AVG(cost) FROM procedures
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 and cost of all procedures sorted by the cost from the highest to the lowest.
SQL: SELECT name ,  cost FROM procedures ORDER BY cost DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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: Sort the list of names and costs of all procedures in the descending order of cost.
SQL: SELECT name ,  cost FROM procedures ORDER BY cost DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 three most expensive procedures.
SQL: SELECT name FROM procedures ORDER BY cost LIMIT 3
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 three most costly procedures?
SQL: SELECT name FROM procedures ORDER BY cost LIMIT 3
-- Language PostgreSQL
        -- Tables: 
             -- Table: physician 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['ssn', 'number']]

 -- Table: department 
columns : [['departmentid', 'number'], ['name', 'text'], ['head', 'number']]

 -- Table: affiliated with 
columns : [['physician', 'number'], ['department', 'number'], ['primary affiliation', 'boolean']]

 -- Table: procedures 
columns : [['code', 'number'], ['name', 'text'], ['cost', 'number']]

 -- Table: trained in 
columns : [['physician', 'number'], ['treatment', 'number'], ['certification date', 'time'], ['certification expires', 'time']]

 -- Table: patient 
columns : [['ssn', 'number'], ['name', 'text'], ['address', 'text'], ['phone', 'text'], ['insurance id', 'number'], ['pcp', 'number']]

 -- Table: nurse 
columns : [['employee id', 'number'], ['name', 'text'], ['position', 'text'], ['registered', 'boolean'], ['ssn', 'number']]

 -- Table: appointment 
columns : [['appointment id', 'number'], ['patient', 'number'], ['prep nurse', 'number'], ['physician', 'number'], ['start', 'time'], ['end', 'time'], ['examination room', 'text']]

 -- Table: medication 
columns : [['code', 'number'], ['name', 'text'], ['brand', 'text'], ['description', 'text']]

 -- Table: prescribes 
columns : [['physician', 'number'], ['patient', 'number'], ['medication', 'number'], ['date', 'time'], ['appointment', 'number'], ['dose', 'text']]

 -- Table: block 
columns : [['block floor', 'number'], ['block code', 'number']]

 -- Table: room 
columns : [['roomnumber', 'number'], ['room type', 'text'], ['block floor', 'number'], ['block code', 'number'], ['unavailable', 'boolean']]

 -- Table: on call 
columns : [['nurse', 'number'], ['block floor', 'number'], ['block code', 'number'], ['oncall start', 'time'], ['oncall end', 'time']]

 -- Table: stay 
columns : [['stay id', 'number'], ['patient', 'number'], ['room', 'number'], ['stay start', 'time'], ['stay end', 'time']]

 -- Table: undergoes 
columns : [['patient', 'number'], ['procedures', 'number'], ['stay', 'number'], ['date undergoes', 'time'], ['physician', 'number'], ['assisting nurse', '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 physicians who are trained in a procedure that costs more than 5000.
SQL: SELECT T1.name FROM physician AS T1 JOIN trained_in AS T2 ON T1.employeeid  =  T2.physician JOIN procedures AS T3 ON T3.code  =  T2.treatment WHERE T3.cost  >  5000