Nested and Correlated Subqueries MCQs: 12 Solved SQL Questions with Explanations

Result shape, correlation, duplicate handling, and quantified comparisons determine how SQL subqueries behave. The earlier broader SQL revision set owns the survey-level introduction to maximum-by-ALL and unique-price ranking; these 12 questions focus on empty-set quantifiers, tied ranks, duplicate multiplication, and per-row correlation.

Three-pass reading method

For each query, mark the outer row, classify the inner result, then apply duplicate and empty-set rules.

Q9 check for {100,200}: 150 > ANY true; 150 > ALL false; NOT EXISTS false since 200 >= 150.

Questions 1 to 3

Q1. Company-wide average

The relation scheme given below is used to store information about the employees of a company, where empId is the key and deptId indicates the department to which the employee is assigned. Each employee is assigned to exactly one department.

emp(empId, name, gender, salary, deptId)

Consider the following SQL query:

select deptId, count(*)
from emp
where gender = 'female' and salary > (select avg(salary) from emp)
group by deptId;

The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of

• A. employees in the department.

• B. employees in the company.

• C. female employees in the department.

• D. female employees in the company.

Answer: B. The uncorrelated inner query returns the company-wide average. The outer query counts qualifying women by department.

Q2. Row-value IN

Given the following schema:

employees(emp_id, first_name, last_name, hire_date, dept_id, salary)

departments(dept_id, dept_name, manager_id, location_id)

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SELECT last_name, hire_date
FROM employees
WHERE (dept_id, hire_date) IN
      (SELECT dept_id, MAX(hire_date)
       FROM employees JOIN departments USING(dept_id)
       WHERE location_id = 1700
       GROUP BY dept_id);

What is the outcome?

• A. It executes but does not give the correct result.

• B. It executes and gives the correct result.

• C. It generates an error because of pairwise comparison.

• D. It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.

Answer: B. The outer row must match one returned (department,date) pair. Latest-date ties remain in this dialect.

Q3. NATURAL JOIN multiplicity

Database table by name Loan_Records is given below.

What is the output of the following SQL query?

SELECT count(*)
FROM (
  SELECT Borrower, Bank_Manager FROM Loan_Records) AS S
