
CREATE TABLE "Actual_Order_Products" (
	actual_order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	FOREIGN KEY(actual_order_id) REFERENCES "Actual_Orders" (actual_order_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id)
)


CREATE TABLE "Actual_Orders" (
	actual_order_id INTEGER, 
	order_status_code VARCHAR(10) NOT NULL, 
	regular_order_id INTEGER NOT NULL, 
	actual_order_date DATETIME, 
	PRIMARY KEY (actual_order_id), 
	FOREIGN KEY(regular_order_id) REFERENCES "Regular_Orders" (regular_order_id)
)


CREATE TABLE "Addresses" (
	address_id INTEGER, 
	address_details VARCHAR(80), 
	city VARCHAR(50), 
	zip_postcode VARCHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	PRIMARY KEY (address_id)
)


CREATE TABLE "Customer_Addresses" (
	customer_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_from DATETIME NOT NULL, 
	address_type VARCHAR(10) NOT NULL, 
	date_to DATETIME, 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	payment_method VARCHAR(10) NOT NULL, 
	customer_name VARCHAR(80), 
	customer_phone VARCHAR(80), 
	customer_email VARCHAR(80), 
	date_became_customer DATETIME, 
	PRIMARY KEY (customer_id)
)


CREATE TABLE "Delivery_Route_Locations" (
	location_code VARCHAR(10), 
	route_id INTEGER NOT NULL, 
	location_address_id INTEGER NOT NULL, 
	location_name VARCHAR(50), 
	PRIMARY KEY (location_code), 
	FOREIGN KEY(route_id) REFERENCES "Delivery_Routes" (route_id), 
	FOREIGN KEY(location_address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Delivery_Routes" (
	route_id INTEGER, 
	route_name VARCHAR(50), 
	other_route_details VARCHAR(255), 
	PRIMARY KEY (route_id)
)


CREATE TABLE "Employees" (
	employee_id INTEGER, 
	employee_address_id INTEGER NOT NULL, 
	employee_name VARCHAR(80), 
	employee_phone VARCHAR(80), 
	PRIMARY KEY (employee_id), 
	FOREIGN KEY(employee_address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Order_Deliveries" (
	location_code VARCHAR(10) NOT NULL, 
	actual_order_id INTEGER NOT NULL, 
	delivery_status_code VARCHAR(10) NOT NULL, 
	driver_employee_id INTEGER NOT NULL, 
	truck_id INTEGER NOT NULL, 
	delivery_date DATETIME, 
	FOREIGN KEY(driver_employee_id) REFERENCES "Employees" (employee_id), 
	FOREIGN KEY(location_code) REFERENCES "Delivery_Route_Locations" (location_code), 
	FOREIGN KEY(actual_order_id) REFERENCES "Actual_Orders" (actual_order_id), 
	FOREIGN KEY(truck_id) REFERENCES "Trucks" (truck_id)
)


CREATE TABLE "Products" (
	product_id INTEGER, 
	product_name VARCHAR(20), 
	product_price DECIMAL(19, 4), 
	product_description VARCHAR(255), 
	PRIMARY KEY (product_id)
)


CREATE TABLE "Regular_Order_Products" (
	regular_order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	FOREIGN KEY(regular_order_id) REFERENCES "Regular_Orders" (regular_order_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id)
)


CREATE TABLE "Regular_Orders" (
	regular_order_id INTEGER, 
	distributer_id INTEGER NOT NULL, 
	PRIMARY KEY (regular_order_id), 
	FOREIGN KEY(distributer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Trucks" (
	truck_id INTEGER, 
	truck_licence_number VARCHAR(20), 
	truck_details VARCHAR(255), 
	PRIMARY KEY (truck_id)
)

CREATE TABLE "Actual_Order_Products" (
	actual_order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	FOREIGN KEY(actual_order_id) REFERENCES "Actual_Orders" (actual_order_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id)
)

/*
2 rows from Actual_Order_Products table:
actual_order_id	product_id
2	1
14	5
*/


CREATE TABLE "Actual_Orders" (
	actual_order_id INTEGER, 
	order_status_code VARCHAR(10) NOT NULL, 
	regular_order_id INTEGER NOT NULL, 
	actual_order_date DATETIME, 
	PRIMARY KEY (actual_order_id), 
	FOREIGN KEY(regular_order_id) REFERENCES "Regular_Orders" (regular_order_id)
)

/*
2 rows from Actual_Orders table:
actual_order_id	order_status_code	regular_order_id	actual_order_date
1	Success	8	2018-03-02 23:26:19
2	Cancel	15	2018-03-02 08:33:39
*/


CREATE TABLE "Addresses" (
	address_id INTEGER, 
	address_details VARCHAR(80), 
	city VARCHAR(50), 
	zip_postcode VARCHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	PRIMARY KEY (address_id)
)

/*
2 rows from Addresses table:
address_id	address_details	city	zip_postcode	state_province_county	country
1	92283 Lora Forges Suite 322	Mohrville	271	Nebraska	USA
2	17135 Jaida Fork Suite 798	East Brody	940	Colorado	USA
*/


CREATE TABLE "Customer_Addresses" (
	customer_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_from DATETIME NOT NULL, 
	address_type VARCHAR(10) NOT NULL, 
	date_to DATETIME, 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)

/*
2 rows from Customer_Addresses table:
customer_id	address_id	date_from	address_type	date_to
5	6	2016-09-06 19:23:46	House	2018-02-25 15:34:58
14	5	2016-12-21 03:49:54	House	2018-03-13 21:20:21
*/


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	payment_method VARCHAR(10) NOT NULL, 
	customer_name VARCHAR(80), 
	customer_phone VARCHAR(80), 
	customer_email VARCHAR(80), 
	date_became_customer DATETIME, 
	PRIMARY KEY (customer_id)
)

/*
2 rows from Customers table:
customer_id	payment_method	customer_name	customer_phone	customer_email	date_became_customer
1	Visa	Ron Emard	1-382-503-5179x53639	shaniya45@example.net	2011-04-25 22:20:35
2	MasterCard	Gabe Schroeder	1-728-537-4293x0885	alexandra91@example.net	2011-10-17 16:08:25
*/


CREATE TABLE "Delivery_Route_Locations" (
	location_code VARCHAR(10), 
	route_id INTEGER NOT NULL, 
	location_address_id INTEGER NOT NULL, 
	location_name VARCHAR(50), 
	PRIMARY KEY (location_code), 
	FOREIGN KEY(route_id) REFERENCES "Delivery_Routes" (route_id), 
	FOREIGN KEY(location_address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Delivery_Route_Locations table:
location_code	route_id	location_address_id	location_name
27 City Rd	11	5	Labadie-Crooks
30 Sam Rd	7	13	VonRueden, Schmeler and Fay
*/


CREATE TABLE "Delivery_Routes" (
	route_id INTEGER, 
	route_name VARCHAR(50), 
	other_route_details VARCHAR(255), 
	PRIMARY KEY (route_id)
)

/*
2 rows from Delivery_Routes table:
route_id	route_name	other_route_details
1	Torphy Ltd	16893 Wilderman Terrace
Port Lucasburgh, ND 55978-5550
2	Heidenreich Group	6534 Cheyenne Trace Suite 242
Koryburgh, PA 21391-9164
*/


CREATE TABLE "Employees" (
	employee_id INTEGER, 
	employee_address_id INTEGER NOT NULL, 
	employee_name VARCHAR(80), 
	employee_phone VARCHAR(80), 
	PRIMARY KEY (employee_id), 
	FOREIGN KEY(employee_address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Employees table:
employee_id	employee_address_id	employee_name	employee_phone
1	4	Kacie	716-650-2081
2	12	Dejuan	211.289.9042
*/


CREATE TABLE "Order_Deliveries" (
	location_code VARCHAR(10) NOT NULL, 
	actual_order_id INTEGER NOT NULL, 
	delivery_status_code VARCHAR(10) NOT NULL, 
	driver_employee_id INTEGER NOT NULL, 
	truck_id INTEGER NOT NULL, 
	delivery_date DATETIME, 
	FOREIGN KEY(driver_employee_id) REFERENCES "Employees" (employee_id), 
	FOREIGN KEY(location_code) REFERENCES "Delivery_Route_Locations" (location_code), 
	FOREIGN KEY(actual_order_id) REFERENCES "Actual_Orders" (actual_order_id), 
	FOREIGN KEY(truck_id) REFERENCES "Trucks" (truck_id)
)

/*
2 rows from Order_Deliveries table:
location_code	actual_order_id	delivery_status_code	driver_employee_id	truck_id	delivery_date
27 City Rd	11	Ready	6	11	2018-03-21 00:57:22
27 City Rd	1	On Road	4	10	2018-02-26 01:32:49
*/


CREATE TABLE "Products" (
	product_id INTEGER, 
	product_name VARCHAR(20), 
	product_price DECIMAL(19, 4), 
	product_description VARCHAR(255), 
	PRIMARY KEY (product_id)
)

/*
2 rows from Products table:
product_id	product_name	product_price	product_description
1	dvds	1322.7800	good condition
2	cloth	6402.0900	good condition
*/


CREATE TABLE "Regular_Order_Products" (
	regular_order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	FOREIGN KEY(regular_order_id) REFERENCES "Regular_Orders" (regular_order_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id)
)

/*
2 rows from Regular_Order_Products table:
regular_order_id	product_id
5	3
2	3
*/


CREATE TABLE "Regular_Orders" (
	regular_order_id INTEGER, 
	distributer_id INTEGER NOT NULL, 
	PRIMARY KEY (regular_order_id), 
	FOREIGN KEY(distributer_id) REFERENCES "Customers" (customer_id)
)

/*
2 rows from Regular_Orders table:
regular_order_id	distributer_id
1	12
2	15
*/


CREATE TABLE "Trucks" (
	truck_id INTEGER, 
	truck_licence_number VARCHAR(20), 
	truck_details VARCHAR(255), 
	PRIMARY KEY (truck_id)
)

/*
2 rows from Trucks table:
truck_id	truck_licence_number	truck_details
1	58110	Frida
2	33822	Randy
*/
Find the states which do not have any employee in their record.
SELECT state_province_county FROM addresses WHERE address_id NOT IN (SELECT employee_address_id FROM Employees)
[{'state_province_county': 'Colorado'}, {'state_province_county': 'Idaho'}, {'state_province_county': 'Maine'}, {'state_province_county': 'Washington'}]