PostgreSQL JOINS - Exercises, Practice, Solution

Last update on August 19 2022 21:50:56 (UTC/GMT +8 hours)

PostgreSQL Joins [13 exercises with solution]

1. Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments.
Hint : Use NATURAL JOIN.

Go to editor

SELECT location_id, street_address, city, state_province, country_name
FROM locations
NATURAL JOIN countries;

Sample table : locations

Sample table : countries

2. Write a query to find the names (first_name, last name), department ID and name of all the employees.

Go to editor

SELECT first_name, last_name, department_id, department_name 
FROM employees 
JOIN departments USING (department_id);

Sample table : employees

Sample table : departments

3. Find the names (first_name, last_name), job, department number, and department name of the employees who work in London.

Go to editor

SELECT e.first_name, e.last_name, e.job_id, e.department_id, d.department_name 
FROM employees e 
JOIN departments d 
ON (e.department_id = d.department_id) 
JOIN locations l ON 
(d.location_id = l.location_id) 
WHERE l.city = 'London';

Sample table : departments

Sample table : locations

Sample table : employees

4. Write a query to find the employee id, name (last_name) along with their manager_id, manager name (last_name).

Go to editor

SELECT W1.employee_id as "Emp_id" , W1.last_name AS "Employee",
W2.employee_id AS "Manager ID", W2.last_name AS "Manager"
FROM employees W1 JOIN employees W2
ON W1.manager_id= W2.employee_id;

Sample table : employees

5. Find the names (first_name, last_name) and hire date of the employees who were hired after 'Jones'.

Go to editor

SELECT e.first_name, e.last_name, e.hire_date 
FROM employees e 
JOIN employees davies 
ON (davies.last_name = 'Jones') 
WHERE davies.hire_date < e.hire_date;

Sample table : employees

6. Write a query to get the department name and number of employees in the department.

Go to editor

SELECT department_name AS "Department Name", 
COUNT(*) AS "No of Employees" 
FROM departments 
INNER JOIN employees 
ON employees.department_id = departments.department_id 
GROUP BY departments.department_id, department_name 
ORDER BY department_name;

Sample table : employees

Sample table : departments

7. Find the employee ID, job title number of days between ending date and starting date for all jobs in department 90 from job history.

Go to editor

SELECT employee_id, job_title, end_date-start_date Days 
FROM job_history 
NATURAL JOIN jobs 
WHERE department_id=90;

Sample table : employees

8. Write a query to display the department ID, department name and manager first name.

Go to editor

SELECT w1.department_id, w1.department_name, w2.manager_id, w2.first_name 
FROM departments w1 
INNER JOIN employees w2 
ON (w1.manager_id = w2.employee_id);

Sample table : employees

Sample table : departments

9. Write a query to display the department name, manager name, and city.

Go to editor

SELECT w1.department_name, w2.first_name, w3.city 
FROM departments w1 
JOIN employees w2 
ON (w1.manager_id = w2.employee_id) 
JOIN locations w3 USING (location_id);

Sample table : employees

Sample table : departments

Sample table : locations

10. Write a query to display the job title and average salary of employees.

Go to editor

SELECT job_title, AVG(salary) 
FROM employees 
NATURAL JOIN jobs 
GROUP BY job_title;

Sample table : employees

11. Display job title, employee name, and the difference between salary of the employee and minimum salary for the job.

Go to editor

SELECT w2.job_title, w1.first_name, w1.salary,
w2.min_salary,(w1.salary - w2.min_salary) as "Salary - Min_Salary" 
FROM employees  w1
NATURAL JOIN jobs w2;

Sample table : employees

12. Write a query to display the job history that were done by any employee who is currently drawing more than 10000 of salary.

Go to editor

SELECT jh.* FROM job_history jh 
JOIN employees em 
ON (jh.employee_id = em.employee_id) 
WHERE em.salary > 10000;

Sample table : employees

Sample table : Job_history

13. Write a query to display department name, name (first_name, last_name), hire date, salary of the manager for all managers whose experience is more than 15 years.

Go to editor

SELECT department_name, first_name, last_name,
hire_date, salary,date_part('year',age(now(),hire_date)) Experience 
FROM departments w1 
JOIN employees w2 
ON (w1.manager_id = w2.manager_id)
WHERE date_part('year',age(now(),hire_date))>15;

Sample table : employees

Sample table : departments

Structure of 'hr' database :

More.....

Practice Online

Go To Top