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

 -- Table: reference detention type 
columns : [['detention type code', 'text'], ['detention type description', 'text']]

 -- Table: reference incident type 
columns : [['incident type code', 'text'], ['incident type description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['line 3', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text'], ['other address details', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['date first rental', 'time'], ['date left university', 'time'], ['other student details', 'text']]

 -- Table: teachers 
columns : [['teacher id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['gender', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['other details', 'text']]

 -- Table: assessment notes 
columns : [['notes id', 'number'], ['student id', 'number'], ['teacher id', 'number'], ['date of notes', 'time'], ['text of notes', 'text'], ['other details', 'text']]

 -- Table: behavior incident 
columns : [['incident id', 'number'], ['incident type code', 'text'], ['student id', 'number'], ['date incident start', 'time'], ['date incident end', 'time'], ['incident summary', 'text'], ['recommendations', 'text'], ['other details', 'text']]

 -- Table: detention 
columns : [['detention id', 'number'], ['detention type code', 'text'], ['teacher id', 'number'], ['datetime detention start', 'time'], ['datetime detention end', 'time'], ['detention summary', 'text'], ['other details', 'text']]

 -- Table: student addresses 
columns : [['student id', 'number'], ['address id', 'number'], ['date address from', 'time'], ['date address to', 'time'], ['monthly rental', 'number'], ['other details', 'text']]

 -- Table: students in detention 
columns : [['student id', 'number'], ['detention id', 'number'], ['incident id', 'number']]

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



Question: What are the line 1 and average monthly rentals of all student addresses?
SQL: SELECT T1.line_1 ,  avg(T2.monthly_rental) FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id GROUP BY T2.address_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference address types 
columns : [['address type code', 'text'], ['address type description', 'text']]

 -- Table: reference detention type 
columns : [['detention type code', 'text'], ['detention type description', 'text']]

 -- Table: reference incident type 
columns : [['incident type code', 'text'], ['incident type description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['line 3', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text'], ['other address details', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['date first rental', 'time'], ['date left university', 'time'], ['other student details', 'text']]

 -- Table: teachers 
columns : [['teacher id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['gender', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['other details', 'text']]

 -- Table: assessment notes 
columns : [['notes id', 'number'], ['student id', 'number'], ['teacher id', 'number'], ['date of notes', 'time'], ['text of notes', 'text'], ['other details', 'text']]

 -- Table: behavior incident 
columns : [['incident id', 'number'], ['incident type code', 'text'], ['student id', 'number'], ['date incident start', 'time'], ['date incident end', 'time'], ['incident summary', 'text'], ['recommendations', 'text'], ['other details', 'text']]

 -- Table: detention 
columns : [['detention id', 'number'], ['detention type code', 'text'], ['teacher id', 'number'], ['datetime detention start', 'time'], ['datetime detention end', 'time'], ['detention summary', 'text'], ['other details', 'text']]

 -- Table: student addresses 
columns : [['student id', 'number'], ['address id', 'number'], ['date address from', 'time'], ['date address to', 'time'], ['monthly rental', 'number'], ['other details', 'text']]

 -- Table: students in detention 
columns : [['student id', 'number'], ['detention id', 'number'], ['incident id', 'number']]

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



Question: What is the zip code of the address where the teacher with first name "Lyla" lives?
SQL: SELECT T1.zip_postcode FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id WHERE T2.first_name  =  "Lyla"
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference address types 
columns : [['address type code', 'text'], ['address type description', 'text']]

 -- Table: reference detention type 
columns : [['detention type code', 'text'], ['detention type description', 'text']]

 -- Table: reference incident type 
columns : [['incident type code', 'text'], ['incident type description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['line 3', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text'], ['other address details', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['date first rental', 'time'], ['date left university', 'time'], ['other student details', 'text']]

 -- Table: teachers 
columns : [['teacher id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['gender', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['other details', 'text']]

 -- Table: assessment notes 
columns : [['notes id', 'number'], ['student id', 'number'], ['teacher id', 'number'], ['date of notes', 'time'], ['text of notes', 'text'], ['other details', 'text']]

 -- Table: behavior incident 
columns : [['incident id', 'number'], ['incident type code', 'text'], ['student id', 'number'], ['date incident start', 'time'], ['date incident end', 'time'], ['incident summary', 'text'], ['recommendations', 'text'], ['other details', 'text']]

 -- Table: detention 
columns : [['detention id', 'number'], ['detention type code', 'text'], ['teacher id', 'number'], ['datetime detention start', 'time'], ['datetime detention end', 'time'], ['detention summary', 'text'], ['other details', 'text']]

 -- Table: student addresses 
columns : [['student id', 'number'], ['address id', 'number'], ['date address from', 'time'], ['date address to', 'time'], ['monthly rental', 'number'], ['other details', 'text']]

 -- Table: students in detention 
columns : [['student id', 'number'], ['detention id', 'number'], ['incident id', 'number']]

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



Question: What are the email addresses of teachers whose address has zip code "918"?
SQL: SELECT T2.email_address FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id WHERE T1.zip_postcode  =  "918"
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference address types 
columns : [['address type code', 'text'], ['address type description', 'text']]

 -- Table: reference detention type 
columns : [['detention type code', 'text'], ['detention type description', 'text']]

 -- Table: reference incident type 
columns : [['incident type code', 'text'], ['incident type description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['line 3', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text'], ['other address details', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['date first rental', 'time'], ['date left university', 'time'], ['other student details', 'text']]

 -- Table: teachers 
columns : [['teacher id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['gender', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['other details', 'text']]

 -- Table: assessment notes 
columns : [['notes id', 'number'], ['student id', 'number'], ['teacher id', 'number'], ['date of notes', 'time'], ['text of notes', 'text'], ['other details', 'text']]

 -- Table: behavior incident 
columns : [['incident id', 'number'], ['incident type code', 'text'], ['student id', 'number'], ['date incident start', 'time'], ['date incident end', 'time'], ['incident summary', 'text'], ['recommendations', 'text'], ['other details', 'text']]

 -- Table: detention 
columns : [['detention id', 'number'], ['detention type code', 'text'], ['teacher id', 'number'], ['datetime detention start', 'time'], ['datetime detention end', 'time'], ['detention summary', 'text'], ['other details', 'text']]

 -- Table: student addresses 
columns : [['student id', 'number'], ['address id', 'number'], ['date address from', 'time'], ['date address to', 'time'], ['monthly rental', 'number'], ['other details', 'text']]

 -- Table: students in detention 
columns : [['student id', 'number'], ['detention id', 'number'], ['incident id', 'number']]

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



Question: How many students are not involved in any behavior incident?
SQL: SELECT count(*) FROM STUDENTS WHERE student_id NOT IN ( SELECT student_id FROM Behavior_Incident )
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference address types 
columns : [['address type code', 'text'], ['address type description', 'text']]

 -- Table: reference detention type 
columns : [['detention type code', 'text'], ['detention type description', 'text']]

 -- Table: reference incident type 
columns : [['incident type code', 'text'], ['incident type description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['line 3', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text'], ['other address details', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['date first rental', 'time'], ['date left university', 'time'], ['other student details', 'text']]

 -- Table: teachers 
columns : [['teacher id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['gender', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['other details', 'text']]

 -- Table: assessment notes 
columns : [['notes id', 'number'], ['student id', 'number'], ['teacher id', 'number'], ['date of notes', 'time'], ['text of notes', 'text'], ['other details', 'text']]

 -- Table: behavior incident 
columns : [['incident id', 'number'], ['incident type code', 'text'], ['student id', 'number'], ['date incident start', 'time'], ['date incident end', 'time'], ['incident summary', 'text'], ['recommendations', 'text'], ['other details', 'text']]

 -- Table: detention 
columns : [['detention id', 'number'], ['detention type code', 'text'], ['teacher id', 'number'], ['datetime detention start', 'time'], ['datetime detention end', 'time'], ['detention summary', 'text'], ['other details', 'text']]

 -- Table: student addresses 
columns : [['student id', 'number'], ['address id', 'number'], ['date address from', 'time'], ['date address to', 'time'], ['monthly rental', 'number'], ['other details', 'text']]

 -- Table: students in detention 
columns : [['student id', 'number'], ['detention id', 'number'], ['incident id', 'number']]

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



Question: Find the last names of teachers who are not involved in any detention.
SQL: SELECT last_name FROM Teachers EXCEPT SELECT T1.last_name FROM Teachers AS T1 JOIN Detention AS T2 ON T1.teacher_id  =  T2.teacher_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference address types 
columns : [['address type code', 'text'], ['address type description', 'text']]

 -- Table: reference detention type 
columns : [['detention type code', 'text'], ['detention type description', 'text']]

 -- Table: reference incident type 
columns : [['incident type code', 'text'], ['incident type description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['line 3', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text'], ['other address details', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['date first rental', 'time'], ['date left university', 'time'], ['other student details', 'text']]

 -- Table: teachers 
columns : [['teacher id', 'number'], ['address id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['gender', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['other details', 'text']]

 -- Table: assessment notes 
columns : [['notes id', 'number'], ['student id', 'number'], ['teacher id', 'number'], ['date of notes', 'time'], ['text of notes', 'text'], ['other details', 'text']]

 -- Table: behavior incident 
columns : [['incident id', 'number'], ['incident type code', 'text'], ['student id', 'number'], ['date incident start', 'time'], ['date incident end', 'time'], ['incident summary', 'text'], ['recommendations', 'text'], ['other details', 'text']]

 -- Table: detention 
columns : [['detention id', 'number'], ['detention type code', 'text'], ['teacher id', 'number'], ['datetime detention start', 'time'], ['datetime detention end', 'time'], ['detention summary', 'text'], ['other details', 'text']]

 -- Table: student addresses 
columns : [['student id', 'number'], ['address id', 'number'], ['date address from', 'time'], ['date address to', 'time'], ['monthly rental', 'number'], ['other details', 'text']]

 -- Table: students in detention 
columns : [['student id', 'number'], ['detention id', 'number'], ['incident id', 'number']]

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



Question: What are the line 1 of addresses shared by some students and some teachers?
SQL: SELECT T1.line_1 FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.address_id INTERSECT SELECT T1.line_1 FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: How many assets does each maintenance contract contain? List the number and the contract id.
SQL: SELECT count(*) ,  T1.maintenance_contract_id FROM Maintenance_Contracts AS T1 JOIN Assets AS T2 ON T1.maintenance_contract_id  =  T2.maintenance_contract_id GROUP BY T1.maintenance_contract_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: How many assets does each third party company supply? List the count and the company id.
SQL: SELECT count(*) ,  T1.company_id FROM Third_Party_Companies AS T1 JOIN Assets AS T2 ON T1.company_id  =  T2.supplier_company_id GROUP BY T1.company_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: What is the name and id of the staff who recorded the fault log but has not contacted any visiting engineers?
SQL: SELECT T1.staff_name ,  T1.staff_id FROM Staff AS T1 JOIN Fault_Log AS T2 ON T1.staff_id  =  T2.recorded_by_staff_id EXCEPT SELECT T3.staff_name ,  T3.staff_id FROM Staff AS T3 JOIN Engineer_Visits AS T4 ON T3.staff_id  =  T4.contact_staff_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: Which engineer has visited the most times? Show the engineer id, first name and last name.
SQL: SELECT T1.engineer_id ,  T1.first_name ,  T1.last_name FROM Maintenance_Engineers AS T1 JOIN Engineer_Visits AS T2 GROUP BY T1.engineer_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: Which parts have more than 2 faults? Show the part name and id.
SQL: SELECT T1.part_name ,  T1.part_id FROM Parts AS T1 JOIN Part_Faults AS T2 ON T1.part_id  =  T2.part_id GROUP BY T1.part_id HAVING count(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: List all every engineer's first name, last name, details and coresponding skill description.
SQL: SELECT T1.first_name ,  T1.last_name ,  T1.other_details ,  T3.skill_description FROM Maintenance_Engineers AS T1 JOIN Engineer_Skills AS T2 ON T1.engineer_id  =  T2.engineer_id JOIN Skills AS T3 ON T2.skill_id  =  T3.skill_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: For all the faults of different parts, what are all the decriptions of the skills required to fix them? List the name of the faults and the skill description.
SQL: SELECT T1.fault_short_name ,  T3.skill_description FROM Part_Faults AS T1 JOIN Skills_Required_To_Fix AS T2 ON T1.part_fault_id  =  T2.part_fault_id JOIN Skills AS T3 ON T2.skill_id  =  T3.skill_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: How many assets can each parts be used in? List the part name and the number.
SQL: SELECT T1.part_name ,  count(*) FROM Parts AS T1 JOIN Asset_Parts AS T2 ON T1.part_id  =  T2.part_id GROUP BY T1.part_name
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: What are all the fault descriptions and the fault status of all the faults recoreded in the logs?
SQL: SELECT T1.fault_description ,  T2.fault_status FROM Fault_Log AS T1 JOIN Fault_Log_Parts AS T2 ON T1.fault_log_entry_id  =  T2.fault_log_entry_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: third party companies 
columns : [['company id', 'number'], ['company type', 'text'], ['company name', 'text'], ['company address', 'text'], ['other company details', 'text']]

 -- Table: maintenance contracts 
columns : [['maintenance contract id', 'number'], ['maintenance contract company id', 'number'], ['contract start date', 'time'], ['contract end date', 'time'], ['other contract details', 'text']]

 -- Table: parts 
columns : [['part id', 'number'], ['part name', 'text'], ['chargeable yn', 'text'], ['chargeable amount', 'text'], ['other part details', 'text']]

 -- Table: skills 
columns : [['skill id', 'number'], ['skill code', 'text'], ['skill description', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff name', 'text'], ['gender', 'text'], ['other staff details', 'text']]

 -- Table: assets 
columns : [['asset id', 'number'], ['maintenance contract id', 'number'], ['supplier company id', 'number'], ['asset details', 'text'], ['asset make', 'text'], ['asset model', 'text'], ['asset acquired date', 'time'], ['asset disposed date', 'time'], ['other asset details', 'text']]

 -- Table: asset parts 
columns : [['asset id', 'number'], ['part id', 'number']]

 -- Table: maintenance engineers 
columns : [['engineer id', 'number'], ['company id', 'number'], ['first name', 'text'], ['last name', 'text'], ['other details', 'text']]

 -- Table: engineer skills 
columns : [['engineer id', 'number'], ['skill id', 'number']]

 -- Table: fault log 
columns : [['fault log entry id', 'number'], ['asset id', 'number'], ['recorded by staff id', 'number'], ['fault log entry datetime', 'time'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: engineer visits 
columns : [['engineer visit id', 'number'], ['contact staff id', 'number'], ['engineer id', 'number'], ['fault log entry id', 'number'], ['fault status', 'text'], ['visit start datetime', 'time'], ['visit end datetime', 'time'], ['other visit details', 'text']]

 -- Table: part faults 
columns : [['part fault id', 'number'], ['part id', 'number'], ['fault short name', 'text'], ['fault description', 'text'], ['other fault details', 'text']]

 -- Table: fault log parts 
columns : [['fault log entry id', 'number'], ['part fault id', 'number'], ['fault status', 'text']]

 -- Table: skills required to fix 
columns : [['part fault id', 'number'], ['skill id', 'number']]

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



Question: How many engineer visits are required at most for a single fault log? List the number and the log entry id.
SQL: SELECT count(*) ,  T1.fault_log_entry_id FROM Fault_Log AS T1 JOIN Engineer_Visits AS T2 ON T1.fault_log_entry_id  =  T2.fault_log_entry_id GROUP BY T1.fault_log_entry_id ORDER BY count(*) DESC LIMIT 1