Database: e_learning

CREATE TABLE `Course_Authors_and_Tutors` (
`author_id` INTEGER PRIMARY KEY,
`author_tutor_ATB` VARCHAR(3),
`login_name` VARCHAR(40),
`password` VARCHAR(40),
`personal_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`family_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`address_line_1` VARCHAR(80)
);
CREATE TABLE `Students` (
`student_id` INTEGER PRIMARY KEY,
`date_of_registration` DATETIME,
`date_of_latest_logon` DATETIME,
`login_name` VARCHAR(40),
`password` VARCHAR(10),
`personal_name` VARCHAR(40),
`middle_name` VARCHAR(40),
`family_name` VARCHAR(40)
);
CREATE TABLE `Subjects` (
`subject_id` INTEGER PRIMARY KEY,
`subject_name` VARCHAR(120)
);
CREATE TABLE `Courses` (
`course_id` INTEGER PRIMARY KEY,
`author_id` INTEGER NOT NULL,
`subject_id` INTEGER NOT NULL,
`course_name` VARCHAR(120),
`course_description` VARCHAR(255),
FOREIGN KEY (`author_id` ) REFERENCES `Course_Authors_and_Tutors`(`author_id` ),
FOREIGN KEY (`subject_id` ) REFERENCES `Subjects`(`subject_id` )
);
CREATE TABLE `Student_Course_Enrolment` (
`registration_id` INTEGER PRIMARY KEY,
`student_id` INTEGER NOT NULL,
`course_id` INTEGER NOT NULL,
`date_of_enrolment` DATETIME NOT NULL,
`date_of_completion` DATETIME NOT NULL,
FOREIGN KEY (`course_id` ) REFERENCES `Courses`(`course_id` ),
FOREIGN KEY (`student_id` ) REFERENCES `Students`(`student_id` )
);
CREATE TABLE `Student_Tests_Taken` (
`registration_id` INTEGER NOT NULL,
`date_test_taken` DATETIME NOT NULL,
`test_result` VARCHAR(255),
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"

Question: Find the personal name, family name, and author ID of the course author that teaches the most courses.
SQL: 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

Question: What are the personal name, family name, and author ID of the course author who teaches the most courses?
SQL: 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

Question: Find the addresses and author IDs of the course authors that teach at least two courses.
SQL: 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

Question: Which course authors teach two or more courses? Give me their addresses and author IDs.
SQL: 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

Question: Find the names of courses taught by the tutor who has personal name "Julio".
SQL: SELECT T2.course_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T1.personal_name  =  "Julio"

Question: What are the names of the courses taught by the tutor whose personal name is "Julio"?
SQL: SELECT T2.course_name FROM Course_Authors_and_Tutors AS T1 JOIN Courses AS T2 ON T1.author_id  =  T2.author_id WHERE T1.personal_name  =  "Julio"

Question: Find the names and descriptions of courses that belong to the subject named "Computer Science".
SQL: SELECT T1.course_name ,  T1.course_description FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id WHERE T2.subject_name  =  "Computer Science"

Question: What are the names and descriptions of the all courses under the "Computer Science" subject?
SQL: SELECT T1.course_name ,  T1.course_description FROM Courses AS T1 JOIN Subjects AS T2 ON T1.subject_id  =  T2.subject_id WHERE T2.subject_name  =  "Computer Science"

Question: Find the subject ID, subject name, and the corresponding number of available courses for each subject.
SQL: 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

Question: What are the subject ID, subject name, and the number of available courses for each subject?
SQL: 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

Question: 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.
SQL: 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

Question: List the subject ID, name of subject and the number of courses available for each subject in ascending order of the course counts.
SQL: 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

Question: What is the date of enrollment of the course named "Spanish"?
SQL: SELECT T2.date_of_enrolment FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id WHERE T1.course_name  =  "Spanish"

Question: Find the the date of enrollment of the "Spanish" course.
SQL: SELECT T2.date_of_enrolment FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id WHERE T1.course_name  =  "Spanish"

Question: What is the name of the course that has the most student enrollment?
SQL: SELECT T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name ORDER BY COUNT(*) DESC LIMIT 1

Question: Which course is enrolled in by the most students? Give me the course name.
SQL: SELECT T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the names of the courses that have exactly 1 student enrollment?
SQL: SELECT T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name HAVING COUNT(*)  =  1

Question: Find the names of the courses that have just one student enrollment.
SQL: SELECT T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name HAVING COUNT(*)  =  1

Question: What are the descriptions and names of the courses that have student enrollment bigger than 2?
SQL: SELECT T1.course_description ,  T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name HAVING COUNT(*)  >  2

Question: Return the descriptions and names of the courses that have more than two students enrolled in.
SQL: SELECT T1.course_description ,  T1.course_name FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name HAVING COUNT(*)  >  2

Question: What is the name of each course and the corresponding number of student enrollment?
SQL: SELECT T1.course_name ,  COUNT(*) FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name

Question: List the name and the number of enrolled student for each course.
SQL: SELECT T1.course_name ,  COUNT(*) FROM Courses AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.course_id  =  T2.course_id GROUP BY T1.course_name

Question: What are the enrollment dates of all the tests that have result "Pass"?
SQL: 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"

Question: Find the enrollment date for all the tests that have "Pass" result.
SQL: 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"

Question: What are the completion dates of all the tests that have result "Fail"?
SQL: 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"

Question: Return the completion date for all the tests that have "Fail" result.
SQL: 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"

Question: List the dates of enrollment and completion of the student with personal name "Karson".
SQL: 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"

Question: On what dates did the student whose personal name is "Karson" enroll in and complete the courses?
SQL: 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"

Question: List the dates of enrollment and completion of the student with family name "Zieme" and personal name "Bernie".
SQL: 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.family_name  =  "Zieme" AND T2.personal_name  =  "Bernie"

Question: On what dates did the student with family name "Zieme" and personal name "Bernie" enroll in and complete the courses?
SQL: 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.family_name  =  "Zieme" AND T2.personal_name  =  "Bernie"

Question: Find the student ID and login name of the student with the most course enrollments
SQL: SELECT T1.student_id ,  T2.login_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the student ID and login name of the student who are enrolled in the most courses?
SQL: SELECT T1.student_id ,  T2.login_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY COUNT(*) DESC LIMIT 1

Question: Find the student ID and personal name of the student with at least two enrollments.
SQL: SELECT T1.student_id ,  T2.personal_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  >=  2

Question: Which student are enrolled in at least two courses? Give me the student ID and personal name.
SQL: SELECT T1.student_id ,  T2.personal_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  >=  2

Question: Find the student ID and middle name for all the students with at most two enrollments.
SQL: SELECT T1.student_id ,  T2.middle_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  <=  2

Question: What are the student IDs and middle names of the students enrolled in at most two courses?
SQL: SELECT T1.student_id ,  T2.middle_name FROM Student_Course_Enrolment AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id HAVING COUNT(*)  <=  2

Question: Find the personal names of students not enrolled in any course.
SQL: SELECT personal_name FROM Students EXCEPT SELECT T1.personal_name FROM Students AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.student_id  =  T2.student_id

Question: Which students not enrolled in any course? Find their personal names.
SQL: SELECT personal_name FROM Students EXCEPT SELECT T1.personal_name FROM Students AS T1 JOIN Student_Course_Enrolment AS T2 ON T1.student_id  =  T2.student_id

Question: How many students did not have any course enrollment?
SQL: SELECT count(*) FROM Students WHERE student_id NOT IN (SELECT student_id FROM Student_Course_Enrolment)

Question: Count the number of students who did not enroll in any course.
SQL: SELECT count(*) FROM Students WHERE student_id NOT IN (SELECT student_id FROM Student_Course_Enrolment)

Question: Find the common login name of course authors and students.
SQL: SELECT login_name FROM Course_Authors_and_Tutors INTERSECT SELECT login_name FROM Students

Question: What are the login names used both by some course authors and some students?
SQL: SELECT login_name FROM Course_Authors_and_Tutors INTERSECT SELECT login_name FROM Students

Question: Find the common personal name of course authors and students.
SQL: SELECT personal_name FROM Course_Authors_and_Tutors INTERSECT SELECT personal_name FROM Students

Question: What are the personal names used both by some course authors and some students?
SQL: SELECT personal_name FROM Course_Authors_and_Tutors INTERSECT SELECT personal_name FROM Students