SQL Exercise: Salary difference between jobs maximum and employees

SQL JOINS on HR Database: Exercise-14 with Solution

14. From the following tables, write a SQL query to calculate the difference between the maximum salary of the job and the employee's salary. Return job title, employee name, and salary difference.

Sample table: employees

Sample table: jobs

Sample Solution:

-- Selecting specific columns (job_title, first_name || ' ' || last_name AS Employee_name, max_salary - salary AS salary_difference) from the 'employees' table
SELECT job_title, first_name || ' ' || last_name AS Employee_name, max_salary - salary AS salary_difference 

-- Performing a NATURAL JOIN between the 'employees' table and the 'jobs' table, where the join is implicitly based on columns with the same name in both tables
FROM employees 

NATURAL JOIN jobs;

Sample Output:

job_title			employee_name		salary_difference
President			Steven King	 		16000.00
Administration Vice President   Neena Kochhar			13000.00
Administration Vice President   Lex De Haan	 		13000.00
Programmer			Alexander Hunold 		1000.00
Programmer			Bruce Ernst			4000.00
Programmer			David Austin			5200.00
Programmer			Valli Pataballa			5200.00
Programmer			Diana Lorentz			5800.00
Finance Manager			Nancy Greenberg			4000.00
Accountant			Daniel Faviet			0.00
Accountant			John Chen			800.00
Accountant			Ismael Sciarra			1300.00
Accountant			Jose Manuel Urman		1200.00
Accountant			Luis Popp			2100.00
Purchasing 	Manager	Den Raphaely				4000.00
Purchasing 	Clerk	Alexander Khoo				2400.00
Purchasing 	Clerk	Shelli Baida				2600.00
Purchasing 	Clerk	Sigal Tobias				2700.00
Purchasing 	Clerk	Guy Himuro				2900.00
Purchasing 	Clerk	Karen Colmenares			3000.00
Stock Manager 		Matthew Weiss				500.00
Stock Manager 		Adam Fripp				300.00
Stock Manager 		Payam Kaufling				600.00
Stock Manager 		Shanta Vollman				2000.00
Stock Manager 		Kevin Mourgos				2700.00
Stock Clerk	 		Julia Nayer			1800.00
Stock Clerk	 		Irene Mikkilineni		2300.00
Stock Clerk	 		James Landry			2600.00
Stock Clerk	 		Steven Markle			2800.00
Stock Clerk	 		Laura Bissot			1700.00
Stock Clerk	 		Mozhe Atkinson			2200.00
Stock Clerk	 		James Marlow			2500.00
Stock Clerk	 		TJ Olson			2900.00
Stock Clerk	 		Jason Mallin			1700.00
Stock Clerk	 		Michael Rogers			2100.00
Stock Clerk	 		Ki Gee				2600.00
Stock Clerk	 		Hazel Philtanker		2800.00
Stock Clerk	 		Renske Ladwig			1400.00
Stock Clerk			Stephen Stiles			1800.00
Stock Clerk			John Seo			2300.00
Stock Clerk			Joshua Patel			2500.00
Stock Clerk			Trenna Rajs			1500.00
Stock Clerk			Curtis Davies			1900.00
Stock Clerk			Randall Matos			2400.00
Stock Clerk			Peter Vargas			2500.00
Sales Manager		John Russell				6000.00
Sales Manager		Karen Partners				6500.00
Sales Manager		Alberto Errazuriz			8000.00
Sales Manager		Gerald Cambrault			9000.00
Sales Manager		Eleni Zlotkey				9500.00
Sales Representative	Peter Tucker				2000.00
Sales Representative	David Bernstein				2500.00
Sales Representative	Peter Hall				3000.00
Sales Representative	Christopher Olsen			4000.00
Sales Representative	Nanette Cambrault			4500.00
Sales Representative	Oliver Tuvault				5000.00
Sales Representative	Janette King				2000.00
Sales Representative	Patrick Sully				2500.00
Sales Representative	Allan McEwen				3000.00
Sales Representative	Lindsey Smith				4000.00
Sales Representative	Louise Doran				4500.00
Sales Representative	Sarath Sewall				5000.00
Sales Representative	Clara Vishney				1500.00
Sales Representative	Danielle Greene				2500.00
Sales Representative	Mattea Marvins				4800.00
Sales Representative	David Lee				5200.00
Sales Representative	Sundar Ande				5600.00
Sales Representative	Amit Banda				5800.00
Sales Representative	Lisa Ozer				500.00
Sales Representative	Harrison Bloom				2000.00
Sales Representative	Tayler Fox				2400.00
Sales Representative	William Smith				4600.00
Sales Representative	Elizabeth Bates				4700.00
Sales Representative	Sundita Kumar				5900.00
Sales Representative	Ellen Abel				1000.00
Sales Representative	Alyssa Hutton				3200.00
Sales Representative	Jonathon Taylor				3400.00
Sales Representative	Jack Livingston				3600.00
Sales Representative	Kimberely Grant				5000.00
Sales Representative	Charles Johnson				5800.00
Shipping Clerk		Winston Taylor				2300.00
Shipping Clerk		Jean Fleaur				2400.00
Shipping Clerk		Martha Sullivan				3000.00
Shipping Clerk		Girard Geoni				2700.00
Shipping Clerk		Nandita Sarchand			1300.00
Shipping Clerk		Alexis Bull				1400.00
Shipping Clerk		Julia Dellinger				2100.00
Shipping Clerk		Anthony Cabrio				2500.00
Shipping Clerk		Kelly Chung				1700.00
Shipping Clerk		Jennifer Dilly				1900.00
Shipping Clerk		Timothy Gates				2600.00
Shipping Clerk		Randall Perkins				3000.00
Shipping Clerk		Sarah Bell				1500.00
Shipping Clerk		Britney Everett				1600.00
Shipping Clerk		Samuel McCain				2300.00
Shipping Clerk		Vance Jones				2700.00
Shipping Clerk		Alana Walsh				2400.00
Shipping Clerk		Kevin Feeney				2500.00
Shipping Clerk		Donald OConnell				2900.00
Shipping Clerk		Douglas Grant				2900.00
Administration Assistant	Jennifer Whalen			1600.00
Marketing Manager	Michael Hartstein			2000.00
Marketing Representative	Pat Fay				3000.00
Human Resources Representative	Susan Mavrs			2500.00
Public Relations Representative	Hermann Bae			500.00
Accounting Manager	Shelley Higgins				4000.00
Public Accountant	William Gietz				700.00

