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: Count the number of patients who stayed in room 112.
SQL: SELECT count(patient) FROM stay WHERE room  =  112
-- 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: How many patients' prescriptions are made by physician John Dorian?
SQL: SELECT count(T1.SSN) FROM patient AS T1 JOIN prescribes AS T2 ON T1.SSN  =  T2.patient JOIN physician AS T3 ON T2.physician  =  T3.employeeid WHERE T3.name = "John Dorian"
-- 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 number of patients' prescriptions physician John Dorian made.
SQL: SELECT count(T1.SSN) FROM patient AS T1 JOIN prescribes AS T2 ON T1.SSN  =  T2.patient JOIN physician AS T3 ON T2.physician  =  T3.employeeid WHERE T3.name = "John Dorian"
-- 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 name of medication used on the patient who stays in room 111?
SQL: SELECT T4.name FROM stay AS T1 JOIN patient AS T2 ON T1.Patient  =  T2.SSN JOIN Prescribes AS T3 ON T3.Patient  =  T2.SSN JOIN Medication AS T4 ON T3.Medication  =  T4.Code WHERE room  =  111
-- 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 name of the medication used for the patient staying in room 111?
SQL: SELECT T4.name FROM stay AS T1 JOIN patient AS T2 ON T1.Patient  =  T2.SSN JOIN Prescribes AS T3 ON T3.Patient  =  T2.SSN JOIN Medication AS T4 ON T3.Medication  =  T4.Code WHERE room  =  111
-- 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 patient who most recently stayed in room 111.
SQL: SELECT patient FROM stay WHERE room  =  111 ORDER BY staystart 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: What is the id of the patient who stayed in room 111 most recently?
SQL: SELECT patient FROM stay WHERE room  =  111 ORDER BY staystart 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: What is the name of the nurse has the most appointments?
SQL: SELECT T1.name FROM nurse AS T1 JOIN appointment AS T2 ON T1.employeeid  =  T2.prepnurse GROUP BY T1.employeeid ORDER BY count(*) 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 name of the nurse who has the largest number of appointments.
SQL: SELECT T1.name FROM nurse AS T1 JOIN appointment AS T2 ON T1.employeeid  =  T2.prepnurse GROUP BY T1.employeeid ORDER BY count(*) 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: How many patients do each physician take care of? List their names and number of patients they take care of.
SQL: SELECT T1.name ,  count(*) FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid  =  T2.PCP GROUP BY T1.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: Return the name of each physician and the number of patients he or she treats.
SQL: SELECT T1.name ,  count(*) FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid  =  T2.PCP GROUP BY T1.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: Find the name of physicians who are in charge of more than one patient.
SQL: SELECT T1.name FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid  =  T2.PCP GROUP BY T1.employeeid HAVING count(*)  >  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: Which physicians are in charge of more than one patient? Give me their names.
SQL: SELECT T1.name FROM physician AS T1 JOIN patient AS T2 ON T1.employeeid  =  T2.PCP GROUP BY T1.employeeid HAVING count(*)  >  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 number of rooms located on each block floor.
SQL: SELECT count(*) ,  T1.blockfloor FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockfloor
-- 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: How many rooms does each block floor have?
SQL: SELECT count(*) ,  T1.blockfloor FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockfloor
-- 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 number of rooms for different block code?
SQL: SELECT count(*) ,  T1.blockcode FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockcode
-- 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: How many rooms are located for each block code?
SQL: SELECT count(*) ,  T1.blockcode FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockcode
-- 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 unique block codes that have available rooms?
SQL: SELECT DISTINCT blockcode FROM room WHERE unavailable  =  0