SQL Subquery Exercises: Display the first and last name, salary, and department ID for all those employees who earn more than the average salary and arrange the list in descending order on salary

SQL SUBQUERY: Exercise-36 with Solution

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

Sample Solution:

SELECT first_name, last_name , salary, department_id 
  FROM employees 
    WHERE salary > (
                SELECT AVG(salary)
                   FROM employees )
    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

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find those departments, located in the city ‘London’. Return department ID, department name.
Next: From the following table, write a SQL query to find those employees who earn more than the maximum salary of a department of ID 40. Return first name, last name and department ID.