PostgreSQL JOINS: Make a join with two tables job_history and employees to display the status of employees who is currently drawing the salary above 10000
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 Solution:
Code:
-- This SQL query retrieves job history records for employees whose salary is greater than 10000, by joining the job_history and employees tables.
SELECT jh.* -- Selects all columns from the job_history table
FROM job_history jh -- Specifies the first table from which to retrieve data, in this case, the job_history table, aliasing it as 'jh'
JOIN employees em -- Joins the job_history table with the employees table, specifying the second table and aliasing it as 'em'
ON (jh.employee_id = em.employee_id) -- Specifies the join condition based on the employee_id column
WHERE em.salary > 10000; -- Filters the rows to include only those where the salary of the employee is greater than 10000
Explanation:
- This SQL query retrieves job history records for employees whose salary is greater than 10000 by joining the job_history and employees tables.
- The SELECT statement selects all columns from the job_history table.
- The FROM clause specifies the first table from which to retrieve data, which is the job_history table, aliased as 'jh'.
- A JOIN operation is performed with the employees table, specifying the second table and aliasing it as 'em'.
- The ON clause specifies the join condition where the employee_id in the job_history table matches the employee_id in the employees table.
- The WHERE clause filters the rows to include only those where the salary of the employee is greater than 10000.
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: Job_history
employee_id start_date end_date job_id department_id ----------- ---------- ---------- ---------- ------------- employee_id start_date end_date` job_id department_id 102 1993-01-13 1998-07-24 IT_PROG 60 101 1989-09-21 1993-10-27 AC_ACCOUNT 110 101 1993-10-28 1997-03-15 AC_MGR 110 201 1996-02-17 1999-12-19 MK_REP 20 114 1998-03-24 1999-12-31 ST_CLERK 50 122 1999-01-01 1999-12-31 ST_CLERK 50 200 1987-09-17 1993-06-17 AD_ASST 90 176 1998-03-24 1998-12-31 SA_REP 80 176 1999-01-01 1999-12-31 SA_MAN 80 200 1994-07-01 1998-12-31 AC_ACCOUNT 90
Output:
pg_exercises=# SELECT jh.* FROM job_history jh
pg_exercises-# JOIN employees em
pg_exercises-# ON (jh.employee_id = em.employee_id)
pg_exercises-# WHERE em.salary > 10000;
employee_id | start_date | end_date | job_id | department_id
-------------+------------+------------+------------+---------------
114 | 1998-03-24 | 1999-12-31 | ST_CLERK | 50
101 | 1993-10-28 | 1997-03-15 | AC_MGR | 110
101 | 1989-09-21 | 1993-10-27 | AC_ACCOUNT | 110
102 | 1993-01-13 | 1998-07-24 | IT_PROG | 60
201 | 1996-02-17 | 1999-12-19 | MK_REP | 20
(5 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 : 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.
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?