The GATE Grind

GATE 2021 CS (CS2) – Question 41

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Multiple choice

emp(empId,name,gender,salary,deptId) has key empId, and each employee belongs to one department. What does this query count for each department?

SELECT deptId,COUNT(*) FROM emp
WHERE gender='female'
  AND salary>(SELECT AVG(salary) FROM emp)
GROUP BY deptId;

It counts female employees earning more than the average salary of whom?

  1. Employees in that department
  2. Employees in the company
  3. Female employees in that department
  4. Female employees in the company

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (B) Employees in the company

Explanation

The subquery is uncorrelated and has neither department nor gender filtering, so it computes the company-wide average over all employees. The outer query filters women above that average and groups qualifying rows by department. Thus B; departments without qualifying rows do not appear.

More Relational Model: Relational Algebra, Tuple Calculus, SQL questions

All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →