
CREATE TABLE "Addresses" (
	address_id INTEGER, 
	line_1 VARCHAR(120), 
	line_2 VARCHAR(120), 
	line_3 VARCHAR(120), 
	city VARCHAR(80), 
	zip_postcode VARCHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	other_address_details VARCHAR(255), 
	PRIMARY KEY (address_id)
)


CREATE TABLE "Assessment_Notes" (
	notes_id INTEGER NOT NULL, 
	student_id INTEGER, 
	teacher_id INTEGER NOT NULL, 
	date_of_notes DATETIME, 
	text_of_notes VARCHAR(255), 
	other_details VARCHAR(255), 
	FOREIGN KEY(teacher_id) REFERENCES "Teachers" (teacher_id), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id)
)


CREATE TABLE "Behavior_Incident" (
	incident_id INTEGER, 
	incident_type_code VARCHAR(10) NOT NULL, 
	student_id INTEGER NOT NULL, 
	date_incident_start DATETIME, 
	date_incident_end DATETIME, 
	incident_summary VARCHAR(255), 
	recommendations VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (incident_id), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(incident_type_code) REFERENCES "Ref_Incident_Type" (incident_type_code)
)


CREATE TABLE "Detention" (
	detention_id INTEGER, 
	detention_type_code VARCHAR(10) NOT NULL, 
	teacher_id INTEGER, 
	datetime_detention_start DATETIME, 
	datetime_detention_end DATETIME, 
	detention_summary VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (detention_id), 
	FOREIGN KEY(teacher_id) REFERENCES "Teachers" (teacher_id), 
	FOREIGN KEY(detention_type_code) REFERENCES "Ref_Detention_Type" (detention_type_code)
)


CREATE TABLE "Ref_Address_Types" (
	address_type_code VARCHAR(15), 
	address_type_description VARCHAR(80), 
	PRIMARY KEY (address_type_code)
)


CREATE TABLE "Ref_Detention_Type" (
	detention_type_code VARCHAR(10), 
	detention_type_description VARCHAR(80), 
	PRIMARY KEY (detention_type_code)
)


CREATE TABLE "Ref_Incident_Type" (
	incident_type_code VARCHAR(10), 
	incident_type_description VARCHAR(80), 
	PRIMARY KEY (incident_type_code)
)


