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: Find the personal name, family name, and author ID of the course author that teaches the most courses.
SQL: SELECT T1.personal_name ,  T1.family_name ,  T2.author_id FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id GROUP BY T2.author_id ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the personal name, family name, and author ID of the course author who teaches the most courses?
SQL: SELECT T1.personal_name ,  T1.family_name ,  T2.author_id FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id GROUP BY T2.author_id ORDER BY COUNT(*) DESC LIMIT 1

Question: Find the addresses and author IDs of the course authors that teach at least two courses.
SQL: SELECT T1.address_line_1 ,  T2.author_id FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id GROUP BY T2.author_id HAVING Count(*)  >=  2

Question: Which course authors teach two or more courses? Give me their addresses and author IDs.
SQL: SELECT T1.address_line_1 ,  T2.author_id FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id GROUP BY T2.author_id HAVING Count(*)  >=  2

Question: Find the names of courses taught by the tutor who has personal name "Julio".
SQL: SELECT T2.course_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T1.personal_name  =  "Julio"

Question: What are the names of the courses taught by the tutor whose personal name is "Julio"?
SQL: SELECT T2.course_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T1.personal_name  =  "Julio"

Question: Find the names and descriptions of courses that belong to the subject named "Computer Science".
SQL: SELECT T1.course_name ,  T1.course_description FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id WHERE T2.subject_name  =  "Computer Science"

Question: What are the names and descriptions of the all courses under the "Computer Science" subject?
SQL: SELECT T1.course_name ,  T1.course_description FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id WHERE T2.subject_name  =  "Computer Science"

Question: Find the subject ID, subject name, and the corresponding number of available courses for each subject.
SQL: SELECT T1.subject_id ,  T2.subject_name ,  COUNT(*) FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id GROUP BY T1.subject_id

Question: What are the subject ID, subject name, and the number of available courses for each subject?
SQL: SELECT T1.subject_id ,  T2.subject_name ,  COUNT(*) FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id GROUP BY T1.subject_id

Question: Find the subject ID, name of subject and the corresponding number of courses for each subject, and sort by the course count in ascending order.
SQL: SELECT T1.subject_id ,  T2.subject_name ,  COUNT(*) FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id GROUP BY T1.subject_id ORDER BY COUNT(*) ASC

Question: List the subject ID, name of subject and the number of courses available for each subject in ascending order of the course counts.
SQL: SELECT T1.subject_id ,  T2.subject_name ,  COUNT(*) FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id GROUP BY T1.subject_id ORDER BY COUNT(*) ASC

Question: What is the date of enrollment of the course named "Spanish"?
SQL: SELECT T2.date_of_enrolment FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id WHERE T1.course_name  =  "Spanish"

Question: Find the the date of enrollment of the "Spanish" course.
SQL: SELECT T2.date_of_enrolment FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id WHERE T1.course_name  =  "Spanish"

Question: What is the name of the course that has the most student enrollment?
SQL: SELECT T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name ORDER BY COUNT(*) DESC LIMIT 1

Question: Which course is enrolled in by the most students? Give me the course name.
SQL: SELECT T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the names of the courses that have exactly 1 student enrollment?
SQL: SELECT T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name HAVING COUNT(*)  =  1

Question: Find the names of the courses that have just one student enrollment.
SQL: SELECT T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name HAVING COUNT(*)  =  1

Question: What are the descriptions and names of the courses that have student enrollment bigger than 2?
SQL: SELECT T1.course_description ,  T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name HAVING COUNT(*)  >  2

Question: Return the descriptions and names of the courses that have more than two students enrolled in.
SQL: SELECT T1.course_description ,  T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name HAVING COUNT(*)  >  2

Question: What is the name of each course and the corresponding number of student enrollment?
SQL: SELECT T1.course_name ,  COUNT(*) FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name

Question: List the name and the number of enrolled student for each course.
SQL: SELECT T1.course_name ,  COUNT(*) FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name

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

Question: Find the enrollment date for all the tests that have "Pass" result.
SQL: SELECT T1.date_of_enrolment FROM Student_Course_Enrolment AS T1 JOIN Student_Tests_Taken AS T2 ON T1.registration_id  =  T2.registration_id WHERE T2.test_result  =  "Pass"