w3resource

PostgreSQL Subquery: Find the name and salary of the employees who draws the same salary as the minimum salary for all departments


9. Write a SQL subquery to find all the information of the employees who draws the same salary as the minimum salary for all departments.

Sample Solution:

Code:



-- This SQL query retrieves all columns of the employee(s) with the minimum salary in the employees table.

SELECT * -- Selects all columns from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE salary = ( -- Filters the rows to include only those where the salary matches the minimum salary obtained from the subquery
    SELECT MIN(salary) -- Subquery: Calculates the minimum salary among all employees
    FROM employees
);

Explanation:

  • This SQL query retrieves all columns of the employee(s) with the minimum salary in the employees table.
  • The outermost SELECT statement selects all columns from the employees table.
  • The WHERE clause filters the rows to include only those where the salary matches the minimum salary obtained from the subquery.
  • The subquery calculates the minimum salary among all employees using the MIN() function.

Sample table: employees


Sample table: departments


Output:

pg_exercises=# SELECT *
pg_exercises-# FROM employees
pg_exercises-# WHERE salary = (
pg_exercises(# SELECT MIN(salary)
pg_exercises(# FROM employees);

 employee_id | first_name | last_name |     email     | phone_number | hire_date  |  job_id  | salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+----------+---------+----------------+------------+---------------
         132 | TJ         | Olson     | not available | 650.123.8234 | 1987-07-19 | ST_CLERK | 2100.00 |           0.00 |        121 |            50
(1 row)

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 of the employees who draw a more salary than the employee, which the last name is Bell.
Next: Write a SQL subquery to find all the information of the employees whose salary greater than the average salary of all departments.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/postgresql-exercises/subquery/postgresql-subquery-exercise-9.php