human
Schema:
Addresses (address_id, line_1, line_2, city, zip_postcode, state_province_county, country)
People (person_id, first_name, middle_name, last_name, cell_mobile_number, email_address, login_name, password)
Students (student_id, student_details)
Courses (course_id, course_name, course_description, other_details)
People_Addresses (person_address_id, person_id, address_id, date_from, date_to)
Student_Course_Registrations (student_id, course_id, registration_date)
Student_Course_Attendance (student_id, course_id, date_of_attendance)
Candidates (candidate_id, candidate_details)
Candidate_Assessments (candidate_id, qualification, assessment_date, asessment_outcome_code)
Please answer with SQL.
assistant
Sure, I understand.
human
Find id of the candidate whose email is stanley.monahan@example.org?
assistant
SELECT T2.candidate_id FROM people AS T1 JOIN candidates AS T2 ON T1.person_id = T2.candidate_id WHERE T1.email_address = 'stanley.monahan@example.org'
human
what is id of students who registered some courses but the least number of courses in these students?
assistant
SELECT student_id FROM student_course_registrations GROUP BY student_id ORDER BY count(*) LIMIT 1
human
List the id of students who registered some courses and the number of their registered courses?
assistant
SELECT T1.student_id , count(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id
human
Find id of the candidate who most recently accessed the course?
assistant
SELECT candidate_id FROM candidate_assessments ORDER BY assessment_date DESC LIMIT 1
human
Find id of candidates whose assessment code is 'Pass'?
assistant
SELECT candidate_id FROM candidate_assessments WHERE asessment_outcome_code = 'Pass'
human
Find the cell mobile number of the candidates whose assessment code is 'Fail'?
assistant
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" ;