CREATE TABLE Course_Authors_and_Tutors (
    author_id NUMBER PRIMARY KEY,
    author_tutor_ATB TEXT,
    login_name TEXT,
    password TEXT,
    personal_name TEXT,
    middle_name TEXT,
    family_name TEXT,
    gender_mf TEXT,
    address_line_1 TEXT
);
CREATE TABLE Students (
    student_id NUMBER PRIMARY KEY,
    date_of_registration TIME,
    date_of_latest_logon TIME,
    login_name TEXT,
    password TEXT,
    personal_name TEXT,
    middle_name TEXT,
    family_name TEXT
);
CREATE TABLE Subjects (
    subject_id NUMBER PRIMARY KEY,
    subject_name TEXT
);
CREATE TABLE Courses (
    course_id NUMBER PRIMARY KEY,
    author_id NUMBER,
    subject_id NUMBER,
    course_name TEXT,
    course_description TEXT,
    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 NUMBER PRIMARY KEY,
    student_id NUMBER,
    course_id NUMBER,
    date_of_enrolment TIME,
    date_of_completion TIME,
    FOREIGN KEY ("student_id") REFERENCES Students("student_id"),
    FOREIGN KEY ("course_id") REFERENCES Courses("course_id")
);
CREATE TABLE Student_Tests_Taken (
    registration_id NUMBER,
    date_test_taken TIME,
    test_result TEXT,
    FOREIGN KEY ("registration_id") REFERENCES Student_Course_Enrolment("registration_id")
);

Question: What are the completion dates of all the tests that have result "Fail"?
SQL: SELECT T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Student_Tests_Taken AS T2 ON T1.registration_id  =  T2.registration_id WHERE T2.test_result  =  "Fail"

Question: Return the completion date for all the tests that have "Fail" result.
SQL: SELECT T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Student_Tests_Taken AS T2 ON T1.registration_id  =  T2.registration_id WHERE T2.test_result  =  "Fail"

Question: List the dates of enrollment and completion of the student with personal name "Karson".
SQL: SELECT T1.date_of_enrolment ,  T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.personal_name  =  "Karson"

Question: On what dates did the student whose personal name is "Karson" enroll in and complete the courses?
SQL: SELECT T1.date_of_enrolment ,  T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.personal_name  =  "Karson"

Question: List the dates of enrollment and completion of the student with family name "Zieme" and personal name "Bernie".
SQL: SELECT T1.date_of_enrolment ,  T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.family_name  =  "Zieme" AND T2.personal_name  =  "Bernie"

Question: On what dates did the student with family name "Zieme" and personal name "Bernie" enroll in and complete the courses?
SQL: SELECT T1.date_of_enrolment ,  T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.family_name  =  "Zieme" AND T2.personal_name  =  "Bernie"

Question: Find the student ID and login name of the student with the most course enrollments
SQL: SELECT T1.student_id ,  T2.login_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the student ID and login name of the student who are enrolled in the most courses?
SQL: SELECT T1.student_id ,  T2.login_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY COUNT(*) DESC LIMIT 1

Question: Find the student ID and personal name of the student with at least two enrollments.
SQL: SELECT T1.student_id ,  T2.personal_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  >=  2

Question: Which student are enrolled in at least two courses? Give me the student ID and personal name.
SQL: SELECT T1.student_id ,  T2.personal_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  >=  2

Question: Find the student ID and middle name for all the students with at most two enrollments.
SQL: SELECT T1.student_id ,  T2.middle_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  <=  2

Question: What are the student IDs and middle names of the students enrolled in at most two courses?
SQL: SELECT T1.student_id ,  T2.middle_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  <=  2

Question: Find the personal names of students not enrolled in any course.
SQL: SELECT personal_name FROM Students EXCEPT SELECT T1.personal_name FROM Students AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.student_id  =  T2.student_id

Question: Which students not enrolled in any course? Find their personal names.
SQL: SELECT personal_name FROM Students EXCEPT SELECT T1.personal_name FROM Students AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.student_id  =  T2.student_id

Question: How many students did not have any course enrollment?
SQL: SELECT count(*) FROM Students WHERE student_id NOT IN (SELECT student_id FROM Student_Course_Enrolment)

Question: Count the number of students who did not enroll in any course.
SQL: SELECT count(*) FROM Students WHERE student_id NOT IN (SELECT student_id FROM Student_Course_Enrolment)

Question: Find the common login name of course authors and students.
SQL: SELECT login_name FROM Course_Authors_and_Tutors INTERSECT SELECT login_name FROM Students

Question: What are the login names used both by some course authors and some students?
SQL: SELECT login_name FROM Course_Authors_and_Tutors INTERSECT SELECT login_name FROM Students

Question: Find the common personal name of course authors and students.
SQL: SELECT personal_name FROM Course_Authors_and_Tutors INTERSECT SELECT personal_name FROM Students

Question: What are the personal names used both by some course authors and some students?
SQL: SELECT personal_name FROM Course_Authors_and_Tutors INTERSECT SELECT personal_name FROM Students