Code Explanation:

The said query in SQL that selects the job title, employee name (which is concatenated from first name and last name columns), and salary difference between the maximum salary for that particular job and the employee's salary. It does so by joining the 'employees' and 'jobs' tables using the NATURAL JOIN keyword.
The uses of NATURAL JOIN keyword to join the tables on their common column, which is "job_id" in this case.

Visual Presentation:

Alternative Solutions:

Using INNER JOIN with Explicit Column Names:

SELECT j.job_title, CONCAT(e.first_name, ' ', e.last_name) AS Employee_name, 
       j.max_salary - e.salary AS salary_difference 
FROM employees e 
JOIN jobs j ON e.job_id = j.job_id;

Explanation:

This one employs an INNER JOIN to connect 'employees' and 'jobs' based on matching "job_id". It then selects the job title, employee name, and computes the salary difference.

Using INNER JOIN with Aliases:

SELECT j.job_title, CONCAT(e.first_name, ' ', e.last_name) AS Employee_name, 
       j.max_salary - e.salary AS salary_difference 
FROM employees e 
JOIN jobs j ON e.job_id = j.job_id;

Explanation:

This query also uses an INNER JOIN to link 'employees' and 'jobs' based on their "job_id". It then selects the job title, employee name, and computes the salary difference as requested.

Using INNER JOIN:

SELECT j.job_title, CONCAT(e.first_name, ' ', e.last_name) AS Employee_name, 
       j.max_salary - e.salary AS salary_difference 
FROM employees e 
JOIN jobs j ON e.job_id = j.job_id;

Explanation:

This query uses an INNER JOIN to combine the 'employees' and 'jobs' tables based on their "job_id". It selects the job title, employee name (concatenated from first and last names), and calculates the salary difference between the maximum salary for the job and the employee's salary.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Jobs which started between two given dates.
Next SQL Exercise: Employees working in the department who got commission.