
CREATE TABLE "Asset_Parts" (
	asset_id INTEGER NOT NULL, 
	part_id INTEGER NOT NULL, 
	FOREIGN KEY(asset_id) REFERENCES "Assets" (asset_id), 
	FOREIGN KEY(part_id) REFERENCES "Parts" (part_id)
)


CREATE TABLE "Assets" (
	asset_id INTEGER, 
	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), 
	PRIMARY KEY (asset_id), 
	FOREIGN KEY(supplier_company_id) REFERENCES "Third_Party_Companies" (company_id), 
	FOREIGN KEY(maintenance_contract_id) REFERENCES "Maintenance_Contracts" (maintenance_contract_id)
)


CREATE TABLE "Engineer_Skills" (
	engineer_id INTEGER NOT NULL, 
	skill_id INTEGER NOT NULL, 
	FOREIGN KEY(skill_id) REFERENCES "Skills" (skill_id), 
	FOREIGN KEY(engineer_id) REFERENCES "Maintenance_Engineers" (engineer_id)
)


CREATE TABLE "Engineer_Visits" (
	engineer_visit_id INTEGER, 
	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), 
	PRIMARY KEY (engineer_visit_id), 
	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 "Fault_Log" (
	fault_log_entry_id INTEGER, 
	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), 
	PRIMARY KEY (fault_log_entry_id), 
	FOREIGN KEY(recorded_by_staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(asset_id) REFERENCES "Assets" (asset_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(fault_log_entry_id) REFERENCES "Fault_Log" (fault_log_entry_id), 
	FOREIGN KEY(part_fault_id) REFERENCES "Part_Faults" (part_fault_id)
)


CREATE TABLE "Maintenance_Contracts" (
	maintenance_contract_id INTEGER, 
	maintenance_contract_company_id INTEGER NOT NULL, 
	contract_start_date DATETIME, 
	contract_end_date DATETIME, 
	other_contract_details VARCHAR(255), 
	PRIMARY KEY (maintenance_contract_id), 
	FOREIGN KEY(maintenance_contract_company_id) REFERENCES "Third_Party_Companies" (company_id)
)


CREATE TABLE "Maintenance_Engineers" (
	engineer_id INTEGER, 
	company_id INTEGER NOT NULL, 
	first_name VARCHAR(50), 
	last_name VARCHAR(50), 
	other_details VARCHAR(255), 
	PRIMARY KEY (engineer_id), 
	FOREIGN KEY(company_id) REFERENCES "Third_Party_Companies" (company_id)
)


CREATE TABLE "Part_Faults" (
	part_fault_id INTEGER, 
	part_id INTEGER NOT NULL, 
	fault_short_name VARCHAR(20), 
	fault_description VARCHAR(255), 
	other_fault_details VARCHAR(255), 
	PRIMARY KEY (part_fault_id), 
	FOREIGN KEY(part_id) REFERENCES "Parts" (part_id)
)


CREATE TABLE "Parts" (
	part_id INTEGER, 
	part_name VARCHAR(255), 
	chargeable_yn VARCHAR(1), 
	chargeable_amount VARCHAR(20), 
	other_part_details VARCHAR(255), 
	PRIMARY KEY (part_id)
)


CREATE TABLE "Skills" (
	skill_id INTEGER, 
	skill_code VARCHAR(20), 
	skill_description VARCHAR(255), 
	PRIMARY KEY (skill_id)
)


CREATE TABLE "Skills_Required_To_Fix" (
	part_fault_id INTEGER NOT NULL, 
	skill_id INTEGER NOT NULL, 
	FOREIGN KEY(skill_id) REFERENCES "Skills" (skill_id), 
	FOREIGN KEY(part_fault_id) REFERENCES "Part_Faults" (part_fault_id)
)


CREATE TABLE "Staff" (
	staff_id INTEGER, 
	staff_name VARCHAR(255), 
	gender VARCHAR(1), 
	other_staff_details VARCHAR(255), 
	PRIMARY KEY (staff_id)
)


CREATE TABLE "Third_Party_Companies" (
	company_id INTEGER, 
	company_type VARCHAR(5) NOT NULL, 
	company_name VARCHAR(255), 
	company_address VARCHAR(255), 
	other_company_details VARCHAR(255), 
	PRIMARY KEY (company_id)
)

CREATE TABLE "Asset_Parts" (
	asset_id INTEGER NOT NULL, 
	part_id INTEGER NOT NULL, 
	FOREIGN KEY(asset_id) REFERENCES "Assets" (asset_id), 
	FOREIGN KEY(part_id) REFERENCES "Parts" (part_id)
)

/*
2 rows from Asset_Parts table:
asset_id	part_id
5	3
3	3
*/


CREATE TABLE "Assets" (
	asset_id INTEGER, 
	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), 
	PRIMARY KEY (asset_id), 
	FOREIGN KEY(supplier_company_id) REFERENCES "Third_Party_Companies" (company_id), 
	FOREIGN KEY(maintenance_contract_id) REFERENCES "Maintenance_Contracts" (maintenance_contract_id)
)

