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
List all the login names and family names of course author and tutors.
assistant
SELECT login_name , family_name FROM Course_Authors_and_Tutors
human
Find the common personal name of course authors and students.
assistant
SELECT personal_name FROM Course_Authors_and_Tutors INTERSECT SELECT personal_name FROM Students
human
List the addresses of all the course authors or tutors.
assistant
SELECT address_line_1 FROM Course_Authors_and_Tutors
human
Find the addresses and author IDs of the course authors that teach at least two courses.
assistant
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
human
List all the information about course authors and tutors in alphabetical order of the personal name.
assistant
SELECT * FROM Course_Authors_and_Tutors ORDER BY personal_name
human
Find the personal name, family name, and author ID of the course author that teaches the most courses.
assistant
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 ;