w3resource

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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.