SQL Exercises: Employees who earn more than the average salary
From the following table, write a SQL query to find those employees who earn more than the average salary. Return employee ID, first name, last name.
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 (employee_id, first_name, last_name) from the 'employees' table
SELECT employee_id, first_name, last_name
-- 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
);
Sample Output:
employee_id first_name last_name 100 Steven King 101 Neena Kochhar 102 Lex De Haan 103 Alexander Hunold 108 Nancy Greenberg 109 Daniel Faviet 110 John Chen 111 Ismael Sciarra 112 Jose Manuel Urman 113 Luis Popp 114 Den Raphaely 120 Matthew Weiss 121 Adam Fripp 122 Payam Kaufling 123 Shanta Vollman 145 John Russell 146 Karen Partners 147 Alberto Errazuriz 148 Gerald Cambrault 149 Eleni Zlotkey 150 Peter Tucker 151 David Bernstein 152 Peter Hall 153 Christopher Olsen 154 Nanette Cambrault 155 Oliver Tuvault 156 Janette King 157 Patrick Sully 158 Allan McEwen 159 Lindsey Smith 160 Louise Doran 161 Sarath Sewall 162 Clara Vishney 163 Danielle Greene 164 Mattea Marvins 165 David Lee 168 Lisa Ozer 169 Harrison Bloom 170 Tayler Fox 171 William Smith 172 Elizabeth Bates 174 Ellen Abel 175 Alyssa Hutton 176 Jonathon Taylor 177 Jack Livingston 178 Kimberely Grant 201 Michael Hartstein 203 Susan Mavris 204 Hermann Baer 205 Shelley Higgins 206 William Gietz
Code Explanation:
The said query in SQL that retrieves the employee id, first name, and last name of employees in the 'employees' table, where the salary is greater than the average salary of all employees in the table. In other words, it returns the employee id, first name, and last name of employees who earn more than the average salary among all employees.
Visual Presentation:
Alternative Statement:
Using JOIN:
SELECT e.employee_id, e.first_name, e.last_name
FROM employees e
JOIN (
SELECT AVG(salary) as avg_salary
FROM employees
) avg_emp ON e.salary > avg_emp.avg_salary;
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Contribute your code and comments through Disqus.
Previous SQL Exercise: Smallest salary of any of the departments.
Next SQL Exercise: Salary of all employees who report to Payam.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.