w3resource

MySQL Subquery Exercises: Find the name and salary of the employees who earns more than the employee whose last name is Bell

MySQL Subquery: Exercise-8 with Solution

Write a MySQL query to find the name (first_name, last_name), and salary of the employees who earns more than the employee whose last name is Bell.

Sample table: employees


Sample table : departments


Code:

S-- 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 higher than that of the employee with the last name 'Bell'
WHERE salary >
    -- Subquery to fetch the salary of the employee with the last name 'Bell'
    (SELECT salary FROM employees WHERE last_name = 'Bell') 
-- Sorting the result set in ascending order based on the first name of employees
ORDER BY first_name;

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 higher than the salary of the employee with the last name 'Bell'.
  • This is achieved by using a subquery to fetch the salary of the employee with the last name 'Bell', and then comparing it with the salaries of other employees in the outer query.
  • The result set is then sorted in ascending order based on the first name of employees using the ORDER BY clause.

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 who earns more than the employee whose last name is Bell.

 

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 MySQL query to find the name (first_name, last_name), and salary of the employees who earns more than the average salary and works in any of the IT departments.
Next:Write a MySQL query to find the name (first_name, last_name), and salary of the employees who earn the same salary as the minimum salary for all departments.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.