human
Schema:
Third_Party_Companies (company_id, company_type, company_name, company_address, other_company_details)
Maintenance_Contracts (maintenance_contract_id, maintenance_contract_company_id, contract_start_date, contract_end_date, other_contract_details)
Parts (part_id, part_name, chargeable_yn, chargeable_amount, other_part_details)
Skills (skill_id, skill_code, skill_description)
Staff (staff_id, staff_name, gender, other_staff_details)
Assets (asset_id, maintenance_contract_id, supplier_company_id, asset_details, asset_make, asset_model, asset_acquired_date, asset_disposed_date, other_asset_details)
Asset_Parts (asset_id, part_id)
Maintenance_Engineers (engineer_id, company_id, first_name, last_name, other_details)
Engineer_Skills (engineer_id, skill_id)
Fault_Log (fault_log_entry_id, asset_id, recorded_by_staff_id, fault_log_entry_datetime, fault_description, other_fault_details)
Engineer_Visits (engineer_visit_id, contact_staff_id, engineer_id, fault_log_entry_id, fault_status, visit_start_datetime, visit_end_datetime, other_visit_details)
Part_Faults (part_fault_id, part_id, fault_short_name, fault_description, other_fault_details)
Fault_Log_Parts (fault_log_entry_id, part_fault_id, fault_status)
Skills_Required_To_Fix (part_fault_id, skill_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Which fault log included the most number of faulty parts? List the fault log id, description and record time.
assistant
SELECT T1.fault_log_entry_id , T1.fault_description , T1.fault_log_entry_datetime FROM Fault_Log AS T1 JOIN Fault_Log_Parts 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
human
Which skill is used in fixing the most number of faults? List the skill id and description.
assistant
SELECT T1.skill_id , T1.skill_description FROM Skills AS T1 JOIN Skills_Required_To_Fix AS T2 ON T1.skill_id = T2.skill_id GROUP BY T1.skill_id ORDER BY count(*) DESC LIMIT 1
human
Which kind of part has the least number of faults? List the part name.
assistant
SELECT T1.part_name FROM Parts AS T1 JOIN Part_Faults AS T2 ON T1.part_id = T2.part_id GROUP BY T1.part_name ORDER BY count(*) ASC LIMIT 1
human
Which parts have more than 2 faults? Show the part name and id.
assistant
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
human
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.
assistant
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
human
Which part fault requires the most number of skills to fix? List part id and name.
assistant
SELECT T1.part_id , T1.part_name FROM Parts AS T1 JOIN Part_Faults AS T2 ON T1.part_id = T2.part_id JOIN Skills_Required_To_Fix AS T3 ON T2.part_fault_id = T3.part_fault_id GROUP BY T1.part_id ORDER BY COUNT( * ) DESC LIMIT 1 ;