Skip to content
GATETECH
Official PYQ · 2021Set 2 · Q31Tuple Calculus and SQLMCQ2 marks

GATE CS 2021 Set 2 Q31 — Tuple Calculus and SQL

Explain with AI

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

emp(empId‾, name, gender, salary, deptId)\textsf{emp($\underline{\textsf{empId}}$, name, gender, salary, deptId)}

Consider the following SQL\text{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

Choose one option
View worked solution

Answer: B

To understand what the SQL query computes, let us analyze the subquery and the outer query step-by-step:

  1. The Subquery: 
    (select avg(salary) from emp)
    This subquery calculates the average salary of all tuples in the emp relation. Since there is no WHERE clause or correlation with the outer query, it computes the average salary of all employees in the company.
  2. The Outer Query's WHERE Clause: 
    where gender = "female" and salary > (subquery)
    This filters the emp relation to keep only those employees who are:
    • Female (gender = "female"), AND
    • Have a salary strictly greater than the average salary of all employees in the company.
  3. The GROUP BY and SELECT Clause: 
    select deptId, count(*)
from ...
group by deptId;
    This groups the filtered female employees by their department (deptId) and counts how many such employees exist in each department.

Thus, the query returns the number of female employees in each department whose salary is greater than the average salary of employees in the company.

Therefore, the correct option is B.

Free practice · Your answers on this page are not saved.

Published
Report a correction
Keep exploringMore Tuple Calculus and SQL questions