PostgreSQL JOINS - Exercises, Practice, Solution

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.

Sample table: locations

Sample table: countries

Click me to see the solution

2. Write a query to make a join with employees and departments table to find the name of the employee, including first_name and last name, department ID and name of departments.

Sample table: employees

Sample table: departments

Click me to see the solution

3. Write a SQL query to make a join with three tables employees, departments and locations to find the name, including first_name and last_name, jobs, department name and ID, of the employees working in London.

Sample table: employees

Sample table: locations

Sample table: departments

Click me to see the solution

4. Write a query to make a join with two tables employees and itself to find the employee id, last_name as Employee along with their manager_id and last name as Manager.

Sample table: employees

Click me to see the solution

5. Write a query to make a join with a table employees and itself to find the name, including first_name and last_name and hire date for those employees who were hired after the employee Jones.

Sample table: employees

Click me to see the solution

6. Write a query to make a join with two tables employees and departments to get the department name and number of employees working in each department.

Sample table: employees

Sample table: departments

Click me to see the solution

7. Write a query to make a join to find the employee ID, job title and number of days an employee worked, for all the employees who worked in a department which ID is 90.

Sample table: employees

Click me to see the solution

8. Write a query to make a join with two tables employees and departments to display the department ID, department name and the first name of the manager.

Sample table: employees

Sample table: departments

Click me to see the solution

9. Write a query to make a join with three tables departments, employees, and locations to display the department name, manager name, and city.

Sample table: employees

Sample table: departments

Sample table: locations

Click me to see the solution

10. Write a query to make a join with two tables employees and jobs to display the job title and average salary of employees.

Sample table: employees

Click me to see the solution

11. Write a query to make a join with two tables employees and jobs to display the job title, employee name, and the difference between salary and the minimum salary of the employees.

Sample table: employees

Click me to see the solution

12. Write a query to make a join with two tables job_history and employees to display the status of employees who is currently drawing the salary above 10000.

Sample table: employees

Sample table: Job_history

Click me to see the solution

13. Write a query to make a join with two tables employees and departments to display department name, first_name and last_name, hire date and salary for all the managers who achieved a working experience is more than 15 years.

Sample table: employees

Sample table: departments

Click me to see the solution

... More

Structure of 'hr' database: