
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 "Customer_Payments" (
	customer_id INTEGER NOT NULL, 
	datetime_payment DATETIME NOT NULL, 
	payment_method_code VARCHAR(10) NOT NULL, 
	amount_payment DOUBLE, 
	PRIMARY KEY (customer_id, datetime_payment), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	customer_address_id INTEGER NOT NULL, 
	customer_status_code VARCHAR(15) NOT NULL, 
	date_became_customer DATETIME, 
	date_of_birth DATETIME, 
	first_name VARCHAR(80), 
	last_name VARCHAR(80), 
	amount_outstanding DOUBLE, 
	email_address VARCHAR(250), 
	phone_number VARCHAR(255), 
	cell_mobile_phone_number VARCHAR(255), 
	PRIMARY KEY (customer_id), 
	FOREIGN KEY(customer_address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Lessons" (
	lesson_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	lesson_status_code VARCHAR(15) NOT NULL, 
	staff_id INTEGER, 
	vehicle_id INTEGER NOT NULL, 
	lesson_date DATETIME, 
	lesson_time VARCHAR(10), 
	price DOUBLE, 
	PRIMARY KEY (lesson_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id), 
	FOREIGN KEY(staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(vehicle_id) REFERENCES "Vehicles" (vehicle_id)
)


CREATE TABLE "Staff" (
	staff_id INTEGER, 
	staff_address_id INTEGER NOT NULL, 
	nickname VARCHAR(80), 
	first_name VARCHAR(80), 
	middle_name VARCHAR(80), 
	last_name VARCHAR(80), 
	date_of_birth DATETIME, 
	date_joined_staff DATETIME, 
	date_left_staff DATETIME, 
	PRIMARY KEY (staff_id), 
	FOREIGN KEY(staff_address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Vehicles" (
	vehicle_id INTEGER, 
	vehicle_details VARCHAR(255), 
	PRIMARY KEY (vehicle_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	3904 Stroman Passage	Port Melyssa	14445	Georgia	USA
2	053 Quigley Island	Hagenesfurt	22194	Kentucky	USA
*/


CREATE TABLE "Customer_Payments" (
	customer_id INTEGER NOT NULL, 
	datetime_payment DATETIME NOT NULL, 
	payment_method_code VARCHAR(10) NOT NULL, 
	amount_payment DOUBLE, 
	PRIMARY KEY (customer_id, datetime_payment), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)

/*
2 rows from Customer_Payments table:
customer_id	datetime_payment	payment_method_code	amount_payment
11	2018-02-05 18:44:46	Direct Debit	9570.93
2	2018-02-24 10:07:05	Direct Debit	8180.26
*/


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	customer_address_id INTEGER NOT NULL, 
	customer_status_code VARCHAR(15) NOT NULL, 
	date_became_customer DATETIME, 
	date_of_birth DATETIME, 
	first_name VARCHAR(80), 
	last_name VARCHAR(80), 
	amount_outstanding DOUBLE, 
	email_address VARCHAR(250), 
	phone_number VARCHAR(255), 
	cell_mobile_phone_number VARCHAR(255), 
	PRIMARY KEY (customer_id), 
	FOREIGN KEY(customer_address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Customers table:
customer_id	customer_address_id	customer_status_code	date_became_customer	date_of_birth	first_name	last_name	amount_outstanding	email_address	phone_number	cell_mobile_phone_number
1	13	Bad Customer	2016-05-11 17:03:48	1998-12-15 13:24:40	Carole	Bernhard	255.0	everette.goyette@example.org	07278206718	861-638-9797
2	10	Bad Customer	2015-11-16 22:52:14	1995-07-17 12:13:16	Genevieve	Terry	7256.0	huel.jana@example.org	+14(5)2351480248	578-518-4785x612
*/


CREATE TABLE "Lessons" (
	lesson_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	lesson_status_code VARCHAR(15) NOT NULL, 
	staff_id INTEGER, 
	vehicle_id INTEGER NOT NULL, 
	lesson_date DATETIME, 
	lesson_time VARCHAR(10), 
	price DOUBLE, 
	PRIMARY KEY (lesson_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id), 
	FOREIGN KEY(staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(vehicle_id) REFERENCES "Vehicles" (vehicle_id)
)

/*
2 rows from Lessons table:
lesson_id	customer_id	lesson_status_code	staff_id	vehicle_id	lesson_date	lesson_time	price
1	6	Cancelled	8	3	2018-02-28 10:55:36	5	199.0
2	8	Cancelled	14	3	2018-03-07 16:12:36	6	167.0
*/


CREATE TABLE "Staff" (
	staff_id INTEGER, 
	staff_address_id INTEGER NOT NULL, 
	nickname VARCHAR(80), 
	first_name VARCHAR(80), 
	middle_name VARCHAR(80), 
	last_name VARCHAR(80), 
	date_of_birth DATETIME, 
	date_joined_staff DATETIME, 
	date_left_staff DATETIME, 
	PRIMARY KEY (staff_id), 
	FOREIGN KEY(staff_address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Staff table:
staff_id	staff_address_id	nickname	first_name	middle_name	last_name	date_of_birth	date_joined_staff	date_left_staff
1	14	thompson.constantin	Janessa	Amara	Sawayn	2010-12-08 16:55:14	2017-04-27 03:21:26	2018-03-23 22:53:12
2	3	santos45	Camylle	Icie	Weissnat	2015-08-01 13:22:43	2016-06-06 08:54:28	2018-03-10 15:25:00
*/


CREATE TABLE "Vehicles" (
	vehicle_id INTEGER, 
	vehicle_details VARCHAR(255), 
	PRIMARY KEY (vehicle_id)
)

/*
2 rows from Vehicles table:
vehicle_id	vehicle_details
1	Van
2	Truck
*/
How many lessons have been cancelled?
SELECT count(*) FROM Lessons WHERE lesson_status_code = "Cancelled";
[{'count(*)': 9}]