The Binary BrothersTwo Mentors, One Mission – Your Selection
All mock tests are FREE till 31 December — register to save your progress
Unit 4 · Database Management Systems Paper 2 CS · December 2022 (Shift 1)

Directions (Q96–100): Academic database schema: student(rollno, name, degree, year, sex, deptno, advisor); department(deptid, name, hod, phone); professor(empid, name, sex, startyear, deptno, phone); course(courseid, cname, credits, deptno); enrollment(rollno, courseid, sem, year, grade); teaching(empid, courseid, sem, year, classroom); prerequisite(precourseid, courseid). deptno in student, professor and course refers to department.deptid; advisor refers to professor.empid; hod refers to professor.empid; enrollment.rollno refers to student.rollno; courseid in enrollment and teaching refers to course.courseid; teaching.empid refers to professor.empid; precourseid and courseid in prerequisite refer to course.courseid.

Given query: select s.rollno from students where s.sex = ALL (select f.sex from professor f where f.empid = s.advisor). Consider the following statements about the given query that retrieves the set of all students whose gender is same as the gender of their advisor: S1. It has a correlated subquery S2. It has an uncorrelated subquery S3. If we replace ALL by ANY in the above query, its result will be different S4. If we replace ALL by ANY in the above query, its result will be same. Identify all the statement that are TRUE:

Pick an option to check yourself, or open the answer below.

Show answer & explanation

Correct answer: A. S1 and S3 are true

Explanation

The subquery refers to s.advisor from the outer query, so it is correlated (S1). If a student has no matching advisor the subquery is empty: '= ALL' is then vacuously true but '= ANY' is false, so the results differ (S3).

More Database Management Systems questions