
CREATE TABLE "Customer_Addresses" (
	customer_id INTEGER NOT NULL, 
	premise_id INTEGER NOT NULL, 
	date_address_from DATETIME NOT NULL, 
	address_type_code VARCHAR(15) NOT NULL, 
	date_address_to DATETIME, 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id), 
	FOREIGN KEY(premise_id) REFERENCES "Premises" (premise_id)
)


CREATE TABLE "Customer_Orders" (
	order_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	order_status_code VARCHAR(15) NOT NULL, 
	shipping_method_code VARCHAR(15) NOT NULL, 
	order_placed_datetime DATETIME NOT NULL, 
	order_delivered_datetime DATETIME, 
	order_shipping_charges VARCHAR(255), 
	PRIMARY KEY (order_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	payment_method VARCHAR(15) NOT NULL, 
	customer_name VARCHAR(80), 
	customer_phone VARCHAR(80), 
	customer_email VARCHAR(80), 
	customer_address VARCHAR(255), 
	customer_login VARCHAR(80), 
	customer_password VARCHAR(10), 
	PRIMARY KEY (customer_id)
)


CREATE TABLE "Mailshot_Campaigns" (
	mailshot_id INTEGER, 
	product_category VARCHAR(15), 
	mailshot_name VARCHAR(80), 
	mailshot_start_date DATETIME, 
	mailshot_end_date DATETIME, 
	PRIMARY KEY (mailshot_id)
)


CREATE TABLE "Mailshot_Customers" (
	mailshot_id INTEGER NOT NULL, 
	customer_id INTEGER NOT NULL, 
	outcome_code VARCHAR(15) NOT NULL, 
	mailshot_customer_date DATETIME, 
	FOREIGN KEY(mailshot_id) REFERENCES "Mailshot_Campaigns" (mailshot_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Order_Items" (
	item_id INTEGER NOT NULL, 
	order_item_status_code VARCHAR(15) NOT NULL, 
	order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	item_status_code VARCHAR(15), 
	item_delivered_datetime DATETIME, 
	item_order_quantity VARCHAR(80), 
	FOREIGN KEY(order_id) REFERENCES "Customer_Orders" (order_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id)
)


CREATE TABLE "Premises" (
	premise_id INTEGER, 
	premises_type VARCHAR(15) NOT NULL, 
	premise_details VARCHAR(255), 
	PRIMARY KEY (premise_id)
)


CREATE TABLE "Products" (
	product_id INTEGER, 
	product_category VARCHAR(15) NOT NULL, 
	product_name VARCHAR(80), 
	PRIMARY KEY (product_id)
)

CREATE TABLE "Customer_Addresses" (
	customer_id INTEGER NOT NULL, 
	premise_id INTEGER NOT NULL, 
	date_address_from DATETIME NOT NULL, 
	address_type_code VARCHAR(15) NOT NULL, 
	date_address_to DATETIME, 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id), 
	FOREIGN KEY(premise_id) REFERENCES "Premises" (premise_id)
)

/*
2 rows from Customer_Addresses table:
customer_id	premise_id	date_address_from	address_type_code	date_address_to
15	1	1992-08-23 21:17:38	Billing	2018-02-28 20:54:58
2	2	2010-02-23 02:49:50	Billing	2018-03-15 18:34:41
*/


CREATE TABLE "Customer_Orders" (
	order_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	order_status_code VARCHAR(15) NOT NULL, 
	shipping_method_code VARCHAR(15) NOT NULL, 
	order_placed_datetime DATETIME NOT NULL, 
	order_delivered_datetime DATETIME, 
	order_shipping_charges VARCHAR(255), 
	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	shipping_method_code	order_placed_datetime	order_delivered_datetime	order_shipping_charges
1	2	Cancelled	Unspecified	2015-06-15 18:19:26	2018-03-20 12:00:00	85.79
2	8	Cancelled	Unspecified	2009-07-05 20:02:55	2018-03-20 12:00:00	98.4
*/


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	payment_method VARCHAR(15) NOT NULL, 
	customer_name VARCHAR(80), 
	customer_phone VARCHAR(80), 
	customer_email VARCHAR(80), 
	customer_address VARCHAR(255), 
	customer_login VARCHAR(80), 
	customer_password VARCHAR(10), 
	PRIMARY KEY (customer_id)
)

/*
2 rows from Customers table:
customer_id	payment_method	customer_name	customer_phone	customer_email	customer_address	customer_login	customer_password
1	Credit Card	Wendell	(773)971-9411x759	rodriguez.nathanael@example.net	618 Webster Stream Apt. 161	None	b4459ad261
2	Direct Debit	Gage	(402)890-2603x6005	stark.elna@example.net	0260 Walker Trace	None	7905d7f1b1
*/


CREATE TABLE "Mailshot_Campaigns" (
	mailshot_id INTEGER, 
	product_category VARCHAR(15), 
	mailshot_name VARCHAR(80), 
	mailshot_start_date DATETIME, 
	mailshot_end_date DATETIME, 
	PRIMARY KEY (mailshot_id)
)

/*
2 rows from Mailshot_Campaigns table:
mailshot_id	product_category	mailshot_name	mailshot_start_date	mailshot_end_date
1	food	US	2018-02-15 09:52:17	2018-03-08 14:40:20
2	clothes	US	2017-09-14 12:05:30	2018-03-15 01:06:16
*/


CREATE TABLE "Mailshot_Customers" (
	mailshot_id INTEGER NOT NULL, 
	customer_id INTEGER NOT NULL, 
	outcome_code VARCHAR(15) NOT NULL, 
	mailshot_customer_date DATETIME, 
	FOREIGN KEY(mailshot_id) REFERENCES "Mailshot_Campaigns" (mailshot_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)

/*
2 rows from Mailshot_Customers table:
mailshot_id	customer_id	outcome_code	mailshot_customer_date
4	3	Order	2018-03-15 15:43:14
10	1	No Response	2018-03-04 13:06:55
*/


CREATE TABLE "Order_Items" (
	item_id INTEGER NOT NULL, 
	order_item_status_code VARCHAR(15) NOT NULL, 
	order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	item_status_code VARCHAR(15), 
	item_delivered_datetime DATETIME, 
	item_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:
item_id	order_item_status_code	order_id	product_id	item_status_code	item_delivered_datetime	item_order_quantity
1	Delivered	10	13	good	2018-02-25 18:22:10	6
2	Paid	6	7	good	2018-02-27 18:04:55	7
*/


CREATE TABLE "Premises" (
	premise_id INTEGER, 
	premises_type VARCHAR(15) NOT NULL, 
	premise_details VARCHAR(255), 
	PRIMARY KEY (premise_id)
)

/*
2 rows from Premises table:
premise_id	premises_type	premise_details
1	Warehouse	036 Lueilwitz Harbor
2	Warehouse	676 Kelly Spur
*/


CREATE TABLE "Products" (
	product_id INTEGER, 
	product_category VARCHAR(15) NOT NULL, 
	product_name VARCHAR(80), 
	PRIMARY KEY (product_id)
)

/*
2 rows from Products table:
product_id	product_category	product_name
1	Food	Coffee Bean
2	Electronics	Keyboard
*/
Show the names of customers who have at least 2 mailshots with outcome code 'Order'.
SELECT T2.customer_name FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE outcome_code  =  'Order' GROUP BY T1.customer_id HAVING count(*)  >=  2
[{'customer_name': 'Carlotta'}, {'customer_name': 'Osborne'}, {'customer_name': 'Annabel'}]