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:

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

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.