Skip to content
GATEiit

GATE CSE 2021 Set 2 Q41: Meaning of a GROUP BY query with an uncorrelated subquery

Question

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) (key: empId)

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

Try it: multiple choice: exactly one correct option

Select one option
Wrong answer costs 2/3 mark in GATE.
Show answer and explanation

Answer: B (official GATE 2021 final answer key)

Concept used: An uncorrelated subquery is evaluated once over its own FROM clause, independent of the outer WHERE and GROUP BY.

First step: Look at the subquery alone: it has no WHERE clause and no reference to the outer row, so it averages over every row of emp.

Full step-by-step solution

Detailed working, common mistakes and linked mock tests will be available with a free GATEiit account. Log-in is coming soon.

Share this question

WhatsAppDownload image

Source: official GATE 2021 Set 2 CS question paper and final answer key, organised by IIT Bombay. Question paper · Answer key

More Databases PYQs

PracticePractise Databases questions →Full paperAll GATE CSE 2021 Set 2 questions →