CREATE TABLE Third_Party_Companies (
    company_id NUMBER PRIMARY KEY,
    company_type TEXT,
    company_name TEXT,
    company_address TEXT,
    other_company_details TEXT
);
CREATE TABLE Maintenance_Contracts (
    maintenance_contract_id NUMBER PRIMARY KEY,
    maintenance_contract_company_id NUMBER,
    contract_start_date TIME,
    contract_end_date TIME,
    other_contract_details TEXT,
    FOREIGN KEY ("maintenance_contract_company_id") REFERENCES Third_Party_Companies("company_id")
);
CREATE TABLE Parts (
    part_id NUMBER PRIMARY KEY,
    part_name TEXT,
    chargeable_yn TEXT,
    chargeable_amount TEXT,
    other_part_details TEXT
);
CREATE TABLE Skills (
    skill_id NUMBER PRIMARY KEY,
    skill_code TEXT,
    skill_description TEXT
);
CREATE TABLE Staff (
    staff_id NUMBER PRIMARY KEY,
    staff_name TEXT,
    gender TEXT,
    other_staff_details TEXT
);
CREATE TABLE Assets (
    asset_id NUMBER PRIMARY KEY,
    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,
    FOREIGN KEY ("supplier_company_id") REFERENCES Third_Party_Companies("company_id"),
    FOREIGN KEY ("maintenance_contract_id") REFERENCES Maintenance_Contracts("maintenance_contract_id")
);
CREATE TABLE Asset_Parts (
    asset_id NUMBER,
    part_id NUMBER,
    FOREIGN KEY ("asset_id") REFERENCES Assets("asset_id"),
    FOREIGN KEY ("part_id") REFERENCES Parts("part_id")
);
CREATE TABLE Maintenance_Engineers (
    engineer_id NUMBER PRIMARY KEY,
    company_id NUMBER,
    first_name TEXT,
    last_name TEXT,
    other_details TEXT,
    FOREIGN KEY ("company_id") REFERENCES Third_Party_Companies("company_id")
);
CREATE TABLE Engineer_Skills (
    engineer_id NUMBER,
    skill_id NUMBER,
    FOREIGN KEY ("skill_id") REFERENCES Skills("skill_id"),
    FOREIGN KEY ("engineer_id") REFERENCES Maintenance_Engineers("engineer_id")
);
CREATE TABLE Fault_Log (
    fault_log_entry_id NUMBER PRIMARY KEY,
    asset_id NUMBER,
    recorded_by_staff_id NUMBER,
    fault_log_entry_datetime TIME,
    fault_description TEXT,
    other_fault_details TEXT,
    FOREIGN KEY ("recorded_by_staff_id") REFERENCES Staff("staff_id"),
    FOREIGN KEY ("asset_id") REFERENCES Assets("asset_id")
);
CREATE TABLE Engineer_Visits (
    engineer_visit_id NUMBER PRIMARY KEY,
    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,
    FOREIGN KEY ("contact_staff_id") REFERENCES Staff("staff_id"),
    FOREIGN KEY ("engineer_id") REFERENCES Maintenance_Engineers("engineer_id"),
    FOREIGN KEY ("fault_log_entry_id") REFERENCES Fault_Log("fault_log_entry_id")
);
CREATE TABLE Part_Faults (
    part_fault_id NUMBER PRIMARY KEY,
    part_id NUMBER,
    fault_short_name TEXT,
    fault_description TEXT,
    other_fault_details TEXT,
    FOREIGN KEY ("part_id") REFERENCES Parts("part_id")
);
CREATE TABLE Fault_Log_Parts (
    fault_log_entry_id NUMBER,
    part_fault_id NUMBER,
    fault_status TEXT,
    FOREIGN KEY ("fault_log_entry_id") REFERENCES Fault_Log("fault_log_entry_id"),
    FOREIGN KEY ("part_fault_id") REFERENCES Part_Faults("part_fault_id")
);
CREATE TABLE Skills_Required_To_Fix (
    part_fault_id NUMBER,
    skill_id NUMBER,
    FOREIGN KEY ("skill_id") REFERENCES Skills("skill_id"),
    FOREIGN KEY ("part_fault_id") REFERENCES Part_Faults("part_fault_id")
);

Question: Which assets have 2 parts and have less than 2 fault logs? List the asset id and detail.
SQL: 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

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

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

Question: Which third party companies have at least 2 maintenance engineers or have at least 2 maintenance contracts? List the company id and name.
SQL: SELECT T1.company_id ,  T1.company_name FROM Third_Party_Companies AS T1 JOIN Maintenance_Engineers AS T2 ON T1.company_id  =  T2.company_id GROUP BY T1.company_id HAVING count(*)  >=  2 UNION SELECT T3.company_id ,  T3.company_name FROM Third_Party_Companies AS T3 JOIN Maintenance_Contracts AS T4 ON T3.company_id  =  T4.maintenance_contract_company_id GROUP BY T3.company_id HAVING count(*)  >=  2

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

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

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

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

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

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

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

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

Question: What are all the distinct last names of all the engineers?
SQL: SELECT DISTINCT last_name FROM Maintenance_Engineers

Question: How many fault status codes are recorded in the fault log parts table?
SQL: SELECT DISTINCT fault_status FROM Fault_Log_Parts