
CREATE TABLE "Addresses" (
	address_id INTEGER NOT NULL, 
	line_1 VARCHAR(80), 
	line_2 VARCHAR(80), 
	city VARCHAR(50), 
	zip_postcode CHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	PRIMARY KEY (address_id)
)


CREATE TABLE "Candidate_Assessments" (
	candidate_id INTEGER NOT NULL, 
	qualification CHAR(15) NOT NULL, 
	assessment_date DATETIME NOT NULL, 
	asessment_outcome_code CHAR(15) NOT NULL, 
	PRIMARY KEY (candidate_id, qualification), 
	FOREIGN KEY(candidate_id) REFERENCES "Candidates" (candidate_id)
)


CREATE TABLE "Candidates" (
	candidate_id INTEGER NOT NULL, 
	candidate_details VARCHAR(255), 
	PRIMARY KEY (candidate_id), 
	FOREIGN KEY(candidate_id) REFERENCES "People" (person_id)
)


CREATE TABLE "Courses" (
	course_id VARCHAR(100) NOT NULL, 
	course_name VARCHAR(120), 
	course_description VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (course_id)
)


CREATE TABLE "People" (
	person_id INTEGER NOT NULL, 
	first_name VARCHAR(255), 
	middle_name VARCHAR(255), 
	last_name VARCHAR(255), 
	cell_mobile_number VARCHAR(40), 
	email_address VARCHAR(40), 
	login_name VARCHAR(40), 
	password VARCHAR(40), 
	PRIMARY KEY (person_id)
)


CREATE TABLE "People_Addresses" (
	person_address_id INTEGER NOT NULL, 
	person_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_from DATETIME, 
	date_to DATETIME, 
	PRIMARY KEY (person_address_id), 
	FOREIGN KEY(person_id) REFERENCES "People" (person_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Student_Course_Attendance" (
	student_id INTEGER NOT NULL, 
	course_id INTEGER NOT NULL, 
	date_of_attendance DATETIME NOT NULL, 
	PRIMARY KEY (student_id, course_id), 
	FOREIGN KEY(student_id, course_id) REFERENCES "Student_Course_Registrations" (student_id, course_id)
)


CREATE TABLE "Student_Course_Registrations" (
	student_id INTEGER NOT NULL, 
	course_id INTEGER NOT NULL, 
	registration_date DATETIME NOT NULL, 
	PRIMARY KEY (student_id, course_id), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(course_id) REFERENCES "Courses" (course_id)
)


CREATE TABLE "Students" (
	student_id INTEGER NOT NULL, 
	student_details VARCHAR(255), 
	PRIMARY KEY (student_id), 
	FOREIGN KEY(student_id) REFERENCES "People" (person_id)
)

CREATE TABLE "Addresses" (
	address_id INTEGER NOT NULL, 
	line_1 VARCHAR(80), 
	line_2 VARCHAR(80), 
	city VARCHAR(50), 
	zip_postcode CHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	PRIMARY KEY (address_id)
)

/*
2 rows from Addresses table:
address_id	line_1	line_2	city	zip_postcode	state_province_county	country
5	0900 Roderick Oval
New Albina, WA 19200-7914	Suite 096	Linnealand	862	Montana	USA
9	966 Dach Ports Apt. 322
Lake Harmonyhaven, VA 65235	Apt. 163	South Minnie	716	Texas	USA
*/


CREATE TABLE "Candidate_Assessments" (
	candidate_id INTEGER NOT NULL, 
	qualification CHAR(15) NOT NULL, 
	assessment_date DATETIME NOT NULL, 
	asessment_outcome_code CHAR(15) NOT NULL, 
	PRIMARY KEY (candidate_id, qualification), 
	FOREIGN KEY(candidate_id) REFERENCES "Candidates" (candidate_id)
)

/*
2 rows from Candidate_Assessments table:
candidate_id	qualification	assessment_date	asessment_outcome_code
111	A	2010-04-07 11:44:34	Pass
121	B	2010-04-17 11:44:34	Pass
*/


CREATE TABLE "Candidates" (
	candidate_id INTEGER NOT NULL, 
	candidate_details VARCHAR(255), 
	PRIMARY KEY (candidate_id), 
	FOREIGN KEY(candidate_id) REFERENCES "People" (person_id)
)

/*
2 rows from Candidates table:
candidate_id	candidate_details
111	Jane
121	Robert
*/


CREATE TABLE "Courses" (
	course_id VARCHAR(100) NOT NULL, 
	course_name VARCHAR(120), 
	course_description VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (course_id)
)

/*
2 rows from Courses table:
course_id	course_name	course_description	other_details
301	statistics	statistics	None
302	English	English	None
*/


CREATE TABLE "People" (
	person_id INTEGER NOT NULL, 
	first_name VARCHAR(255), 
	middle_name VARCHAR(255), 
	last_name VARCHAR(255), 
	cell_mobile_number VARCHAR(40), 
	email_address VARCHAR(40), 
	login_name VARCHAR(40), 
	password VARCHAR(40), 
	PRIMARY KEY (person_id)
)

/*
2 rows from People table:
person_id	first_name	middle_name	last_name	cell_mobile_number	email_address	login_name	password
111	Shannon	Elissa	Senger	01955267735	javier.trantow@example.net	pgub	5e4ff49a61b3544da3ad7dc7e2cf28847564c64c
121	Virginie	Jasmin	Hartmann	(508)319-2970x043	boyer.lonie@example.com	bkkv	b063331ea8116befaa7b84c59c6a22200f5f8caa
*/


CREATE TABLE "People_Addresses" (
	person_address_id INTEGER NOT NULL, 
	person_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_from DATETIME, 
	date_to DATETIME, 
	PRIMARY KEY (person_address_id), 
	FOREIGN KEY(person_id) REFERENCES "People" (person_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from People_Addresses table:
person_address_id	person_id	address_id	date_from	date_to
122	111	9	2012-09-26 13:21:00	2018-03-21 09:46:30
257	121	5	2008-07-31 02:17:25	2018-03-09 02:11:12
*/


CREATE TABLE "Student_Course_Attendance" (
	student_id INTEGER NOT NULL, 
	course_id INTEGER NOT NULL, 
	date_of_attendance DATETIME NOT NULL, 
	PRIMARY KEY (student_id, course_id), 
	FOREIGN KEY(student_id, course_id) REFERENCES "Student_Course_Registrations" (student_id, course_id)
)

/*
2 rows from Student_Course_Attendance table:
student_id	course_id	date_of_attendance
111	301	2008-11-04 10:35:13
121	301	2012-04-09 11:44:34
*/


CREATE TABLE "Student_Course_Registrations" (
	student_id INTEGER NOT NULL, 
	course_id INTEGER NOT NULL, 
	registration_date DATETIME NOT NULL, 
	PRIMARY KEY (student_id, course_id), 
	FOREIGN KEY(student_id) REFERENCES "Students" (student_id), 
	FOREIGN KEY(course_id) REFERENCES "Courses" (course_id)
)

/*
2 rows from Student_Course_Registrations table:
student_id	course_id	registration_date
111	301	2008-11-04 10:35:13
121	301	2008-10-04 10:35:13
*/


CREATE TABLE "Students" (
	student_id INTEGER NOT NULL, 
	student_details VARCHAR(255), 
	PRIMARY KEY (student_id), 
	FOREIGN KEY(student_id) REFERENCES "People" (person_id)
)

/*
2 rows from Students table:
student_id	student_details
111	Marry
121	Martin
*/
How many courses do the student whose id is 171 attend?
SELECT count(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T2.student_id = 171
[{'count(*)': 1}]