GATE CS 2021 Set 2 Q.41 (CS Q.31): SQL Queries and Integrity Constraints

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.

\text{emp}(\text{empId}, \text{name}, \text{gender}, \text{salary}, \text{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

employees in the department.

employees in the company.

female employees in the department.

female employees in the company.

Official answer and solution

Official answer: B
From: Official GATE 2021 answer key

Solution

Parse the scope and filtering of the SQL subquery and main query.

Analyze the subquery:

(select avg(salary) from emp)

This subquery is uncorrelated and does not contain any WHERE or GROUP BY clause. It calculates the overall average salary across all employees in the entire emp table (the whole company).

Analyze the outer query:

The WHERE clause filters rows where gender = 'female' and salary > (overall average salary).
The GROUP BY deptId clause groups these qualifying female employees by their department and counts them (count(*)).

Therefore, the query outputs, for each department, the count of female employees whose salary exceeds the average salary of all employees in the company (matching option B).

Final answer

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2021 Computer Science and Information Technology - Session 2 (CS2)
Question: 41 of 65, printed as CS Q.31
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source