CREATE TABLE Course_Authors_and_Tutors (
    author_id NUMBER PRIMARY KEY,
    author_tutor_ATB TEXT,
    login_name TEXT,
    password TEXT,
    personal_name TEXT,
    middle_name TEXT,
    family_name TEXT,
    gender_mf TEXT,
    address_line_1 TEXT
);
CREATE TABLE Students (
    student_id NUMBER PRIMARY KEY,
    date_of_registration TIME,
    date_of_latest_logon TIME,
    login_name TEXT,
    password TEXT,
    personal_name TEXT,
    middle_name TEXT,
    family_name TEXT
);
CREATE TABLE Subjects (
    subject_id NUMBER PRIMARY KEY,
    subject_name TEXT
);
CREATE TABLE Courses (
    course_id NUMBER PRIMARY KEY,
    author_id NUMBER,
    subject_id NUMBER,
    course_name TEXT,
    course_description TEXT,
    FOREIGN KEY ("subject_id") REFERENCES Subjects("subject_id"),
    FOREIGN KEY ("author_id") REFERENCES Course_Authors_and_Tutors("author_id")
);
CREATE TABLE Student_Course_Enrolment (
    registration_id NUMBER PRIMARY KEY,
    student_id NUMBER,
    course_id NUMBER,
    date_of_enrolment TIME,
    date_of_completion TIME,
    FOREIGN KEY ("student_id") REFERENCES Students("student_id"),
    FOREIGN KEY ("course_id") REFERENCES Courses("course_id")
);
CREATE TABLE Student_Tests_Taken (
    registration_id NUMBER,
    date_test_taken TIME,
    test_result TEXT,
    FOREIGN KEY ("registration_id") REFERENCES Student_Course_Enrolment("registration_id")
);

Question: How many courses are there in total?
SQL: SELECT count(*) FROM COURSES

Question: Find the total number of courses offered.
SQL: SELECT count(*) FROM COURSES

Question: What are the descriptions of the courses with name "database"?
SQL: SELECT course_description FROM COURSES WHERE course_name  =  "database"

Question: Return the description for the courses named "database".
SQL: SELECT course_description FROM COURSES WHERE course_name  =  "database"

Question: What are the addresses of the course authors or tutors with personal name "Cathrine"
SQL: SELECT address_line_1 FROM Course_Authors_and_Tutors WHERE personal_name	 =  "Cathrine"

Question: Return the addresses of the course authors or tutors whose personal name is "Cathrine".
SQL: SELECT address_line_1 FROM Course_Authors_and_Tutors WHERE personal_name	 =  "Cathrine"

Question: List the addresses of all the course authors or tutors.
SQL: SELECT address_line_1 FROM Course_Authors_and_Tutors

Question: What is the address of each course author or tutor?
SQL: SELECT address_line_1 FROM Course_Authors_and_Tutors

Question: List all the login names and family names of course author and tutors.
SQL: SELECT login_name ,  family_name FROM Course_Authors_and_Tutors

Question: What are the login names and family names of course author and tutors?
SQL: SELECT login_name ,  family_name FROM Course_Authors_and_Tutors

Question: List all the dates of enrollment and completion of students.
SQL: SELECT date_of_enrolment ,  date_of_completion FROM Student_Course_Enrolment

Question: What are all the dates of enrollment and completion in record?
SQL: SELECT date_of_enrolment ,  date_of_completion FROM Student_Course_Enrolment

Question: How many distinct students are enrolled in courses?
SQL: SELECT count(DISTINCT student_id) FROM Student_Course_Enrolment

Question: Find the number of distinct students enrolled in courses.
SQL: SELECT count(DISTINCT student_id) FROM Student_Course_Enrolment

Question: How many distinct courses are enrolled in by students?
SQL: SELECT count(course_id) FROM Student_Course_Enrolment

Question: Find the number of distinct courses that have enrolled students.
SQL: SELECT count(course_id) FROM Student_Course_Enrolment

Question: Find the dates of the tests taken with result "Pass".
SQL: SELECT date_test_taken FROM Student_Tests_Taken WHERE test_result  =  "Pass"

Question: Which tests have "Pass" results? Return the dates when the tests were taken.
SQL: SELECT date_test_taken FROM Student_Tests_Taken WHERE test_result  =  "Pass"

Question: How many tests have result "Fail"?
SQL: SELECT count(*) FROM Student_Tests_Taken WHERE test_result  =  "Fail"

Question: Count the number of tests with "Fail" result.
SQL: SELECT count(*) FROM Student_Tests_Taken WHERE test_result  =  "Fail"

Question: What are the login names of the students with family name "Ward"?
SQL: SELECT login_name FROM Students WHERE family_name  =  "Ward"

Question: Return the login names of the students whose family name is "Ward".
SQL: SELECT login_name FROM Students WHERE family_name  =  "Ward"

Question: What are the dates of the latest logon of the students with family name "Jaskolski" or "Langosh"?
SQL: SELECT date_of_latest_logon FROM Students WHERE family_name  =  "Jaskolski" OR family_name  =  "Langosh"

Question: Find the latest logon date of the students whose family name is "Jaskolski" or "Langosh".
SQL: SELECT date_of_latest_logon FROM Students WHERE family_name  =  "Jaskolski" OR family_name  =  "Langosh"

Question: How many students have personal names that contain the word "son"?
SQL: SELECT COUNT(*) FROM Students WHERE personal_name LIKE "%son%"

Question: Find the number of students who have the word "son" in their personal names.
SQL: SELECT COUNT(*) FROM Students WHERE personal_name LIKE "%son%"

Question: List all the subject names.
SQL: SELECT subject_name FROM SUBJECTS

Question: What are the names of all the subjects.
SQL: SELECT subject_name FROM SUBJECTS

Question: List all the information about course authors and tutors in alphabetical order of the personal name.
SQL: SELECT * FROM Course_Authors_and_Tutors ORDER BY personal_name

Question: Sort the information about course authors and tutors in alphabetical order of the personal name.
SQL: SELECT * FROM Course_Authors_and_Tutors ORDER BY personal_name

Question: List the personal names and family names of all the students in alphabetical order of family name.
SQL: SELECT personal_name ,  family_name FROM Students ORDER BY family_name

Question: What are the personal names and family names of the students? Sort the result in alphabetical order of the family name.
SQL: SELECT personal_name ,  family_name FROM Students ORDER BY family_name

Question: List each test result and its count in descending order of count.
SQL: SELECT test_result ,  COUNT(*) FROM Student_Tests_Taken GROUP BY test_result ORDER BY COUNT(*) DESC

Question: For each distinct test result, find the number of students who got the result.
SQL: SELECT test_result ,  COUNT(*) FROM Student_Tests_Taken GROUP BY test_result ORDER BY COUNT(*) DESC

Question: Find the login name of the course author that teaches the course with name "advanced database".
SQL: SELECT T1.login_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T2.course_name  =  "advanced database"

Question: Which course author teaches the "advanced database" course? Give me his or her login name.
SQL: SELECT T1.login_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T2.course_name  =  "advanced database"

Question: Find the addresses of the course authors who teach the course with name "operating system" or "data structure".
SQL: SELECT T1.address_line_1 FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T2.course_name  =  "operating system" OR T2.course_name  =  "data structure"

Question: What are the addresses of the course authors who teach either "operating system" or "data structure" course.
SQL: SELECT T1.address_line_1 FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T2.course_name  =  "operating system" OR T2.course_name  =  "data structure"