Database schema:

CREATE TABLE "RA" (
	student_id INTEGER -- example: ['2', '5'],
	capability INTEGER -- example: ['2', '5'],
	prof_id INTEGER -- example: ['7', '10'],
	salary TEXT -- example: ['med', 'high'],
	PRIMARY KEY (student_id, prof_id), 
	CONSTRAINT fk_ra_prof FOREIGN KEY(prof_id) REFERENCES prof (prof_id),
	CONSTRAINT fk_ra_student FOREIGN KEY(student_id) REFERENCES student (student_id)
)


CREATE TABLE course (
	course_id INTEGER -- example: ['2', '1'],
	name TEXT -- example: ['Machine Learning Theory', 'Intro to Database 1'],
	credit INTEGER -- example: ['2', '3'],
	diff INTEGER -- example: ['4', '3'],
	PRIMARY KEY (course_id)
)


CREATE TABLE prof (
	prof_id INTEGER -- example: ['2', '1'],
	gender TEXT -- example: ['Male', 'Female'],
	first_name TEXT -- example: ['Nathaniel', 'Zhihua'],
	last_name TEXT -- example: ['Pigford', 'Zhou'],
	email TEXT -- example: ['zzhihua@hku.hk', 'npigford0@hku.hk'],
	popularity INTEGER -- example: ['2', '3'],
	teachingability INTEGER -- example: ['5', '1'],
	graduate_from TEXT -- example: ['University of Washington', 'Beijing Polytechnic University'],
	PRIMARY KEY (prof_id)
)


CREATE TABLE registration (
	course_id INTEGER -- example: ['2', '1'],
	student_id INTEGER -- example: ['2', '3'],
	grade TEXT -- example: ['A', 'B'],
	sat INTEGER -- example: ['5', '4'],
	PRIMARY KEY (course_id, student_id), 
	CONSTRAINT fk_registration_course FOREIGN KEY(course_id) REFERENCES course (course_id),
	CONSTRAINT fk_registration_student FOREIGN KEY(student_id) REFERENCES student (student_id)
)


CREATE TABLE student (
	student_id INTEGER -- example: ['2', '1'],
	f_name TEXT -- example: ['Kerry', 'Chrysa'],
	l_name TEXT -- example: ['Dine-Hart', 'Pryor'],
	phone_number TEXT -- example: ['(243) 6836472', '(672) 9245255'],
	email TEXT -- example: ['kpryor0@hku.hk', 'cdinehart1@hku.hk'],
	intelligence INTEGER -- example: ['5', '2'],
	gpa REAL -- example: ['2.7', '2.4'],
	type TEXT -- example: ['RPG', 'TPG'],
	PRIMARY KEY (student_id)
)

Question: Among the students who got a B in the course Machine Learning Theory, how many of them have a gpa of over 3?
Evidence: B refers to grade; GPA is an abbreviated name of Grade Point Average where over 3 refers to gpa > 3;

SQL:
SELECT COUNT(student_id) FROM registration WHERE grade = 'B' AND student_id IN ( SELECT student_id FROM student WHERE gpa > 3 AND course_id IN ( SELECT course_id FROM course WHERE name = 'Machine Learning Theory' ) )