Skip to content
GATETECH
Official PYQ · 2004Q53Tuple Calculus and SQLMCQ1 mark

GATE CS 2004 Q53 — Tuple Calculus and SQL

Explain with AI

The employee information in a company is stored in the relation

  • Employee (name, sex, salary, deptName)

Consider the following SQL query

Select deptName
    From Employee
    Where sex = ‘M’
    Group by deptName
    Having avg(salary) >
        (select avg (salary) from Employee)

It returns the names of the department in which

Choose one option
View worked solution

Answer: D

To understand what the SQL query returns, let us break it down step-by-step:

  1. The WHERE clause: 
    Where sex = 'M'
    This filters the Employee relation to retain only male employees before any grouping takes place.
  2. The GROUP BY clause: 
    Group by deptName
    This groups the filtered male employees by their department (deptName).
  3. The HAVING clause: 
    Having avg(salary) > ...
    Since the grouping is done on the filtered set (only male employees), avg(salary) here computes the average salary of male employees in each department.
  4. The Subquery: 
    (select avg (salary) from Employee)
    This subquery has no WHERE clause, so it computes the average salary of all employees in the entire company.

Combining these parts, the query selects the deptName of departments where the average salary of male employees is greater than the average salary of all employees in the company.

Therefore, the correct option is D.

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

Published
Report a correction
Keep exploringMore Tuple Calculus and SQL questions