GATE | GATE-CS-2015 (Mock Test) | Question 17
Consider the following three table to store student enrollements in different courses.
Student(EnrollNo, Name)
Course(CourseID, Name)
EnrollMents(EnrollNo, CourseID)
What does the following query do?
SELECT S.Name
FROM Student S, Course C, Enrollments E
WHERE S.EnrollNo = E.EnrollNo AND
C.Name = \"DBMS\" AND
E.CourseID = C.CourseID AND
S.EnrollNo IN
(SELECT S2.EnrollNo
FROM Student S2, Course C2, Enrollments E2
WHERE S2.EnrollNo = E2.EnrollNo AND
E2.CourseID = C2.CourseID
C2.Name = \"OS\")
(A)
Name of all students who are either enrolled in \”DBMS\” or \”OS\” courses
(B)
Name of all students who are enrolled in \”DBMS\” and \”OS\”
(C)
Name of all students who are either enrolled in \”DBMS\” or \”OS\” or both.
(D)
None of the above
Answer: (B)
Explanation:
Background Reading: The above query is an example of nested query i.e. query within a query. Firstly the inner query is solved and then the outer one depending on the result of the inner query.
- WHERE IN returns values that matches values in a list or subquery.
- WHERE IN is a shorthand for multiple OR conditions.
Here, firstly the inner query is solved. It returns all the Enrollment
Numbers (SELECT S2.EnrollNo) of students where the students’ enrollment
number matches with the enrollment number of the courses
(WHERE S2.EnrollNo = E2.EnrollNo) which have the course IDs whose Course
Name is “OS” (E2.CourseID = C2.CourseID and C2.Name = “OS”).
Hence all the enrollment IDs are filtered out for the students who are enrolled for the “OS” course.
The outer query works similarly and filters out all the all tuples where
the Students Enrollment Number matches with the Enrollment Number where the
course ID’s are for the course names “DBMS”
(S.EnrollNo = E.EnrollNo AND C.Name =”DBMS” AND E.CourseID = C.CourseId) and
additionally matches with the ones that are returned by the inner query i.e.
Enrollment Number of students who are enrolled for the course “OS”.
Hence the above queries returns names of all students (SELECT S.Name) who have enrolled for both courses “DBMS” and “OS”. Hence option (B). This explanation has been contributed by Yashika Arora.
Quiz of this Question
Please comment below if you find anything wrong in the above post
Last Updated :
28 Jun, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...