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?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics