
CREATE TABLE "Course_Authors_and_Tutors" (
	author_id INTEGER, 
	"author_tutor_ATB" VARCHAR(3), 
	login_name VARCHAR(40), 
	password VARCHAR(40), 
	personal_name VARCHAR(80), 
	middle_name VARCHAR(80), 
	family_name VARCHAR(80), 
	gender_mf VARCHAR(1), 
	address_line_1 VARCHAR(80), 
	PRIMARY KEY (author_id)
)


CREATE TABLE "Courses" (
	course_id INTEGER, 
	author_id INTEGER NOT NULL, 
	subject_id INTEGER NOT NULL, 
	course_name VARCHAR(120), 
	course_description VARCHAR(255), 
	PRIMARY KEY (course_id), 
	FOREIGN KEY(subject_id) REFERENCES "Subjects" (subject_id), 
	FOREIGN KEY(author_id) REFERENCES "Course_Authors_and_Tutors" (author_id)
)


CREATE TABLE "Student_Course_Enrolment" (
	registration_id INTEGER, 
	student_id INTEGER NOT NULL, 
	course_id INTEGER NOT NULL, 
	date_of_enrolment DATETIME NOT NULL, 
	date_of_completion DATETIME NOT NULL, 
	PRIMARY KEY (registration_id), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(course_id) REFERENCES "Courses" (course_id)
)


CREATE TABLE "Student_Tests_Taken" (
	registration_id INTEGER NOT NULL, 
	date_test_taken DATETIME NOT NULL, 
	test_result VARCHAR(255), 
	FOREIGN KEY(registration_id) REFERENCES "Student_Course_Enrolment" (registration_id)
)


CREATE TABLE "Students" (
	student_id INTEGER, 
	date_of_registration DATETIME, 
	date_of_latest_logon DATETIME, 
	login_name VARCHAR(40), 
	password VARCHAR(10), 
	personal_name VARCHAR(40), 
	middle_name VARCHAR(40), 
	family_name VARCHAR(40), 
	PRIMARY KEY (student_id)
)


CREATE TABLE "Subjects" (
	subject_id INTEGER, 
	subject_name VARCHAR(120), 
	PRIMARY KEY (subject_id)
)

CREATE TABLE "Course_Authors_and_Tutors" (
	author_id INTEGER, 
	"author_tutor_ATB" VARCHAR(3), 
	login_name VARCHAR(40), 
	password VARCHAR(40), 
	personal_name VARCHAR(80), 
	middle_name VARCHAR(80), 
	family_name VARCHAR(80), 
	gender_mf VARCHAR(1), 
	address_line_1 VARCHAR(80), 
	PRIMARY KEY (author_id)
)

/*
2 rows from Course_Authors_and_Tutors table:
author_id	author_tutor_ATB	login_name	password	personal_name	middle_name	family_name	gender_mf	address_line_1
1	331	jmckenzie	c40fa148bdd0d2d45cd6e9ec1e685750fe07f81b	Cathrine	Ruthie	Grant	0	756 Monahan Mews
Spinkashire, NJ 64230-5098
2	975	heidenreich.ara	24b0ee84063c3b017ab1839e01b7280f47f7c7c2	Retha	Corene	Armstrong	0	98623 Huels Manor
Jasttown, DE 31611
*/


CREATE TABLE "Courses" (
	course_id INTEGER, 
	author_id INTEGER NOT NULL, 
	subject_id INTEGER NOT NULL, 
	course_name VARCHAR(120), 
	course_description VARCHAR(255), 
	PRIMARY KEY (course_id), 
	FOREIGN KEY(subject_id) REFERENCES "Subjects" (subject_id), 
	FOREIGN KEY(author_id) REFERENCES "Course_Authors_and_Tutors" (author_id)
)

/*
2 rows from Courses table:
course_id	author_id	subject_id	course_name	course_description
1	8	1	database	database
2	6	1	advanced database	advanced database
*/


CREATE TABLE "Student_Course_Enrolment" (
	registration_id INTEGER, 
	student_id INTEGER NOT NULL, 
	course_id INTEGER NOT NULL, 
	date_of_enrolment DATETIME NOT NULL, 
	date_of_completion DATETIME NOT NULL, 
	PRIMARY KEY (registration_id), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(course_id) REFERENCES "Courses" (course_id)
)

/*
2 rows from Student_Course_Enrolment table:
registration_id	student_id	course_id	date_of_enrolment	date_of_completion
1	11	2	2017-10-09 07:09:02	2018-02-26 07:48:52
2	15	4	2017-11-13 12:49:33	2018-03-04 01:24:56
*/


CREATE TABLE "Student_Tests_Taken" (
	registration_id INTEGER NOT NULL, 
	date_test_taken DATETIME NOT NULL, 
	test_result VARCHAR(255), 
	FOREIGN KEY(registration_id) REFERENCES "Student_Course_Enrolment" (registration_id)
)

/*
2 rows from Student_Tests_Taken table:
registration_id	date_test_taken	test_result
12	2018-03-25 03:27:16	Fail
10	2018-03-25 21:50:22	Pass
*/


CREATE TABLE "Students" (
	student_id INTEGER, 
	date_of_registration DATETIME, 
	date_of_latest_logon DATETIME, 
	login_name VARCHAR(40), 
	password VARCHAR(10), 
	personal_name VARCHAR(40), 
	middle_name VARCHAR(40), 
	family_name VARCHAR(40), 
	PRIMARY KEY (student_id)
)

/*
2 rows from Students table:
student_id	date_of_registration	date_of_latest_logon	login_name	password	personal_name	middle_name	family_name
1	2015-07-22 13:32:35	2017-10-17 22:52:26	annamae.hoppe	db8765bb6f	Wilson	Aubrey	Ward
2	2015-07-02 00:21:42	2017-06-24 22:16:27	wmiller	35faf8182a	Karson	Luella	Jaskolski
*/


CREATE TABLE "Subjects" (
	subject_id INTEGER, 
	subject_name VARCHAR(120), 
	PRIMARY KEY (subject_id)
)

/*
2 rows from Subjects table:
subject_id	subject_name
1	Computer Science
2	Arts
*/
List each test result and its count in descending order of count.
SELECT test_result ,  COUNT(*) FROM Student_Tests_Taken GROUP BY test_result ORDER BY COUNT(*) DESC
[{'test_result': 'Pass', 'COUNT(*)': 8}, {'test_result': 'Fail', 'COUNT(*)': 7}]