
CREATE TABLE "Addresses" (
	address_id INTEGER, 
	address_details VARCHAR(255), 
	PRIMARY KEY (address_id)
)


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


CREATE TABLE "Customer_Orders" (
	order_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	order_status_code VARCHAR(10) NOT NULL, 
	order_date DATETIME NOT NULL, 
	PRIMARY KEY (order_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	payment_method_code VARCHAR(10) NOT NULL, 
	customer_code VARCHAR(20), 
	customer_name VARCHAR(80), 
	customer_address VARCHAR(255), 
	customer_phone VARCHAR(80), 
	customer_email VARCHAR(80), 
	PRIMARY KEY (customer_id)
)


CREATE TABLE "Department_Store_Chain" (
	dept_store_chain_id INTEGER, 
	dept_store_chain_name VARCHAR(80), 
	PRIMARY KEY (dept_store_chain_id)
)


CREATE TABLE "Department_Stores" (
	dept_store_id INTEGER, 
	dept_store_chain_id INTEGER, 
	store_name VARCHAR(80), 
	store_address VARCHAR(255), 
	store_phone VARCHAR(80), 
	store_email VARCHAR(80), 
	PRIMARY KEY (dept_store_id), 
	FOREIGN KEY(dept_store_chain_id) REFERENCES "Department_Store_Chain" (dept_store_chain_id)
)


CREATE TABLE "Departments" (
	department_id INTEGER, 
	dept_store_id INTEGER NOT NULL, 
	department_name VARCHAR(80), 
	PRIMARY KEY (department_id), 
	FOREIGN KEY(dept_store_id) REFERENCES "Department_Stores" (dept_store_id)
)


CREATE TABLE "Order_Items" (
	order_item_id INTEGER, 
	order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	PRIMARY KEY (order_item_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id), 
	FOREIGN KEY(order_id) REFERENCES "Customer_Orders" (order_id)
)


CREATE TABLE "Product_Suppliers" (
	product_id INTEGER NOT NULL, 
	supplier_id INTEGER NOT NULL, 
	date_supplied_from DATETIME NOT NULL, 
	date_supplied_to DATETIME, 
	total_amount_purchased VARCHAR(80), 
	total_value_purchased DECIMAL(19, 4), 
	PRIMARY KEY (product_id, supplier_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id), 
	FOREIGN KEY(supplier_id) REFERENCES "Suppliers" (supplier_id)
)


CREATE TABLE "Products" (
	product_id INTEGER, 
	product_type_code VARCHAR(10) NOT NULL, 
	product_name VARCHAR(80), 
	product_price DECIMAL(19, 4), 
	PRIMARY KEY (product_id)
)


CREATE TABLE "Staff" (
	staff_id INTEGER, 
	staff_gender VARCHAR(1), 
	staff_name VARCHAR(80), 
	PRIMARY KEY (staff_id)
)


CREATE TABLE "Staff_Department_Assignments" (
	staff_id INTEGER NOT NULL, 
	department_id INTEGER NOT NULL, 
	date_assigned_from DATETIME NOT NULL, 
	job_title_code VARCHAR(10) NOT NULL, 
	date_assigned_to DATETIME, 
	PRIMARY KEY (staff_id, department_id), 
	FOREIGN KEY(staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(department_id) REFERENCES "Departments" (department_id)
)


CREATE TABLE "Supplier_Addresses" (
	supplier_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_from DATETIME NOT NULL, 
	date_to DATETIME, 
	PRIMARY KEY (supplier_id, address_id), 
	FOREIGN KEY(supplier_id) REFERENCES "Suppliers" (supplier_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Suppliers" (
	supplier_id INTEGER, 
	supplier_name VARCHAR(80), 
	supplier_phone VARCHAR(80), 
	PRIMARY KEY (supplier_id)
)

CREATE TABLE "Addresses" (
	address_id INTEGER, 
	address_details VARCHAR(255), 
	PRIMARY KEY (address_id)
)

/*
2 rows from Addresses table:
address_id	address_details
1	28481 Crist Circle
East Burdettestad, IA 21232
2	0292 Mitchel Pike
Port Abefurt, IA 84402-4249
*/


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

/*
2 rows from Customer_Addresses table:
customer_id	address_id	date_from	date_to
2	9	2017-12-11 05:00:22	2018-03-20 20:52:34
1	6	2017-10-07 23:00:26	2018-02-28 14:53:52
*/


CREATE TABLE "Customer_Orders" (
	order_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	order_status_code VARCHAR(10) NOT NULL, 
	order_date DATETIME NOT NULL, 
	PRIMARY KEY (order_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)

/*
2 rows from Customer_Orders table:
order_id	customer_id	order_status_code	order_date
1	12	Completed	2018-02-10 15:44:48
2	4	New	2018-01-31 17:49:18
*/


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	payment_method_code VARCHAR(10) NOT NULL, 
	customer_code VARCHAR(20), 
	customer_name VARCHAR(80), 
	customer_address VARCHAR(255), 
	customer_phone VARCHAR(80), 
	customer_email VARCHAR(80), 
	PRIMARY KEY (customer_id)
)

/*
2 rows from Customers table:
customer_id	payment_method_code	customer_code	customer_name	customer_address	customer_phone	customer_email
1	Credit Card	401	Ahmed	75099 Tremblay Port Apt. 163
South Norrisland, SC 80546	254-072-4068x33935	margarett.vonrueden@example.com
2	Credit Card	665	Chauncey	8408 Lindsay Court
East Dasiabury, IL 72656-3552	+41(8)1897032009	stiedemann.sigrid@example.com
*/


CREATE TABLE "Department_Store_Chain" (
	dept_store_chain_id INTEGER, 
	dept_store_chain_name VARCHAR(80), 
	PRIMARY KEY (dept_store_chain_id)
)

/*
2 rows from Department_Store_Chain table:
dept_store_chain_id	dept_store_chain_name
1	South
2	West
*/


CREATE TABLE "Department_Stores" (
	dept_store_id INTEGER, 
	dept_store_chain_id INTEGER, 
	store_name VARCHAR(80), 
	store_address VARCHAR(255), 
	store_phone VARCHAR(80), 
	store_email VARCHAR(80), 
	PRIMARY KEY (dept_store_id), 
	FOREIGN KEY(dept_store_chain_id) REFERENCES "Department_Store_Chain" (dept_store_chain_id)
)

/*
2 rows from Department_Stores table:
dept_store_id	dept_store_chain_id	store_name	store_address	store_phone	store_email
1	1	store_name	01290 Jeremie Parkway Suite 753
North Arielle, MS 51249	(948)944-5099x2027	bmaggio@example.com
2	3	store_name	082 Purdy Expressway
O'Connellshire, IL 31732	877-917-5029	larissa10@example.org
*/


CREATE TABLE "Departments" (
	department_id INTEGER, 
	dept_store_id INTEGER NOT NULL, 
	department_name VARCHAR(80), 
	PRIMARY KEY (department_id), 
	FOREIGN KEY(dept_store_id) REFERENCES "Department_Stores" (dept_store_id)
)

/*
2 rows from Departments table:
department_id	dept_store_id	department_name
1	5	human resource
2	11	purchasing
*/


CREATE TABLE "Order_Items" (
	order_item_id INTEGER, 
	order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	PRIMARY KEY (order_item_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id), 
	FOREIGN KEY(order_id) REFERENCES "Customer_Orders" (order_id)
)

/*
2 rows from Order_Items table:
order_item_id	order_id	product_id
1	9	7
2	1	3
*/


CREATE TABLE "Product_Suppliers" (
	product_id INTEGER NOT NULL, 
	supplier_id INTEGER NOT NULL, 
	date_supplied_from DATETIME NOT NULL, 
	date_supplied_to DATETIME, 
	total_amount_purchased VARCHAR(80), 
	total_value_purchased DECIMAL(19, 4), 
	PRIMARY KEY (product_id, supplier_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id), 
	FOREIGN KEY(supplier_id) REFERENCES "Suppliers" (supplier_id)
)

/*
2 rows from Product_Suppliers table:
product_id	supplier_id	date_supplied_from	date_supplied_to	total_amount_purchased	total_value_purchased
4	3	2017-06-19 00:49:05	2018-03-24 19:29:18	89366.05	36014.6000
8	4	2017-07-02 00:35:12	2018-03-25 07:30:49	25085.57	36274.5600
*/


CREATE TABLE "Products" (
	product_id INTEGER, 
	product_type_code VARCHAR(10) NOT NULL, 
	product_name VARCHAR(80), 
	product_price DECIMAL(19, 4), 
	PRIMARY KEY (product_id)
)

/*
2 rows from Products table:
product_id	product_type_code	product_name	product_price
1	Clothes	red jeans	734.7300
2	Clothes	yellow jeans	687.2300
*/


CREATE TABLE "Staff" (
	staff_id INTEGER, 
	staff_gender VARCHAR(1), 
	staff_name VARCHAR(80), 
	PRIMARY KEY (staff_id)
)

/*
2 rows from Staff table:
staff_id	staff_gender	staff_name
1	1	Tom
2	1	Malika
*/


CREATE TABLE "Staff_Department_Assignments" (
	staff_id INTEGER NOT NULL, 
	department_id INTEGER NOT NULL, 
	date_assigned_from DATETIME NOT NULL, 
	job_title_code VARCHAR(10) NOT NULL, 
	date_assigned_to DATETIME, 
	PRIMARY KEY (staff_id, department_id), 
	FOREIGN KEY(staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(department_id) REFERENCES "Departments" (department_id)
)

/*
2 rows from Staff_Department_Assignments table:
staff_id	department_id	date_assigned_from	job_title_code	date_assigned_to
5	4	2017-06-11 22:55:20	Department Manager	2018-03-23 21:59:11
10	5	2017-12-18 19:12:15	Sales Person	2018-03-23 20:25:24
*/


CREATE TABLE "Supplier_Addresses" (
	supplier_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_from DATETIME NOT NULL, 
	date_to DATETIME, 
	PRIMARY KEY (supplier_id, address_id), 
	FOREIGN KEY(supplier_id) REFERENCES "Suppliers" (supplier_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Supplier_Addresses table:
supplier_id	address_id	date_from	date_to
4	5	2016-09-22 16:41:31	2018-03-14 20:06:37
3	9	2014-11-07 19:18:49	2018-03-16 16:39:58
*/


CREATE TABLE "Suppliers" (
	supplier_id INTEGER, 
	supplier_name VARCHAR(80), 
	supplier_phone VARCHAR(80), 
	PRIMARY KEY (supplier_id)
)

/*
2 rows from Suppliers table:
supplier_id	supplier_name	supplier_phone
1	Lidl	(692)009-5928
2	AB Store	1-483-283-4742
*/
Return the names and ids of all products whose price is between 600 and 700.
SELECT product_name ,  product_id FROM products WHERE product_price BETWEEN 600 AND 700
[{'product_name': 'yellow jeans', 'product_id': 2}, {'product_name': 'black jeans', 'product_id': 3}, {'product_name': 'blue topping', 'product_id': 9}, {'product_name': 'keyboard', 'product_id': 13}, {'product_name': 'speaker', 'product_id': 14}]