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 the personal names and family names of all the students in alphabetical order of family name.
assistant
SELECT personal_name , family_name FROM Students ORDER BY family_name
human
What are the login names of the students with family name 'Ward'?
assistant
SELECT login_name FROM Students WHERE family_name = 'Ward'
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
List the dates of enrollment and completion of the student with personal name 'Karson'.
assistant
SELECT T1.date_of_enrolment , T1.date_of_completion FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id = T2.student_id WHERE T2.personal_name = 'Karson'
human
Find the common login name of course authors and students.
assistant
SELECT login_name FROM Course_Authors_and_Tutors INTERSECT SELECT login_name FROM Students
human
Find the latest logon date of the students whose family name is 'Jaskolski' or 'Langosh'.
assistant
SELECT date_of_latest_logon FROM Students WHERE family_name = "Jaskolski" OR family_name = "Langosh" ;