PostgreSQL JOINS: Display job title, employee name, and the difference between salary of the employee and minimum salary for the job

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 Solution:

Code:

-- This SQL query retrieves employee information along with job title details and calculates the difference between each employee's salary and the minimum salary for their job title.

SELECT w2.job_title, -- Selects the job_title column from the second instance of the jobs table
       w1.first_name, -- Selects the first_name column from the employees table
       w1.salary, -- Selects the salary column from the employees table
       w2.min_salary, -- Selects the min_salary column from the second instance of the jobs table
       (w1.salary - w2.min_salary) as "Salary - Min_Salary" -- Calculates the difference between each employee's salary and the minimum salary for their job title
FROM employees w1 -- Specifies the first table from which to retrieve data, aliasing it as 'w1'
NATURAL JOIN jobs w2; -- Performs a natural join with the jobs table, specifying the second table and aliasing it as 'w2'

Explanation:

• This SQL query retrieves employee information along with job title details and calculates the difference between each employee's salary and the minimum salary for their job title.
• The SELECT statement selects the job title, first name, salary, minimum salary for the job title, and calculates the difference between salary and minimum salary.
• The FROM clause specifies the first table from which to retrieve data, which is the employees table, aliased as 'w1'.
• A NATURAL JOIN operation is performed with the jobs table, specifying the second table and aliasing it as 'w2'.
• The w1.salary - w2.min_salary expression calculates the difference between each employee's salary and the minimum salary for their corresponding job title.

Sample table: employees

Output:

pg_exercises=# SELECT w2.job_title, w1.first_name, w1.salary,
pg_exercises-# w2.min_salary,(w1.salary - w2.min_salary) as "Salary - Min_Salary"
pg_exercises-# FROM employees  w1
pg_exercises-# NATURAL JOIN jobs w2;
            job_title            | first_name  |  salary  | min_salary | Salary - Min_Salary
---------------------------------+-------------+----------+------------+---------------------
 Programmer                      | Alexander   |  9030.00 |       4000 |             5030.00
 Programmer                      | Bruce       |  6030.00 |       4000 |             2030.00
 Programmer                      | David       |  4830.00 |       4000 |              830.00
 Programmer                      | Valli       |  4830.00 |       4000 |              830.00
 Programmer                      | Diana       |  4230.00 |       4000 |              230.00
 Purchasing Manager              | Den         | 11030.00 |       8000 |             3030.00
 Purchasing Clerk                | Alexander   |  3130.00 |       2500 |              630.00
 Purchasing Clerk                | Shelli      |  2930.00 |       2500 |              430.00
 Purchasing Clerk                | Sigal       |  2830.00 |       2500 |              330.00
 President                       | Steven      | 24030.00 |      20000 |             4030.00
 ...                             | ...         | ...      |      ...   |            ...
 Accounting Manager              | Shelley     | 12030.00 |       8200 |             3830.00
 Public Accountant               | William     |  8330.00 |       4200 |             4130.00
(101 rows)

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to make a join with two tables employees and jobs to display the job title and average salary of employees.
Next: 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.