NATURAL JOIN
  (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T;

• A. 3

• B. 9

• C. 5

• D. 6

Answer: C. Sunderajan gives 2 x 2 = 4 rows. Ramgopal gives 1 x 1 = 1, totalling five.

Questions 4 to 6

Q4. IN versus join row multiplication

Consider Sale(sale_id, region) holding the rows (1,'North'), (2,'North'), (3,'East'), (4,'West'), (5,'South') and Target(region) holding the rows 'North', 'North', 'East', 'East', 'East', 'Central'. How many rows does Query 1 return, and how many does Query 2 return? Duplicate rows produced by the join count separately.

Query 1:
SELECT S.*
FROM Sale S
WHERE S.region IN (SELECT T.region FROM Target T);

Query 2:
SELECT S.*
FROM Sale S
JOIN Target T ON S.region = T.region;

• A. Query 1 returns 3 rows; Query 2 returns 3 rows.

• B. Query 1 returns 7 rows; Query 2 returns 7 rows.

• C. Query 1 returns 3 rows; Query 2 returns 7 rows.

• D. Query 1 returns 7 rows; Query 2 returns 3 rows.

Answer: C. IN keeps two North rows and one East row: 2 + 1 = 3. The join multiplies matches: 2 x 2 + 1 x 3 = 7.

Q5. EXISTS, COUNT and NATURAL JOIN

Consider the following relational schema:

Students (rollno: integer, name: string, age: integer, cgpa: real)

Courses (courseno: integer, cname: string, credits: integer)

Enrolled (rollno: integer, courseno: integer, grade: string)

Which queries retrieve the names of students enrolled in course 1470?

• A.

  SELECT S.name
  FROM Students S
  WHERE EXISTS (SELECT * FROM Enrolled E
                WHERE E.courseno = 1470
                AND E.rollno = S.rollno);

• B.

  SELECT S.name
  FROM Students S
  WHERE SIZEOF (SELECT * FROM Enrolled E
                WHERE E.courseno = 1470
                AND E.rollno = S.rollno) > 0;

• C.

  SELECT S.name
  FROM Students S
  WHERE 0 < (SELECT COUNT(*) FROM Enrolled E
             WHERE E.courseno = 1470
             AND E.rollno = S.rollno);

• D.

  SELECT S.name
  FROM Students S NATURAL JOIN Enrolled E
  WHERE E.courseno = 1470;

Answers: A, C and D. A and C test membership; SIZEOF is non-standard. D works here, but explicit JOIN ON rollno is safer than NATURAL JOIN.

Q6. IN, EXISTS and multiplicity

In enrolled(student, course), the pair is the primary key. In paid(student, amount), student is the primary key. Assume no NULL values, foreign keys, or other integrity constraints.

Query1: select student from enrolled where
student in (select student from paid)

Query2: select student from paid where
student in (select student from enrolled)

Query3: select E.student from enrolled E, paid P
where E.student = P.student

Query4: select student from paid where exists
(select * from enrolled where enrolled.student = paid.student)

Which statement is correct when duplicate result rows are counted?

• A. All queries return identical row results for every database.
• B. Query2 and Query4 always return identical row results, but Query1 and Query2 can return different row counts.
• C. Query3 can return strictly fewer rows than Query2.
• D. Query4 can encounter an integrity violation at runtime.

Answer: B. With enrolled={(Alice,Math),(Alice,CS)} and paid={(Alice,100)}, Query1/Query3 output Alice twice. Query2/Query4 output her once because paid.student is primary.

Questions 7 to 9

Q7. NOT EXISTS and universal logic

Consider the following relational schema:

employee (empId,empName,empDept)

customer (custId,custName,salesRepId,rating)

salesRepId refers to employee.empId. Assume every employee has at least one customer and rating is never NULL. What does the query return?

SELECT empName FROM employee E
WHERE NOT EXISTS (SELECT custId
                  FROM customer C
                  WHERE C.salesRepId = E.empId
                  AND C.rating <> 'GOOD');

• A. Names of all the employees with at least one of their customers having a 'GOOD' rating.

• B. Names of all the employees with at most one of their customers having a 'GOOD' rating.

• C. Names of all the employees with none of their customers having a 'GOOD' rating.

• D. Names of all the employees with all their customers having a 'GOOD' rating.

Answer: D. NOT EXISTS rejects a non-GOOD counterexample. The at-least-one-customer assumption prevents customerless employees passing vacuously.

Q8. ALL, ANY and an empty set

Rule(rule_id, limit_value, active) contains (1, 10, FALSE), (2, 20, FALSE). The subquery SELECT limit_value FROM Rule WHERE active = TRUE is empty. For x = 15, which pair is TRUE under standard SQL? I. x > ALL (subquery); II. x > ANY (subquery); III. EXISTS (subquery); IV. NOT EXISTS (subquery).

• A. I and IV

• B. I and II

• C. II and III

• D. III and IV

Answer: A. A comparison against ALL (empty) is vacuously true, while ANY (empty) is false. The empty subquery also makes EXISTS false and NOT EXISTS true.

Q9. NOT EXISTS versus ANY

For employee(empId, name, department, salary), assume department 5 has more than one employee and every salary is non-NULL. Which query finds employees paid more than every employee in department 5?

Q1: Select e.empId
    From employee e
    Where not exists
    (Select * From employee s where s.department = '5' and
     s.salary >= e.salary)

Q2: Select e.empId
    From employee e
    Where e.salary > Any
    (Select distinct salary From employee s Where s.department = '5')

• A. Q1 is the correct query

• B. Q2 is the correct query

• C. Both Q1 and Q2 produce the same answer.

• D. Neither Q1 nor Q2 is the correct query

Answer: A. Against {100,200}, 150 passes > ANY but fails Q1 because 200 >= 150. At 250, no counterexample remains, so Q1 passes.

Questions 10 to 12

Q10. Correlated company average

Consider the following relation:

Works(emp_name, company_name, salary)

Here, emp name is primary key. Consider the following SQL query:

Select emp_name
From works T
where salary > (select avg(salary)
                from works S
                where T.company_name = S.company_name)

The above query is for following:

• A. Find the highest paid employee who earns more than the average salary of all employees of his company.

• B. Find the highest paid employee who earns more than the average salary of all the employees of all the companies.

• C. Find all employees who earn more than the average salary of all employees all the companies.

• D. Find all employees who earn more than the average salary of all employees of their company.

Answer: D. For A={40,60,80}, average 60 leaves 80; B={50,70}, average 60 leaves 70. Correlation recomputes by company and returns every above-average employee.

Q11. Correlated COUNT with tied ranks

Result(candidate, score) contains A=90, B=80, C=80, and D=70. What does this query return?

SELECT candidate FROM Result AS R
WHERE (SELECT COUNT(DISTINCT T.score)
       FROM Result AS T
       WHERE T.score > R.score) < 2;

• A. A only

• B. A and B

• C. A, B and C

• D. A, B, C and D

Answer: C. A has zero distinct higher scores. B and C each have one, 90, while D has two, 80 and 90. The condition keeps the top two distinct score bands, including the tie at 80.

Q12. NOT UNIQUE duplicates

Supply(supplierid, itemcode) records supplier items. Inventory(itemcode, warehouse, stocklevel) records stock by warehouse.

What does this query find for the Nagpur warehouse?

Select distinct STMP.supplierid
From Supply as STMP
Where not unique (Select ITMP.supplierid
                  From Inventory, Supply as ITMP
                  Where STMP.supplierid = ITMP.supplierid
                  And ITMP.itemcode = Inventory.itemcode
                  And Inventory.warehouse = 'Nagpur');

• A. do not supply any item

• B. supply exactly one item

• C. supply one or more items

• D. supply two or more items

Answer: D. If S1 matches I10 and I20, {S1,S1} is not unique; zero/one match is unique. The UNIQUE predicate is dialect-specific.

Closing checklist

Identify correlation, classify the inner result, check duplicates and empty sets, then trace one outer row. IN and EXISTS test membership; ANY and ALL quantify comparisons; correlated counts can rank.

Continue through the Nested and Correlated Subqueries practice hub for the full bank view. The CS Fundamentals for Placements hub groups related sets, while Computer Science Fundamentals for Placements by Sanchit Sir teaches the wider syllabus.