PostgreSQL JOINS: Make a join with two tables employees and departments for all the managers who achieved a working experience is more than 15 years
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 Solution:
Code:
-- This SQL query retrieves department name, employee details, and years of experience for employees whose experience is greater than 15 years.
SELECT department_name, -- Selects the department_name column from the departments table
first_name, -- Selects the first_name column from the employees table
last_name, -- Selects the last_name column from the employees table
hire_date, -- Selects the hire_date column from the employees table
salary, -- Selects the salary column from the employees table
date_part('year', age(now(), hire_date)) Experience -- Calculates the years of experience for each employee and labels it as "Experience"
FROM departments w1 -- Specifies the first table from which to retrieve data, aliasing it as 'w1'
JOIN employees w2 -- Joins the departments table with the employees table, specifying the second table and aliasing it as 'w2'
ON (w1.manager_id = w2.employee_id) -- Specifies the join condition based on the manager_id and employee_id columns
WHERE date_part('year', age(now(), hire_date)) > 15; -- Filters the rows to include only those where the years of experience are greater than 15
Explanation:
- This SQL query retrieves department name, employee details, and years of experience for employees whose experience is greater than 15 years.
- The SELECT statement selects department name, employee first name, last name, hire date, salary, and calculates the years of experience for each employee.
- The FROM clause specifies the first table from which to retrieve data, which is the departments table, aliasing it as 'w1'.
- A JOIN operation is performed with the employees table, specifying the second table and aliasing it as 'w2'.
- The ON clause specifies the join condition where the manager_id from the departments table matches the employee_id from the employees table.
- The WHERE clause filters the rows to include only those where the years of experience are greater than 15, calculated using the age() function.
Sample table: employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | .......... | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | ........ | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+
Output:
pg_exercises-# SELECT department_name, first_name, last_name,
pg_exercises-# hire_date, salary,date_part('year',age(now(),hire_date)) Experience
pg_exercises-# FROM departments w1
pg_exercises-# JOIN employees w2
pg_exercises-# ON (w1.manager_id = w2.employee_id)
pg_exercises-# WHERE date_part('year',age(now(),hire_date))>15;
department_name | first_name | last_name | hire_date | salary | experience
------------------+------------+-----------+------------+----------+------------
Administration | Jennifer | Whalen | 1987-09-25 | 4400.00 | 29
Marketing | Michael | Hartstein | 1987-09-26 | 13000.00 | 29
Purchasing | Den | Raphaely | 1987-07-01 | 11000.00 | 29
Human Resources | Susan | Mavris | 1987-09-28 | 6500.00 | 29
Shipping | Adam | Fripp | 1987-07-08 | 8200.00 | 29
IT | Alexander | Hunold | 1987-06-20 | 9000.00 | 29
Public Relations | Hermann | Baer | 1987-09-29 | 10000.00 | 29
Sales | John | Russell | 1987-08-01 | 14000.00 | 29
Executive | Steven | King | 1987-06-17 | 24000.00 | 29
Finance | Nancy | Greenberg | 1987-06-25 | 12000.00 | 29
Accounting | Shelley | Higgins | 1987-09-30 | 12000.00 | 29
(11 rows)
Go to:
PREV : 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.
NEXT : PostgreSQL Subquery - Exercises, Practice, Solution
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?