w3resource logo


PostgreSQL exercises

PostgreSQL Subquery - Exercises, Practice, Solution

Secondary Nav

PostgreSQL Subquery [21 exercises with solution]

1. Write a query to find the names (first_name, last_name) and salaries of the employees who have higher salary than the employee whose last_name='Bull'.

Go to editor

SELECT first_name, last_name, salary 
FROM employees 
WHERE salary > 
(SELECT salary 
FROM employees 
WHERE last_name = 'Bull');

Sample table : employees

2. Find the names (first_name, last_name) of all employees who works in the IT department.

Go to editor

SELECT first_name, last_name 
FROM employees 
WHERE department_id 
IN (SELECT department_id 
FROM departments 
WHERE department_name='IT');

Sample table : employees

3. Find the names (first_name, last_name) of the employees who have a manager who works for a department based in United States.

Go to editor

SELECT first_name, last_name 
FROM employees 
WHERE manager_id in (select employee_id 
FROM employees WHERE department_id 
IN (SELECT department_id 
FROM departments 
WHERE location_id 
IN (select location_id 
FROM locations 
WHERE country_id='US')));

Hint : Write single-row and multiple-row subqueries

Sample table : employees

Sample table : departments

Sample table : locations

4. Find the names (first_name, last_name) of the employees who are managers.

Go to editor

SELECT first_name, last_name 
FROM employees 
WHERE (employee_id 
IN (SELECT manager_id 
FROM employees));

Sample table : employees

5. Find the names (first_name, last_name), salary of the employees whose salary is greater than the average salary.

Go to editor

SELECT first_name, last_name, salary 
FROM employees 
WHERE salary > (
SELECT AVG(salary) 
FROM employees);

Sample table : employees

6. Find the names (first_name, last_name), salary of the employees whose salary is equal to the minimum salary for their job grade.

Go to editor

SELECT first_name, last_name, salary 
FROM employees 
WHERE employees.salary = 
(SELECT min_salary 
FROM jobs 
WHERE employees.job_id = jobs.job_id); 

Sample table : employees

Sample table : jobs

7. Find the names (first_name, last_name), salary of the employees who earn more than the average salary and who works in any of the IT departments.

Go to editor

SELECT first_name, last_name, salary 
FROM employees 
WHERE department_id IN 
(SELECT department_id 
FROM departments 
WHERE department_name LIKE 'IT%') 
AND salary > (
SELECT avg(salary) 
FROM employees);

Sample table : employees

Sample table : departments

8. Find the names (first_name, last_name), salary of the employees who earn more than Mr. Bell.

Go to editor

SELECT first_name, last_name, salary 
FROM employees 
WHERE salary > 
(SELECT salary 
FROM employees 
WHERE last_name = 'Bell') 
ORDER BY first_name;

Sample table : employees

Sample table : departments

9. Find the names (first_name, last_name), salary of the employees who earn the same salary as the minimum salary for all departments.

Go to editor

SELECT * 
FROM employees 
WHERE salary = (
SELECT MIN(salary) 
FROM employees);

Sample table : employees

Sample table : departments

10. Find the names (first_name, last_name), salary of the employees whose salary greater than average salary of all department.

Go to editor

SELECT * 
FROM employees 
WHERE salary > 
ALL(SELECT avg(salary) 
FROM employees 
GROUP BY department_id);

Sample table : employees

11. Write a query to find the names (first_name, last_name) and salary of the employees who earn a salary that is higher than the salary of all the Shipping Clerk (JOB_ID = 'SH_CLERK'). Sort the results on salary from the lowest to highest.

Go to editor

SELECT first_name,last_name, job_id, salary 
FROM employees 
WHERE salary > 
ALL (SELECT salary 
FROM employees 
WHERE job_id = 'SH_CLERK') 
ORDER BY salary ;

Sample table : employees

12. Write a query to find the names (first_name, last_name) of the employees who are not supervisors.

Go to editor

SELECT b.first_name,b.last_name 
FROM employees b 
WHERE NOT EXISTS (
SELECT 'X' 
FROM employees a 
WHERE a.manager_id = b.employee_id);

Sample table : employees

13. Write a query to display the employee ID, first name, last names, and department names of all employees.

Go to editor

SELECT employee_id, first_name, last_name, 
(SELECT department_name 
FROM departments d 
WHERE e.department_id = d.department_id) 
department FROM employees e 
ORDER BY department;

Sample table : employees

Sample table : departments

14. Write a query to display the employee ID, first name, last names, salary of all employees whose salary is above average for their departments.

Go to editor

SELECT employee_id, first_name 
FROM employees AS A 
WHERE salary > 
( SELECT AVG(salary) 
FROM employees 
WHERE department_id = A.department_id); 

Sample table : employees

Sample table : departments

15. Write a query to find the 5th maximum salary in the employees table.

Go to editor

SELECT DISTINCT salary 
FROM employees e1 
WHERE 5 = (SELECT COUNT(DISTINCT salary) 
FROM employees  e2 
WHERE e1.salary <= e2.salary); 

16. Write a query to find the 4th minimum salary in the employees table.

Go to editor

SELECT DISTINCT salary 
FROM employees e1 
WHERE 4 = (SELECT COUNT(DISTINCT salary) 
FROM employees  e2 
WHERE e1.salary >= e2.salary);

17. Write a query to select last 10 records from a table.

Go to editor

SELECT * FROM (
SELECT * FROM employees 
ORDER BY employee_id DESC LIMIT 10) sub 
ORDER BY employee_id ASC;

18. Write a query to list department number, name for all the departments in which there are no employees in the department.

Go to editor

SELECT * FROM departments 
WHERE department_id 
NOT IN (select department_id 
FROM employees); 

Sample table : employees

Sample table : departments

19. Write a query to get 3 maximum salaries.

Go to editor

SELECT DISTINCT salary 
FROM employees a 
WHERE  3 >= (SELECT COUNT(DISTINCT salary) 
FROM employees b 
WHERE a.salary <= b.salary) 
ORDER BY a.salary DESC;

Sample table : employees

20. Write a query to get 3 minimum salaries.

Go to editor

SELECT DISTINCT salary 
FROM employees a 
WHERE  3 >= (SELECT COUNT(DISTINCT salary) 
FROM employees b 
WHERE a.salary >= b.salary) 
ORDER BY a.salary DESC;

Sample table : employees

21. Write a query to get nth max salaries of employees.

Go to editor

SELECT * 
FROM employees emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(emp2.salary))
FROM employees emp2
WHERE emp2.salary > emp1.salary);

Sample table : employees

...More

Structure of 'hr' database :

hr database

More .....

Practice Online


Go To Top