/*
2 rows from Assets table:
asset_id	maintenance_contract_id	supplier_company_id	asset_details	asset_make	asset_model	asset_acquired_date	asset_disposed_date	other_asset_details
1	2	2	dell laptop1	PT	58 ub	2017-12-25 00:31:27	2018-03-14 10:50:00	None
2	14	1	dell laptop2	IN	35 xt	2018-01-27 00:59:46	2018-03-20 04:24:09	None
*/


CREATE TABLE "Engineer_Skills" (
	engineer_id INTEGER NOT NULL, 
	skill_id INTEGER NOT NULL, 
	FOREIGN KEY(skill_id) REFERENCES "Skills" (skill_id), 
	FOREIGN KEY(engineer_id) REFERENCES "Maintenance_Engineers" (engineer_id)
)

/*
2 rows from Engineer_Skills table:
engineer_id	skill_id
10	2
10	1
*/


CREATE TABLE "Engineer_Visits" (
	engineer_visit_id INTEGER, 
	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), 
	PRIMARY KEY (engineer_visit_id), 
	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)
)

/*
2 rows from Engineer_Visits table:
engineer_visit_id	contact_staff_id	engineer_id	fault_log_entry_id	fault_status	visit_start_datetime	visit_end_datetime	other_visit_details
1	8	8	13	Waiting	1978-10-12 23:14:40	1988-01-07 06:41:51	None
2	7	15	13	Return	1980-05-02 23:31:18	1990-08-30 22:44:16	None
*/


CREATE TABLE "Fault_Log" (
	fault_log_entry_id INTEGER, 
	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), 
	PRIMARY KEY (fault_log_entry_id), 
	FOREIGN KEY(recorded_by_staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(asset_id) REFERENCES "Assets" (asset_id)
)

/*
2 rows from Fault_Log table:
fault_log_entry_id	asset_id	recorded_by_staff_id	fault_log_entry_datetime	fault_description	other_fault_details
1	3	14	2018-03-21 04:25:00	system error	None
2	7	4	2018-03-13 09:43:05	system error	None
*/


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(fault_log_entry_id) REFERENCES "Fault_Log" (fault_log_entry_id), 
	FOREIGN KEY(part_fault_id) REFERENCES "Part_Faults" (part_fault_id)
)

/*
2 rows from Fault_Log_Parts table:
fault_log_entry_id	part_fault_id	fault_status
12	3	Reported
7	4	Reported
*/


CREATE TABLE "Maintenance_Contracts" (
	maintenance_contract_id INTEGER, 
	maintenance_contract_company_id INTEGER NOT NULL, 
	contract_start_date DATETIME, 
	contract_end_date DATETIME, 
	other_contract_details VARCHAR(255), 
	PRIMARY KEY (maintenance_contract_id), 
	FOREIGN KEY(maintenance_contract_company_id) REFERENCES "Third_Party_Companies" (company_id)
)

/*
2 rows from Maintenance_Contracts table:
maintenance_contract_id	maintenance_contract_company_id	contract_start_date	contract_end_date	other_contract_details
1	15	2017-09-13 11:51:29	2018-03-16 21:21:50	None
2	9	2017-12-18 11:43:16	2018-03-22 06:00:37	None
*/


