CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    line_1 TEXT,
    line_2 TEXT,
    city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT
);
CREATE TABLE People (
    person_id NUMBER PRIMARY KEY,
    first_name TEXT,
    middle_name TEXT,
    last_name TEXT,
    cell_mobile_number TEXT,
    email_address TEXT,
    login_name TEXT,
    password TEXT
);
CREATE TABLE Students (
    student_id NUMBER PRIMARY KEY,
    student_details TEXT,
    FOREIGN KEY ("student_id") REFERENCES People("person_id")
);
CREATE TABLE Courses (
    course_id TEXT PRIMARY KEY,
    course_name TEXT,
    course_description TEXT,
    other_details TEXT
);
CREATE TABLE People_Addresses (
    person_address_id NUMBER PRIMARY KEY,
    person_id NUMBER,
    address_id NUMBER,
    date_from TIME,
    date_to TIME,
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id"),
    FOREIGN KEY ("person_id") REFERENCES People("person_id")
);
CREATE TABLE Student_Course_Registrations (
    student_id NUMBER PRIMARY KEY,
    course_id NUMBER,
    registration_date TIME,
    FOREIGN KEY ("course_id") REFERENCES Courses("course_id"),
    FOREIGN KEY ("student_id") REFERENCES Students("student_id")
);
CREATE TABLE Student_Course_Attendance (
    student_id NUMBER PRIMARY KEY,
    course_id NUMBER,
    date_of_attendance TIME,
    FOREIGN KEY ("student_id") REFERENCES Student_Course_Registrations("student_id"),
    FOREIGN KEY ("course_id") REFERENCES Student_Course_Registrations("course_id")
);
CREATE TABLE Candidates (
    candidate_id NUMBER PRIMARY KEY,
    candidate_details TEXT,
    FOREIGN KEY ("candidate_id") REFERENCES People("person_id")
);
CREATE TABLE Candidate_Assessments (
    candidate_id NUMBER PRIMARY KEY,
    qualification TEXT,
    assessment_date TIME,
    asessment_outcome_code TEXT,
    FOREIGN KEY ("candidate_id") REFERENCES Candidates("candidate_id")
);

Question: which course has most number of registered students?
SQL: SELECT T1.course_name FROM courses AS T1 JOIN student_course_registrations AS T2 ON T1.course_id = T2.course_Id GROUP BY T1.course_id ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the course with the most registered students?
SQL: SELECT T1.course_name FROM courses AS T1 JOIN student_course_registrations AS T2 ON T1.course_id = T2.course_Id GROUP BY T1.course_id ORDER BY count(*) DESC LIMIT 1

Question: what is id of students who registered some courses but the least number of courses in these students?
SQL: SELECT student_id FROM student_course_registrations GROUP BY student_id ORDER BY count(*) LIMIT 1

Question: What are the ids of the students who registered for some courses but had the least number of courses for all students?
SQL: SELECT student_id FROM student_course_registrations GROUP BY student_id ORDER BY count(*) LIMIT 1

Question: what are the first name and last name of all candidates?
SQL: SELECT T2.first_name ,  T2.last_name FROM candidates AS T1 JOIN people AS T2 ON T1.candidate_id = T2.person_id

Question: What are the first and last names of all the candidates?
SQL: SELECT T2.first_name ,  T2.last_name FROM candidates AS T1 JOIN people AS T2 ON T1.candidate_id = T2.person_id

Question: List the id of students who never attends courses?
SQL: SELECT student_id FROM students WHERE student_id NOT IN (SELECT student_id FROM student_course_attendance)

Question: What are the  ids of every student who has never attended a course?
SQL: SELECT student_id FROM students WHERE student_id NOT IN (SELECT student_id FROM student_course_attendance)

Question: List the id of students who attended some courses?
SQL: SELECT student_id FROM student_course_attendance

Question: What are the ids of all students who have attended at least one course?
SQL: SELECT student_id FROM student_course_attendance

Question: What are the ids of all students for courses and what are the names of those courses?
SQL: SELECT T1.student_id ,  T2.course_name FROM student_course_registrations AS T1 JOIN courses AS T2 ON T1.course_id = T2.course_id

Question: What is detail of the student who most recently registered course?
SQL: SELECT T2.student_details FROM student_course_registrations AS T1 JOIN students AS T2 ON T1.student_id = T2.student_id ORDER BY T1.registration_date DESC LIMIT 1

Question: What details do we have on the students who registered for courses most recently?
SQL: SELECT T2.student_details FROM student_course_registrations AS T1 JOIN students AS T2 ON T1.student_id = T2.student_id ORDER BY T1.registration_date DESC LIMIT 1

Question: How many students attend course English?
SQL: SELECT count(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "English"

Question: How many students are attending English courses?
SQL: SELECT count(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "English"

Question: How many courses do the student whose id is 171 attend?
SQL: SELECT count(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T2.student_id = 171

Question: How many courses does the student with id 171 actually attend?
SQL: SELECT count(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T2.student_id = 171

Question: Find id of the candidate whose email is stanley.monahan@example.org?
SQL: SELECT T2.candidate_id FROM people AS T1 JOIN candidates AS T2 ON T1.person_id = T2.candidate_id WHERE T1.email_address = "stanley.monahan@example.org"

Question: What is the id of the candidate whose email is stanley.monahan@example.org?
SQL: SELECT T2.candidate_id FROM people AS T1 JOIN candidates AS T2 ON T1.person_id = T2.candidate_id WHERE T1.email_address = "stanley.monahan@example.org"

Question: Find id of the candidate who most recently accessed the course?
SQL: SELECT candidate_id FROM candidate_assessments ORDER BY assessment_date DESC LIMIT 1

Question: What is the id of the candidate who most recently accessed the course?
SQL: SELECT candidate_id FROM candidate_assessments ORDER BY assessment_date DESC LIMIT 1

Question: What is detail of the student who registered the most number of courses?
SQL: SELECT T1.student_details FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question: What are the details of the student who registered for the most number of courses?
SQL: SELECT T1.student_details FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question: List the id of students who registered some courses and the number of their registered courses?
SQL: SELECT T1.student_id ,  count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id

Question: For every student who is registered for some course, how many courses are they registered for?
SQL: SELECT T1.student_id ,  count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id

Question: How many registed students do each course have? List course name and the number of their registered students?
SQL: SELECT T3.course_name ,  count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id JOIN courses AS T3 ON T2.course_id = T3.course_id GROUP BY T2.course_id

Question: For each course id, how many students are registered and what are the course names?
SQL: SELECT T3.course_name ,  count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id JOIN courses AS T3 ON T2.course_id = T3.course_id GROUP BY T2.course_id