w3resource

MySQL Subquery Exercises: Find the name and salary of the employees whose salary is greater than the average salary

MySQL Subquery: Exercise-5 with Solution

Write a MySQL query to find the name (first_name, last_name), and salary of the employees whose salary is greater than the average salary.

Sample table: employees


Code:

-- Selecting the first name, last name, and salary of employees 
SELECT first_name, last_name, salary 
-- Selecting data from the employees table
FROM employees 
-- Filtering the result set to include only employees whose salary is greater than the average salary of all employees
WHERE salary >
    -- Subquery to calculate the average salary from the employees table
    (SELECT AVG(salary) FROM employees);

Explanation:

  • This MySQL code selects the first name, last name, and salary of employees from a table named "employees".
  • It filters the results to only include employees whose salary is greater than the average salary of all employees.
  • This is achieved by using a subquery to calculate the average salary from the "employees" table, and then comparing each employee's salary with this average salary in the outer query.

MySQL Subquery Syntax:

MySQL subquery syntax

- The subquery (inner query) executes once before the main query (outer query) executes.
- The main query (outer query) use the subquery result.

MySQL SubQueries: Find the names, salary of the employees whose salary is greater than the average salary

MySQL AVG() function returns the average value of a set of values or an expression.

 

MySQL Code Editor:

Structure of 'hr' database :

hr database

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

Previous:Write a query to find the name (first_name, last_name) of the employees who are managers.
Next:Write a MySQL query to find the name (first_name, last_name), and salary of the employees whose salary is equal to the minimum salary for their job grade.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.