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.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
SQL: Tips of the Day
"where 1=1" statement?
It's usually when folks build up SQL statements.
When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.
Ref: https://bit.ly/3cayyhJ
- Exercises: Weekly Top 12 Most Popular Topics
- Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- JavaScript: HTML Form Validation
- SQL Exercises, Practice, Solution - SUBQUERIES
- C Programming Exercises, Practice, Solution : For Loop
- Python Exercises, Practice, Solution
- Python Data Type: List - Exercises, Practice, Solution
- C++ Basic: Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - exercises on Employee Database
- SQL Exercises, Practice, Solution - exercises on Movie Database
- SQL Exercises, Practice, Solution - exercises on Soccer Database
- C Programming Exercises, Practice, Solution : Recursion