Page 115 - Computer Software Application TP - Volume 1
P. 115
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