SQL Exercise: Employees who earn less than employee 182

Last update on December 20 2024 11:42:17 (UTC/GMT +8 hours)

7. From the following table, write a SQL query to find the employees who earn less than the employee of ID 182. Return first name, last name and salary.

Sample table: employees

Sample Solution:

-- Selecting specific columns (E.first_name, E.last_name, E.salary) from the 'employees' table, aliased as 'E'
SELECT E.first_name, E.last_name, E.salary 

-- Performing an INNER JOIN between the 'employees' table (aliased as 'E') and the 'employees' table (aliased as 'S') based on the condition that 'E.salary' is less than 'S.salary' and 'S.employee_id' is 182
FROM employees E 

JOIN employees S
  ON E.salary < S.salary 
   AND S.employee_id = 182;

Sample Output:

first_name	last_name	salary
James		Landry		2400.00
Steven		Markle		2200.00
TJ		Olson		2100.00
Ki		Gee		2400.00
Hazel		Philtanker	2200.00

Code Explanation:

The said query in SQL that selects the first name, last name, and salary of all employees who earn less than the employee with ID 182. The query retrieves data from the employees table.
The JOIN clause joins the employees table with itself using the salary column, which is used to compare salaries between employees. This type of join is known as a self-join.
The ON clause specifies the join condition, which is that the salary of the employee from the left table (E) must be less than the salary of the employee with ID 182 from the right table (S).

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation:

Alternative Solutions:

Using a Subquery with Comparison Operator:

SELECT E.first_name, E.last_name, E.salary
FROM employees E
WHERE E.salary < (SELECT salary FROM employees WHERE employee_id = 182);

Using a Correlated Subquery:

SELECT E.first_name, E.last_name, E.salary
FROM employees E
WHERE E.salary < (
    SELECT S.salary
    FROM employees S
    WHERE S.employee_id = 182
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Departments which does not have any employee.
Next SQL Exercise: Employees including the first name of their manager.