CREATE TABLE "Student_Addresses" (
	student_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_address_from DATETIME NOT NULL, 
	date_address_to DATETIME, 
	monthly_rental DECIMAL(19, 4), 
	other_details VARCHAR(255), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Students" (
	student_id INTEGER, 
	address_id INTEGER NOT NULL, 
	first_name VARCHAR(80), 
	middle_name VARCHAR(40), 
	last_name VARCHAR(40), 
	cell_mobile_number VARCHAR(40), 
	email_address VARCHAR(40), 
	date_first_rental DATETIME, 
	date_left_university DATETIME, 
	other_student_details VARCHAR(255), 
	PRIMARY KEY (student_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Students_in_Detention" (
	student_id INTEGER NOT NULL, 
	detention_id INTEGER NOT NULL, 
	incident_id INTEGER NOT NULL, 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(detention_id) REFERENCES "Detention" (detention_id), 
	FOREIGN KEY(incident_id) REFERENCES "Behavior_Incident" (incident_id)
)


CREATE TABLE "Teachers" (
	teacher_id INTEGER, 
	address_id INTEGER NOT NULL, 
	first_name VARCHAR(80), 
	middle_name VARCHAR(80), 
	last_name VARCHAR(80), 
	gender VARCHAR(1), 
	cell_mobile_number VARCHAR(40), 
	email_address VARCHAR(40), 
	other_details VARCHAR(255), 
	PRIMARY KEY (teacher_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)

CREATE TABLE "Addresses" (
	address_id INTEGER, 
	line_1 VARCHAR(120), 
	line_2 VARCHAR(120), 
	line_3 VARCHAR(120), 
	city VARCHAR(80), 
	zip_postcode VARCHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	other_address_details VARCHAR(255), 
	PRIMARY KEY (address_id)
)

/*
2 rows from Addresses table:
address_id	line_1	line_2	line_3	city	zip_postcode	state_province_county	country	other_address_details
1	020 Orie Canyon	None	None	North Loyceville	197	Hawaii	USA	None
2	1333 Boyle Lane	None	None	West Sean	937	Illinois	USA	None
*/


CREATE TABLE "Assessment_Notes" (
	notes_id INTEGER NOT NULL, 
	student_id INTEGER, 
	teacher_id INTEGER NOT NULL, 
	date_of_notes DATETIME, 
	text_of_notes VARCHAR(255), 
	other_details VARCHAR(255), 
	FOREIGN KEY(teacher_id) REFERENCES "Teachers" (teacher_id), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id)
)

/*
2 rows from Assessment_Notes table:
notes_id	student_id	teacher_id	date_of_notes	text_of_notes	other_details
1	7	3	1978-04-15 04:49:18	None	None
2	11	10	2005-06-30 02:48:35	None	None
*/


CREATE TABLE "Behavior_Incident" (
	incident_id INTEGER, 
	incident_type_code VARCHAR(10) NOT NULL, 
	student_id INTEGER NOT NULL, 
	date_incident_start DATETIME, 
	date_incident_end DATETIME, 
	incident_summary VARCHAR(255), 
	recommendations VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (incident_id), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(incident_type_code) REFERENCES "Ref_Incident_Type" (incident_type_code)
)

/*
2 rows from Behavior_Incident table:
incident_id	incident_type_code	student_id	date_incident_start	date_incident_end	incident_summary	recommendations	other_details
1	NOISE	6	2017-07-09 10:04:13	2018-03-08 14:08:54	None	None	None
2	DISTURB	13	2018-01-31 10:51:13	2018-03-18 18:40:05	None	None	None
*/


CREATE TABLE "Detention" (
	detention_id INTEGER, 
	detention_type_code VARCHAR(10) NOT NULL, 
	teacher_id INTEGER, 
	datetime_detention_start DATETIME, 
	datetime_detention_end DATETIME, 
	detention_summary VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (detention_id), 
	FOREIGN KEY(teacher_id) REFERENCES "Teachers" (teacher_id), 
	FOREIGN KEY(detention_type_code) REFERENCES "Ref_Detention_Type" (detention_type_code)
)

/*
2 rows from Detention table:
detention_id	detention_type_code	teacher_id	datetime_detention_start	datetime_detention_end	detention_summary	other_details
1	AFTER	7	2017-09-05 00:38:25	2018-03-08 02:08:32	None	None
2	AFTER	14	2018-01-10 08:09:02	2018-03-07 04:24:48	None	None
*/


CREATE TABLE "Ref_Address_Types" (
	address_type_code VARCHAR(15), 
	address_type_description VARCHAR(80), 
	PRIMARY KEY (address_type_code)
)

/*
2 rows from Ref_Address_Types table:
address_type_code	address_type_description
BILL	Billing
HOME	Home or Residence
*/


CREATE TABLE "Ref_Detention_Type" (
	detention_type_code VARCHAR(10), 
	detention_type_description VARCHAR(80), 
	PRIMARY KEY (detention_type_code)
)

/*
2 rows from Ref_Detention_Type table:
detention_type_code	detention_type_description
BREAK 	During Break time
AFTER	After School
*/


CREATE TABLE "Ref_Incident_Type" (
	incident_type_code VARCHAR(10), 
	incident_type_description VARCHAR(80), 
	PRIMARY KEY (incident_type_code)
)

/*
2 rows from Ref_Incident_Type table:
incident_type_code	incident_type_description
NOISE	Noise
VIOLENCE	Violence
*/


CREATE TABLE "Student_Addresses" (
	student_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_address_from DATETIME NOT NULL, 
	date_address_to DATETIME, 
	monthly_rental DECIMAL(19, 4), 
	other_details VARCHAR(255), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Student_Addresses table:
student_id	address_id	date_address_from	date_address_to	monthly_rental	other_details
6	12	2017-10-16 13:56:34	2018-03-15 10:37:19	826.4319	house
3	18	2017-06-19 12:39:39	2018-03-02 00:19:57	1113.0996	house
*/


CREATE TABLE "Students" (
	student_id INTEGER, 
	address_id INTEGER NOT NULL, 
	first_name VARCHAR(80), 
	middle_name VARCHAR(40), 
	last_name VARCHAR(40), 
	cell_mobile_number VARCHAR(40), 
	email_address VARCHAR(40), 
	date_first_rental DATETIME, 
	date_left_university DATETIME, 
	other_student_details VARCHAR(255), 
	PRIMARY KEY (student_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Students table:
student_id	address_id	first_name	middle_name	last_name	cell_mobile_number	email_address	date_first_rental	date_left_university	other_student_details
1	19	Emma	Frederic	Rohan	235.899.9744	derrick.jenkins@example.com	2017-12-05 15:20:04	2018-03-03 03:33:05	None
2	9	Louvenia	Fatima	Hansen	1-247-673-8446	rohan.clarabelle@example.org	2017-08-08 22:30:36	2018-02-24 11:12:11	None
*/


CREATE TABLE "Students_in_Detention" (
	student_id INTEGER NOT NULL, 
	detention_id INTEGER NOT NULL, 
	incident_id INTEGER NOT NULL, 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(detention_id) REFERENCES "Detention" (detention_id), 
	FOREIGN KEY(incident_id) REFERENCES "Behavior_Incident" (incident_id)
)

/*
2 rows from Students_in_Detention table:
student_id	detention_id	incident_id
3	15	1
8	13	3
*/


CREATE TABLE "Teachers" (
	teacher_id INTEGER, 
	address_id INTEGER NOT NULL, 
	first_name VARCHAR(80), 
	middle_name VARCHAR(80), 
	last_name VARCHAR(80), 
	gender VARCHAR(1), 
	cell_mobile_number VARCHAR(40), 
	email_address VARCHAR(40), 
	other_details VARCHAR(255), 
	PRIMARY KEY (teacher_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Teachers table:
teacher_id	address_id	first_name	middle_name	last_name	gender	cell_mobile_number	email_address	other_details
1	15	Lyla	Wilson	Medhurst	1	792.333.7714	ohammes@example.com	None
2	7	Sid	Tremayne	Brakus	1	202.626.1698x9242	deborah37@example.com	None
*/
Find all information about student addresses, and sort by monthly rental in descending order.
SELECT * FROM Student_Addresses ORDER BY monthly_rental DESC
[{'student_id': 13, 'address_id': 16, 'date_address_from': '2018-03-23 08:25:36', 'date_address_to': '2018-03-12 17:21:24', 'monthly_rental': 1297.807, 'other_details': 'apartment'}, {'student_id': 8, 'address_id': 1, 'date_address_from': '2017-10-31 12:40:34', 'date_address_to': '2018-02-25 05:21:34', 'monthly_rental': 1297.3186, 'other_details': 'apartment'}, {'student_id': 2, 'address_id': 8, 'date_address_from': '2017-07-21 10:13:10', 'date_address_to': '2018-03-20 09:02:48', 'monthly_rental': 1287.5604, 'other_details': 'house'}, {'student_id': 4, 'address_id': 2, 'date_address_from': '2017-09-10 19:41:10', 'date_address_to': '2018-03-04 06:51:19', 'monthly_rental': 1132.742, 'other_details': 'house'}, {'student_id': 3, 'address_id': 18, 'date_address_from': '2017-06-19 12:39:39', 'date_address_to': '2018-03-02 00:19:57', 'monthly_rental': 1113.0996, 'other_details': 'house'}, {'student_id': 7, 'address_id': 13, 'date_address_from': '2018-01-13 22:56:06', 'date_address_to': '2018-03-22 17:56:20', 'monthly_rental': 1067.8383, 'other_details': 'house'}, {'student_id': 5, 'address_id': 13, 'date_address_from': '2017-03-29 18:22:55', 'date_address_to': '2018-03-14 09:12:05', 'monthly_rental': 1036.8462, 'other_details': 'apartment'}, {'student_id': 12, 'address_id': 10, 'date_address_from': '2017-09-23 05:16:17', 'date_address_to': '2018-03-05 21:12:37', 'monthly_rental': 1032.9782, 'other_details': 'apartment'}, {'student_id': 15, 'address_id': 1, 'date_address_from': '2018-03-05 19:28:26', 'date_address_to': '2018-03-15 04:44:58', 'monthly_rental': 1032.8717, 'other_details': 'apartment'}, {'student_id': 1, 'address_id': 5, 'date_address_from': '2017-11-12 04:24:02', 'date_address_to': '2018-03-14 17:00:44', 'monthly_rental': 1007.2597, 'other_details': 'apartment'}, {'student_id': 7, 'address_id': 3, 'date_address_from': '2017-04-28 06:27:14', 'date_address_to': '2018-03-23 09:52:56', 'monthly_rental': 894.0958, 'other_details': 'house'}, {'student_id': 2, 'address_id': 8, 'date_address_from': '2017-04-18 05:36:44', 'date_address_to': '2018-03-07 04:29:28', 'monthly_rental': 865.2615, 'other_details': 'house'}, {'student_id': 6, 'address_id': 12, 'date_address_from': '2017-10-16 13:56:34', 'date_address_to': '2018-03-15 10:37:19', 'monthly_rental': 826.4319, 'other_details': 'house'}, {'student_id': 2, 'address_id': 3, 'date_address_from': '2017-08-12 18:28:31', 'date_address_to': '2018-03-06 21:41:20', 'monthly_rental': 800.1214, 'other_details': 'house'}, {'student_id': 4, 'address_id': 1, 'date_address_from': '2017-06-22 12:20:52', 'date_address_to': '2018-03-04 17:04:35', 'monthly_rental': 756.6745, 'other_details': 'house'}, {'student_id': 11, 'address_id': 12, 'date_address_from': '2018-02-18 06:58:49', 'date_address_to': '2018-02-27 04:45:57', 'monthly_rental': 747.5312, 'other_details': 'house'}, {'student_id': 8, 'address_id': 16, 'date_address_from': '2018-01-12 13:23:23', 'date_address_to': '2018-03-04 06:05:45', 'monthly_rental': 683.0735, 'other_details': 'house'}, {'student_id': 9, 'address_id': 9, 'date_address_from': '2018-02-04 02:28:04', 'date_address_to': '2018-03-12 02:57:48', 'monthly_rental': 679.2988, 'other_details': 'house'}, {'student_id': 13, 'address_id': 19, 'date_address_from': '2017-08-17 11:51:00', 'date_address_to': '2018-03-04 13:24:28', 'monthly_rental': 644.9306, 'other_details': 'apartment'}, {'student_id': 10, 'address_id': 18, 'date_address_from': '2017-10-09 17:31:44', 'date_address_to': '2018-03-18 03:28:47', 'monthly_rental': 620.2801, 'other_details': 'house'}]