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

Given the query
SELECT s.rollno FROM student s WHERE s.sex = ALL (SELECT f.sex FROM professor f WHERE f.empid = s.advisor);
which retrieves students whose gender is the same as their advisor's. Consider:
S1. It has a correlated subquery.
S2. It has an uncorrelated subquery.
S3. If we replace ALL by ANY, the result will be different.
S4. If we replace ALL by ANY, the result will be the same.
Identify the TRUE statements:

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, so it is correlated (S1). For a student with no advisor (NULL), the subquery is empty: '= ALL (empty)' is TRUE but '= ANY (empty)' is FALSE, so the results differ (S3).

More Database Management Systems questions