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
How many assets does each maintenance contract contain? List the number and the contract id.
assistant
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
human
Which assets have 2 parts and have less than 2 fault logs? List the asset id and detail.
assistant
SELECT T1.asset_id , T1.asset_details FROM Assets AS T1 JOIN Asset_Parts AS T2 ON T1.asset_id = T2.asset_id GROUP BY T1.asset_id HAVING count(*) = 2 INTERSECT SELECT T1.asset_id , T1.asset_details FROM Assets AS T1 JOIN Fault_Log AS T2 ON T1.asset_id = T2.asset_id GROUP BY T1.asset_id HAVING count(*) < 2
human
What are all the distinct asset models?
assistant
SELECT DISTINCT asset_model FROM Assets
human
How many assets can each parts be used in? List the part name and the number.
assistant
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
human
List the all the assets make, model, details by the disposed date ascendingly.
assistant
SELECT asset_make , asset_model , asset_details FROM Assets ORDER BY asset_disposed_date ASC
human
List the asset id, details, make and model for every asset.
assistant
SELECT asset_id , asset_details , asset_make , asset_model FROM Assets ;