DATABASE SYSTEM 2. Consider the following database of a university in which seve
ID: 3709314 • Letter: D
Question
DATABASE SYSTEM
2. Consider the following database of a university in which several schools offer courses for students school (school-name, school-street) course (course#, course-name, school-name) enroll (studentID, course#, mark, semester, year) student (studentID, name-first, name-last, phone, student-street) Write SQL for the following: (a) List the course# of all the courses for which more than 50 students are enrolled (5 Marks) (b) Find the studentID of all the students who are enrolled in at least one course offered by School of Humanity and are not enrolled in any courses offered by School of Psychology (5 Marks) (c) Find the studentID of all the students who are enrolled in the maximum number of courses. (For example, the school offers 11 courses and a student is allowed to be enrolled in at most 7 courses. 150 students are enrolled in 3 courses. 170 students are enrolled in 4 courses. 230 students are enrolled in 5 courses. No students are enrolled in 6 courses or more. Your query should return the studentID of those 230 students who are enrolled in 5 courses.) (5 Marks) (d) List the names of students who are enrolled in only courses offered by (5 Marks) schools located in the same streets in which the students live (e) List the schools of courses with maximum marks above 90 (5 Marks)Explanation / Answer
(a) SELECT course# FROM (SELECT course#, count(studentID) as students_in_course FROM enroll GROUP BY course#) WHERE students_in_course > 50
(b) SELECT studentID FROM student WHERE studentID IN (SELECT DISTINCT studentID FROM enroll, course WHERE enroll.course# = course.course# AND school-name = 'School of Humanity') AND studentID NOT IN (SELECT DISTINCT studentID FROM enroll, course WHERE enroll.course# = course.course# AND school-name = 'School of Psychology')
(d) SELECT name-first, name-last FROM student, enroll, course, school WHERE course.school-name = school.school-name AND enroll.course# = course.course# AND student.studentID = course.studentID AND student-street = school.school-street
(e) SELECT DISTINCT * FORM school, course, enroll WHERE school.school-name = course.school-name AND enroll.course# = course.course# AND mark > 90
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.