human
Schema:
Course_Authors_and_Tutors (author_id, author_tutor_ATB, login_name, password, personal_name, middle_name, family_name, gender_mf, address_line_1)
Students (student_id, date_of_registration, date_of_latest_logon, login_name, password, personal_name, middle_name, family_name)
Subjects (subject_id, subject_name)
Courses (course_id, author_id, subject_id, course_name, course_description)
Student_Course_Enrolment (registration_id, student_id, course_id, date_of_enrolment, date_of_completion)
Student_Tests_Taken (registration_id, date_test_taken, test_result)
Please answer with SQL.
assistant
Sure, I understand.
human
How many distinct students are enrolled in courses?
assistant
SELECT count(DISTINCT student_id) FROM Student_Course_Enrolment
human
How many distinct courses are enrolled in by students?
assistant
SELECT count(course_id) FROM Student_Course_Enrolment
human
What is the name of each course and the corresponding number of student enrollment?
assistant
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
human
What is the name of the course that has the most student enrollment?
assistant
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
human
What are the names of the courses that have exactly 1 student enrollment?
assistant
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
human
What are the descriptions and names of the courses that have student enrollment bigger than 2?
assistant
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 ;