Database: assets_maintenance

CREATE TABLE `Third_Party_Companies` (
`company_id` INTEGER PRIMARY KEY ,
`company_type` VARCHAR(5) NOT NULL,
`company_name` VARCHAR(255),
`company_address` VARCHAR(255),
`other_company_details` VARCHAR(255)
);
CREATE TABLE `Maintenance_Contracts` (
`maintenance_contract_id` INTEGER PRIMARY KEY,
`maintenance_contract_company_id` INTEGER NOT NULL,
`contract_start_date` DATETIME,
`contract_end_date` DATETIME,
`other_contract_details` VARCHAR(255),
FOREIGN KEY (`maintenance_contract_company_id` ) REFERENCES `Third_Party_Companies`(`company_id` )
);
CREATE TABLE `Parts` (
`part_id` INTEGER PRIMARY KEY,
`part_name` VARCHAR(255),
`chargeable_yn` VARCHAR(1),
`chargeable_amount` VARCHAR(20),
`other_part_details` VARCHAR(255)
);
CREATE TABLE `Skills` (
`skill_id` INTEGER PRIMARY KEY,
`skill_code` VARCHAR(20),
`skill_description` VARCHAR(255)
);
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_name` VARCHAR(255),
`gender` VARCHAR(1),
`other_staff_details` VARCHAR(255)
);
CREATE TABLE `Assets` (
`asset_id` INTEGER PRIMARY KEY,
`maintenance_contract_id` INTEGER NOT NULL,
`supplier_company_id` INTEGER NOT NULL,
`asset_details` VARCHAR(255),
`asset_make` VARCHAR(20),
`asset_model` VARCHAR(20),
`asset_acquired_date` DATETIME,
`asset_disposed_date` DATETIME,
`other_asset_details` VARCHAR(255),
FOREIGN KEY (`maintenance_contract_id` )
REFERENCES `Maintenance_Contracts`(`maintenance_contract_id` ),
FOREIGN KEY (`supplier_company_id` ) REFERENCES `Third_Party_Companies`(`company_id` )
);
CREATE TABLE `Asset_Parts` (
`asset_id` INTEGER NOT NULL,
`part_id` INTEGER NOT NULL,
FOREIGN KEY (`part_id` ) REFERENCES `Parts`(`part_id` ),
FOREIGN KEY (`asset_id` ) REFERENCES `Assets`(`asset_id` )
);
CREATE TABLE `Maintenance_Engineers` (
`engineer_id` INTEGER PRIMARY KEY,
`company_id` INTEGER NOT NULL,
`first_name` VARCHAR(50),
`last_name` VARCHAR(50),
`other_details` VARCHAR(255),
FOREIGN KEY (`company_id` ) REFERENCES `Third_Party_Companies`(`company_id` )
);
CREATE TABLE `Engineer_Skills` (
`engineer_id` INTEGER NOT NULL,
`skill_id` INTEGER NOT NULL,
FOREIGN KEY (`engineer_id` ) REFERENCES `Maintenance_Engineers`(`engineer_id` ),
FOREIGN KEY (`skill_id` ) REFERENCES `Skills`(`skill_id` )
);
CREATE TABLE `Fault_Log` (
`fault_log_entry_id` INTEGER PRIMARY KEY,
`asset_id` INTEGER NOT NULL,
`recorded_by_staff_id` INTEGER NOT NULL,
`fault_log_entry_datetime` DATETIME,
`fault_description` VARCHAR(255),
`other_fault_details` VARCHAR(255),
FOREIGN KEY (`asset_id` ) REFERENCES `Assets`(`asset_id` ),
FOREIGN KEY (`recorded_by_staff_id` ) REFERENCES `Staff`(`staff_id` )
);
CREATE TABLE `Engineer_Visits` (
`engineer_visit_id` INTEGER PRIMARY KEY,
`contact_staff_id` INTEGER,
`engineer_id` INTEGER NOT NULL,
`fault_log_entry_id` INTEGER NOT NULL,
`fault_status` VARCHAR(10) NOT NULL,
`visit_start_datetime` DATETIME,
`visit_end_datetime` DATETIME,
`other_visit_details` VARCHAR(255),
FOREIGN KEY (`fault_log_entry_id` ) REFERENCES `Fault_Log`(`fault_log_entry_id` ),
FOREIGN KEY (`engineer_id` ) REFERENCES `Maintenance_Engineers`(`engineer_id` ),
FOREIGN KEY (`contact_staff_id` ) REFERENCES `Staff`(`staff_id` )
);
CREATE TABLE `Part_Faults` (
`part_fault_id` INTEGER PRIMARY KEY,
`part_id` INTEGER NOT NULL,
`fault_short_name` VARCHAR(20),
`fault_description` VARCHAR(255),
`other_fault_details` VARCHAR(255),
FOREIGN KEY (`part_id` ) REFERENCES `Parts`(`part_id` )
);
CREATE TABLE `Fault_Log_Parts` (
`fault_log_entry_id` INTEGER NOT NULL,
`part_fault_id` INTEGER NOT NULL,
`fault_status` VARCHAR(10) NOT NULL,
FOREIGN KEY (`part_fault_id` ) REFERENCES `Part_Faults`(`part_fault_id` ),
FOREIGN KEY (`fault_log_entry_id` ) REFERENCES `Fault_Log`(`fault_log_entry_id` )
);
CREATE TABLE `Skills_Required_To_Fix` (
`part_fault_id` INTEGER NOT NULL,
`skill_id` INTEGER NOT NULL,
FOREIGN KEY (`part_fault_id` ) REFERENCES `Part_Faults`(`part_fault_id` ),
FOREIGN KEY (`skill_id` ) REFERENCES `Skills`(`skill_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

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)