Page 115 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

JOIN departments ON employees.department id = departments.id

GROUP BY departments.name;
3 How to retrieve the average salary of employees in each department?
SELECT departments.name AS department, AVG(salary) AS average salary
FROM employees
JOIN departments ON employees.department id = departments.id

GROUP BY departments.name;
4 How to find the employees who earn more than the average salary in their department?
SELECT name, salary
FROM employees

WHERE salary > (
SELECT AVG(salary)
FROM employees AS e2
WHERE e2.department id = employees.department id
);
5 How to retrieve departments with the highest average salary?

SELECT name AS department, AVG(salary) AS average salary
FROM employees
JOIN departments ON employees.department id = departments.id
GROUP BY departments.name

HAVING AVG(salary) = (
SELECT MAX(avg salary)
FROM (
SELECT AVG(salary) AS avg salary
FROM employees

GROUP BY department id
) AS department avg
);

100
CITS : IT & ITES - Computer Software Application - Exercise 29