CREATE TABLE "Maintenance_Engineers" (
	engineer_id INTEGER, 
	company_id INTEGER NOT NULL, 
	first_name VARCHAR(50), 
	last_name VARCHAR(50), 
	other_details VARCHAR(255), 
	PRIMARY KEY (engineer_id), 
	FOREIGN KEY(company_id) REFERENCES "Third_Party_Companies" (company_id)
)

/*
2 rows from Maintenance_Engineers table:
engineer_id	company_id	first_name	last_name	other_details
1	14	Etha	Reinger	Skilled
2	2	Clemens	Orn	None
*/


CREATE TABLE "Part_Faults" (
	part_fault_id INTEGER, 
	part_id INTEGER NOT NULL, 
	fault_short_name VARCHAR(20), 
	fault_description VARCHAR(255), 
	other_fault_details VARCHAR(255), 
	PRIMARY KEY (part_fault_id), 
	FOREIGN KEY(part_id) REFERENCES "Parts" (part_id)
)

/*
2 rows from Part_Faults table:
part_fault_id	part_id	fault_short_name	fault_description	other_fault_details
1	1	PW	Package Wrong	None
2	1	PW	Package Wrong	None
*/


CREATE TABLE "Parts" (
	part_id INTEGER, 
	part_name VARCHAR(255), 
	chargeable_yn VARCHAR(1), 
	chargeable_amount VARCHAR(20), 
	other_part_details VARCHAR(255), 
	PRIMARY KEY (part_id)
)

/*
2 rows from Parts table:
part_id	part_name	chargeable_yn	chargeable_amount	other_part_details
1	top	0	4	None
2	middle	1	9	None
*/


CREATE TABLE "Skills" (
	skill_id INTEGER, 
	skill_code VARCHAR(20), 
	skill_description VARCHAR(255), 
	PRIMARY KEY (skill_id)
)

/*
2 rows from Skills table:
skill_id	skill_code	skill_description
1	ELEC	Electrical
2	MECH	Mechanical
*/


CREATE TABLE "Skills_Required_To_Fix" (
	part_fault_id INTEGER NOT NULL, 
	skill_id INTEGER NOT NULL, 
	FOREIGN KEY(skill_id) REFERENCES "Skills" (skill_id), 
	FOREIGN KEY(part_fault_id) REFERENCES "Part_Faults" (part_fault_id)
)

/*
2 rows from Skills_Required_To_Fix table:
part_fault_id	skill_id
3	2
12	3
*/


CREATE TABLE "Staff" (
	staff_id INTEGER, 
	staff_name VARCHAR(255), 
	gender VARCHAR(1), 
	other_staff_details VARCHAR(255), 
	PRIMARY KEY (staff_id)
)

/*
2 rows from Staff table:
staff_id	staff_name	gender	other_staff_details
1	Audreanne	1	Manager
2	Berneice	1	Manager
*/


CREATE TABLE "Third_Party_Companies" (
	company_id INTEGER, 
	company_type VARCHAR(5) NOT NULL, 
	company_name VARCHAR(255), 
	company_address VARCHAR(255), 
	other_company_details VARCHAR(255), 
	PRIMARY KEY (company_id)
)

/*
2 rows from Third_Party_Companies table:
company_id	company_type	company_name	company_address	other_company_details
1	Maintenance Contractor	Langworth-Funk	615 Jacobs Mews	Uganda
2	Maintenance Contractor	McDermott Group	873 Conrad Creek Apt. 286	China
*/
What are all the distinct last names of all the engineers?
SELECT DISTINCT last_name FROM Maintenance_Engineers
[{'last_name': 'Reinger'}, {'last_name': 'Orn'}, {'last_name': 'Hauck'}, {'last_name': 'Morar'}, {'last_name': 'Grady'}, {'last_name': 'Adams'}, {'last_name': 'Harvey'}, {'last_name': 'Hirthe'}, {'last_name': 'Stamm'}, {'last_name': 'Price'}, {'last_name': 'Kunde'}, {'last_name': 'Simonis'}, {'last_name': 'Hahn'}, {'last_name': 'Goldner'}, {'last_name': 'Streich'}]