SQL Exercises: Employees earn more than average salary in ASC order
From the following table, write a SQL query to find those employees who earn more than the average salary. Sort the result-set in descending order by salary. Return first name, last name, salary, and department ID.
Sample table: employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | .................... +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample Solution:
-- Selecting specific columns (first_name, last_name, salary, department_id) from the 'employees' table
SELECT first_name, last_name, salary, department_id
-- Filtering rows based on the condition that the 'salary' is greater than the average salary in the 'employees' table
FROM employees
-- Subquery to calculate the average salary from the 'employees' table
WHERE salary > (
SELECT AVG(salary)
FROM employees )
-- Ordering the result set based on the 'salary' column in descending order
ORDER BY salary DESC;
Sample Output:
first_name last_name salary department_id Steven King 24000.00 90 Neena Kochhar 17000.00 90 Lex De Haan 17000.00 90 John Russell 14000.00 80 Karen Partners 13500.00 80 Michael Hartstein 13000.00 20 Shelley Higgins 12000.00 110 Alberto Errazuriz 12000.00 80 Nancy Greenberg 12000.00 100 Lisa Ozer 11500.00 80 Gerald Cambrault 11000.00 80 Ellen Abel 11000.00 80 Den Raphaely 11000.00 30 Clara Vishney 10500.00 80 Eleni Zlotkey 10500.00 80 Peter Tucker 10000.00 80 Hermann Baer 10000.00 70 Janette King 10000.00 80 Harrison Bloom 10000.00 80 Tayler Fox 9600.00 80 Danielle Greene 9500.00 80 David Bernstein 9500.00 80 Patrick Sully 9500.00 80 Daniel Faviet 9000.00 100 Peter Hall 9000.00 80 Allan McEwen 9000.00 80 Alexander Hunold 9000.00 60 Alyssa Hutton 8800.00 80 Jonathon Taylor 8600.00 80 Jack Livingston 8400.00 80 William Gietz 8300.00 110 Adam Fripp 8200.00 50 John Chen 8200.00 100 Christopher Olsen 8000.00 80 Matthew Weiss 8000.00 50 Lindsey Smith 8000.00 80 Payam Kaufling 7900.00 50 Jose Manuel Urman 7800.00 100 Ismael Sciarra 7700.00 100 Louise Doran 7500.00 80 Nanette Cambrault 7500.00 80 William Smith 7400.00 80 Elizabeth Bates 7300.00 80 Mattea Marvins 7200.00 80 Sarath Sewall 7000.00 80 Oliver Tuvault 7000.00 80 Kimberely Grant 7000.00 0 Luis Popp 6900.00 100 David Lee 6800.00 80 Susan Mavris 6500.00 40 Shanta Vollman 6500.00 50
Code Explanation:
The said query in SQL that retrieves the first name, last name, salary, and department ID from the 'employees' table where the salary is greater than the average salary of all employees. Using the AVG function in the WHERE clause, the average salary is calculated for all employees.
After filtering the 'employees' table, the main query selects only the rows with salaries greater than the average.
The resulting data is then ordered in descending order by salary, so that the employees with the highest salaries appear first in the result set.
Visual Presentation:
Alternative Statements:
Using Correlated Subquery:
SELECT first_name, last_name, salary, department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
)
ORDER BY salary DESC;
Using JOIN with AVG Calculation:
SELECT e1.first_name, e1.last_name, e1.salary, e1.department_id
FROM employees e1
JOIN (
SELECT AVG(salary) as avg_salary
FROM employees
) e2
ON e1.salary > e2.avg_salary
ORDER BY e1.salary DESC;
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Departments which located in the city London.
Next SQL Exercise: Employees earn more than the maximum salary.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.