PostgreSQL Subquery: Find the name and salary which is equal to the minimum salary for this post he/she is working on

6. Write a SQL subquery to find the first_name, last_name and salary, which is equal to the minimum salary for this post, he/she is working on.

Sample Solution:

Code:

-- This SQL query retrieves the first name, last name, and salary of employees whose salary matches the minimum salary defined for their job.

SELECT first_name, -- Selects the first_name column from the employees table
       last_name, -- Selects the last_name column from the employees table
salary -- Selects the salary column from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE employees.salary = ( -- Filters the rows to include only those where the employee's salary matches the minimum salary defined for their job
    SELECT min_salary -- Subquery: Selects the minimum salary defined for the employee's job
    FROM jobs -- Specifies the table from which to retrieve data, in this case, the jobs table
    WHERE employees.job_id = jobs.job_id -- Matches the job_id of employees with the job_id of jobs to ensure correct job-to-salary comparison
);

Explanation:

• This SQL query retrieves the first name, last name, and salary of employees whose salary matches the minimum salary defined for their job.
• The outer SELECT statement retrieves the first name, last name, and salary from the employees table.
• The WHERE clause filters the rows to include only those where the employee's salary matches the minimum salary obtained from the subquery.
• The subquery selects the minimum salary defined for the employee's job from the jobs table, ensuring that the correct job-to-salary comparison is made.
• The subquery is enclosed in parentheses and executed before the outer query.

Sample table: employees

Sample table: jobs

Output:

pg_exercises=# SELECT first_name, last_name, salary
pg_exercises-# FROM employees
pg_exercises-# WHERE employees.salary =
pg_exercises-# (SELECT min_salary
pg_exercises(# FROM jobs
pg_exercises(# WHERE employees.job_id = jobs.job_id);
 first_name | last_name  | salary
------------+------------+---------
 Karen      | Colmenares | 2500.00
 Martha     | Sullivan   | 2500.00
 Randall    | Perkins    | 2500.00
(3 rows)

Practice Online

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

Previous: Write a SQL subquery to find the first_name, last_name and salary, which is greater than the average salary of the employees.
Next: Write a SQL Subquery to find the first_name, last_name and salary of the employees who earn more than the average salary and works in any of the IT departments.