SQL Query

Question

Write SQL command to find DepartmentID, EmployeeName from Employee table whose average salary is above 20000.

Comments

SELECT DepartmentID, EmployeeName FROM Employee WHERE (SELECT AVG(Salary) FROM Employee) > 20000;

Answer 1

This query will select the DepartmentID and EmployeeName columns
from the Employee table
and return only the rows where the average salary of all employees
in the table is above $20000$ :-

//SQL CODE:-
SELECT DepartmentID, EmployeeName
FROM Employee
WHERE (SELECT AVG(Salary) FROM Employee) > 20000;

 

This query will select the DepartmentID and EmployeeName columns from the Employee table, and return all rows where the average salary for the department is above 20000. The subquery in the WHERE clause calculates the average salary for each department by selecting the Salary column from the Employee table, grouped by DepartmentID. The outer query then filters the results to include only rows where the average salary is above $20000$ :-

 

//SQL CODE:-
SELECT DepartmentID, EmployeeName
FROM Employee AS e
WHERE (SELECT AVG(Salary) FROM Employee WHERE DepartmentID = e.DepartmentID) > 20000

 

Comments

@gatecse Sir, yes. That was wrongly written.

---

SELECT DepartmentID FROM Employee group by DepartmentID 
having AVG(Salary) > 20000;

Select EmployeeName, DepartmentID from Employee where 
DepartmentID in (
SELECT DepartmentID FROM Employee group by DepartmentID 
having AVG(Salary) > 20000;
)

The first query will return the needed departmentIDs and the second one will return the employeenames in them.

---

@gatecse Sir, oh yes group by clause is most appropriate for this query. Thanks for pointing out the mistake.