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


CREATE TABLE "Contacts" (
	contact_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	gender VARCHAR(1), 
	first_name VARCHAR(80), 
	last_name VARCHAR(50), 
	contact_phone VARCHAR(80), 
	PRIMARY KEY (contact_id)
)


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


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


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	payment_method_code VARCHAR(15), 
	customer_number VARCHAR(20), 
	customer_name VARCHAR(80), 
	customer_address VARCHAR(255), 
	customer_phone VARCHAR(80), 
	customer_email VARCHAR(80), 
	PRIMARY KEY (customer_id)
)


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


CREATE TABLE "Products" (
	product_id INTEGER, 
	product_type_code VARCHAR(15), 
	product_name VARCHAR(80), 
	product_price DOUBLE, 
	PRIMARY KEY (product_id)
)

CREATE TABLE "Addresses" (
	address_id INTEGER, 
	line_1_number_building 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	line_1_number_building	city	zip_postcode	state_province_county	country
1	4315 Kerluke Canyon Apt. 800	Hertafurt	740	Nevada	USA
2	319 Kozey Highway Suite 973	Edgardoberg	282	Colorado	USA
*/


CREATE TABLE "Contacts" (
	contact_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	gender VARCHAR(1), 
	first_name VARCHAR(80), 
	last_name VARCHAR(50), 
	contact_phone VARCHAR(80), 
	PRIMARY KEY (contact_id)
)

/*
2 rows from Contacts table:
contact_id	customer_id	gender	first_name	last_name	contact_phone
1	4	male	Cierra	Collins	+73(2)0854391820
2	6	male	Jennifer	Doyle	482-949-1364x17500
*/


CREATE TABLE "Customer_Address_History" (
	customer_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_from DATETIME 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_Address_History table:
customer_id	address_id	date_from	date_to
12	7	2015-07-23 14:37:18	2018-03-07 12:04:20
12	2	2016-11-06 14:33:12	2018-03-14 21:36:28
*/


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

/*
2 rows from Customer_Orders table:
order_id	customer_id	order_date	order_status_code
1	2	2009-07-19 13:40:49	Completed
2	2	1976-05-28 15:02:44	Part
*/


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	payment_method_code VARCHAR(15), 
	customer_number 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_number	customer_name	customer_address	customer_phone	customer_email
1	Credit Card	456	Kayley	636 Chanelle Isle Apt. 846	+87(9)5279161988	antonette73@example.com
2	Credit Card	553	Sterling	12174 Boyer Crossroad	896.685.8228x2786	stroman.chadd@example.net
*/


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

/*
2 rows from Order_Items table:
order_item_id	order_id	product_id	order_quantity
1	9	15	3
2	8	12	7
*/


CREATE TABLE "Products" (
	product_id INTEGER, 
	product_type_code VARCHAR(15), 
	product_name VARCHAR(80), 
	product_price DOUBLE, 
	PRIMARY KEY (product_id)
)

/*
2 rows from Products table:
product_id	product_type_code	product_name	product_price
1	Hardware	Apple	54753982.574522
2	Clothes	jcrew	30590929.528306
*/
Show each state and the number of addresses in each state.
SELECT state_province_county ,  count(*) FROM addresses GROUP BY state_province_county
[{'state_province_county': 'Colorado', 'count(*)': 1}, {'state_province_county': 'District of Columbia', 'count(*)': 1}, {'state_province_county': 'Idaho', 'count(*)': 1}, {'state_province_county': 'Illinois', 'count(*)': 1}, {'state_province_county': 'Kentucky', 'count(*)': 2}, {'state_province_county': 'Louisiana', 'count(*)': 1}, {'state_province_county': 'Maine', 'count(*)': 1}, {'state_province_county': 'Maryland', 'count(*)': 1}, {'state_province_county': 'Nevada', 'count(*)': 1}, {'state_province_county': 'Oklahoma', 'count(*)': 1}, {'state_province_county': 'Oregon', 'count(*)': 1}, {'state_province_county': 'SouthDakota', 'count(*)': 1}, {'state_province_county': 'Vermont', 'count(*)': 1}, {'state_province_county': 'Virginia', 'count(*)': 1}]