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: Find id of candidates whose assessment code is "Pass"?
SQL: SELECT candidate_id FROM candidate_assessments WHERE asessment_outcome_code = "Pass"

Question: What are the ids of the candidates that have an outcome code of Pass?
SQL: SELECT candidate_id FROM candidate_assessments WHERE asessment_outcome_code = "Pass"

Question: Find the cell mobile number of the candidates whose assessment code is "Fail"?
SQL: SELECT T3.cell_mobile_number FROM candidates AS T1 JOIN candidate_assessments AS T2 ON T1.candidate_id = T2.candidate_id JOIN people AS T3 ON T1.candidate_id = T3.person_id WHERE T2.asessment_outcome_code = "Fail"

Question: What are the cell phone numbers of the candidates that received an assessment code of "Fail"?
SQL: SELECT T3.cell_mobile_number FROM candidates AS T1 JOIN candidate_assessments AS T2 ON T1.candidate_id = T2.candidate_id JOIN people AS T3 ON T1.candidate_id = T3.person_id WHERE T2.asessment_outcome_code = "Fail"

Question: What are the id of students who registered course 301?
SQL: SELECT student_id FROM student_course_attendance WHERE course_id  =  301

Question: What are the ids of the students who registered for course 301?
SQL: SELECT student_id FROM student_course_attendance WHERE course_id  =  301

Question: What is the id of the student who most recently registered course 301?
SQL: SELECT student_id FROM student_course_attendance WHERE course_id = 301 ORDER BY date_of_attendance DESC LIMIT 1

Question: What are the ids of the students who registered for course 301 most recently?
SQL: SELECT student_id FROM student_course_attendance WHERE course_id = 301 ORDER BY date_of_attendance DESC LIMIT 1

Question: Find distinct cities of addresses of people?
SQL: SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id

Question: What are the different cities where people live?
SQL: SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id

Question: Find distinct cities of address of students?
SQL: SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id JOIN students AS T3 ON T2.person_id = T3.student_id

Question: What are the different cities where students live?
SQL: SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id JOIN students AS T3 ON T2.person_id = T3.student_id

Question: List the names of courses in alphabetical order?
SQL: SELECT course_name FROM courses ORDER BY course_name

Question: What are the names of the courses in alphabetical order?
SQL: SELECT course_name FROM courses ORDER BY course_name

Question: List the first names of people in alphabetical order?
SQL: SELECT first_name FROM people ORDER BY first_name

Question: What are the first names of the people in alphabetical order?
SQL: SELECT first_name FROM people ORDER BY first_name

Question: What are the id of students who registered courses or attended courses?
SQL: SELECT student_id FROM student_course_registrations UNION SELECT student_id FROM student_course_attendance

Question: What are the ids of the students who either registered or attended a course?
SQL: SELECT student_id FROM student_course_registrations UNION SELECT student_id FROM student_course_attendance

Question: Find the id of courses which are registered or attended by student whose id is 121?
SQL: SELECT course_id FROM student_course_registrations WHERE student_id = 121 UNION SELECT course_id FROM student_course_attendance WHERE student_id = 121

Question: What are the ids of the courses that are registered or attended by the student whose id is 121?
SQL: SELECT course_id FROM student_course_registrations WHERE student_id = 121 UNION SELECT course_id FROM student_course_attendance WHERE student_id = 121

Question: What are all info of students who registered courses but not attended courses?
SQL: SELECT * FROM student_course_registrations WHERE student_id NOT IN (SELECT student_id FROM student_course_attendance)

Question: What are all details of the students who registered but did not attend any course?
SQL: SELECT * FROM student_course_registrations WHERE student_id NOT IN (SELECT student_id FROM student_course_attendance)

Question: List the id of students who registered course statistics in the order of registration date.
SQL: SELECT T2.student_id FROM courses AS T1 JOIN student_course_registrations AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "statistics" ORDER BY T2.registration_date

Question: What are the ids of the students who registered course statistics by order of registration date?
SQL: SELECT T2.student_id FROM courses AS T1 JOIN student_course_registrations AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "statistics" ORDER BY T2.registration_date

Question: List the id of students who attended  statistics courses in the order of attendance date.
SQL: SELECT T2.student_id FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "statistics" ORDER BY T2.date_of_attendance

Question: What are the ids of the students who attended courses in the statistics department in order of attendance date.
SQL: SELECT T2.student_id FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "statistics" ORDER BY T2.date_of_attendance