Correct Answer - Option 4 : Average salary of male employees is more than the average salary of the organization
The correct answer is "option 4".
QUERY
SELECT Deptt FROM Emp // return department from Emp table
WHERE sex = ‘M’ // of all male employees
GROUP by Dept // group by department name
Having avg (Salary) > { select avg (Salary) from Emp} // all male employees whose salary > average salary of the organization
CALCULATION:
Consider the following table:
Department
|
Salary
|
Sex
|
A
|
2000
|
M
|
B
|
2100
|
M
|
C
|
1000
|
F
|
C
|
4000
|
M
|
B
|
1200
|
F
|
C
|
2400
|
M
|
A
|
2300
|
M
|
B
|
2600
|
M
|
B
|
1800
|
F
|
A
|
2500
|
M
|
-
|
21900 (total)
|
-
|
The average salary of all the Employees are: \(\frac{21900}{10}\) = 2190
The SQL query -
SELECT Deptt FROM Emp
WHERE sex = ‘M’
GROUP by Dept
will produce the table :
Department
|
Salary
|
Sex
|
A
|
2000
|
M
|
A
|
2300
|
M
|
A
|
2500
|
M
|
B
|
2100
|
M
|
B
|
2600
|
M
|
C
|
4000
|
M
|
C
|
2400
|
M
|
Now this query line will give table :
Having avg (Salary) > { select avg (Salary) from Emp}
Department
|
Total Salary
|
AVG Salary
|
A
|
2000 + 2300 + 2500 = 6800
|
\(\frac{6800}{3}\) = 2266
|
B
|
2100 + 2600 = 4700
|
\(\frac{4700}{2}\) = 2350
|
C
|
4000 + 2400 = 6400
|
\(\frac{6400}{2}\)= 3200
|
Final output of the query will be :
Hence, this query will return the department name in which the salary of male employees is greater than the average salary of all the employees in the organization.