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 engineers have never visited to maintain the assets? List the engineer first name and last name.
SQL: SELECT first_name ,  last_name FROM Maintenance_Engineers WHERE engineer_id NOT IN (SELECT engineer_id FROM Engineer_Visits)

Question: List the asset id, details, make and model for every asset.
SQL: SELECT asset_id ,  asset_details ,  asset_make ,  asset_model FROM Assets

Question: When was the first asset acquired?
SQL: SELECT asset_acquired_date FROM Assets ORDER BY asset_acquired_date ASC LIMIT 1

Question: Which part fault requires the most number of skills to fix? List part id and name.
SQL: 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

Question: Which kind of part has the least number of faults? List the part name.
SQL: 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

Question: Among those engineers who have visited, which engineer makes the least number of visits? List 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 ON T1.engineer_id  =  T2.engineer_id GROUP BY T1.engineer_id ORDER BY count(*) ASC LIMIT 1

Question: Which staff have contacted which engineers? List the staff name and the engineer first name and last name.
SQL: SELECT T1.staff_name ,  T3.first_name ,  T3.last_name FROM Staff AS T1 JOIN Engineer_Visits AS T2 ON T1.staff_id  =  T2.contact_staff_id JOIN Maintenance_Engineers AS T3 ON T2.engineer_id  =  T3.engineer_id

Question: Which fault log included the most number of faulty parts? List the fault log id, description and record time.
SQL: 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

Question: Which skill is used in fixing the most number of faults? List the skill id and description.
SQL: 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

Question: What are all the distinct asset models?
SQL: SELECT DISTINCT asset_model FROM Assets

Question: List the all the assets make, model, details by the disposed date ascendingly.
SQL: SELECT asset_make ,  asset_model ,  asset_details FROM Assets ORDER BY asset_disposed_date ASC

Question: Which part has the least chargeable amount? List the part id and amount.
SQL: SELECT part_id ,  chargeable_amount FROM Parts ORDER BY chargeable_amount ASC LIMIT 1

Question: Which company started the earliest the maintenance contract? Show the company name.
SQL: SELECT T1.company_name FROM Third_Party_Companies AS T1 JOIN Maintenance_Contracts AS T2 ON T1.company_id  =  T2.maintenance_contract_company_id ORDER BY T2.contract_start_date ASC LIMIT 1

Question: What is the description of the type of the company who concluded its contracts most recently?
SQL: SELECT T1.company_name FROM Third_Party_Companies AS T1 JOIN Maintenance_Contracts AS T2 ON T1.company_id  =  T2.maintenance_contract_company_id JOIN Ref_Company_Types AS T3 ON T1.company_type_code  =  T3.company_type_code ORDER BY T2.contract_end_date DESC LIMIT 1

Question: Which gender makes up the majority of the staff?
SQL: SELECT gender FROM staff GROUP BY gender ORDER BY count(*) DESC LIMIT 1

Question: How many engineers did each staff contact? List both the contact staff name and number of engineers contacted.
SQL: SELECT T1.staff_name ,  count(*) FROM Staff AS T1 JOIN Engineer_Visits AS T2 ON T1.staff_id  =  T2.contact_staff_id GROUP BY T1.staff_name

Question: Which assets did not incur any fault log? List the asset model.
SQL: SELECT asset_model FROM Assets WHERE asset_id NOT IN (SELECT asset_id FROM Fault_Log)