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
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.
assistant
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
human
What are the enrollment dates of all the tests that have result 'Pass'?
assistant
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'
human
Find the dates of the tests taken with result 'Pass'.
assistant
SELECT date_test_taken FROM Student_Tests_Taken WHERE test_result = 'Pass'
human
What are the completion dates of all the tests that have result 'Fail'?
assistant
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'
human
List each test result and its count in descending order of count.
assistant
SELECT test_result , COUNT(*) FROM Student_Tests_Taken GROUP BY test_result ORDER BY COUNT(*) DESC
human
Count the number of tests with 'Fail' result.
assistant
SELECT COUNT( * ) FROM Student_Tests_Taken WHERE test_result = "Fail" ;