SQL Exercise: Salary differences of employees in the department 80

SQL JOINS on HR Database: Exercise-16 with Solution

16. From the following tables, write a SQL query to calculate the difference between the maximum salary and the salary of all the employees who work in the department of ID 80. 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 

-- Filtering rows based on the condition that the 'department_id' is equal to 80
WHERE department_id = 80;

Sample Output:

job_title		employee_name		salary_difference
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	Charles Johnson		5800.00

Code Explnation:

The said query in SQL that is selecting three columns job_title, Employee_name, and salary_difference from two tables (employees and jobs) and filtering by department_id = 80.
The query uses a natural join to join the employees and jobs tables based on their shared column, job_id. This means that only the rows with matching job_id values are included in the results.
Finally, the WHERE clause filters the results to only include rows where the department_id is equal to 80. This limits the results to only employees in the department with ID 80.

Visual Presentation:

Alternative Solutions:

Using INNER JOIN and WHERE Clause:

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
WHERE e.department_id = 80;

Explanation:

This query combines the 'employees' and 'jobs' tables using an INNER JOIN. It selects the job title, employee name, and computes the salary difference. The results are filtered to only include records with department_id equal to 80.

Using INNER JOIN with Explicit Column Names and WHERE Clause:

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

Explanation:

This one uses an INNER JOIN and explicitly specifies column names. It selects the job title, employee name, and computes the salary difference. The results are filtered to only include records with department_id equal to 80.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Employees working in the department who got commission.
Next SQL Exercise: Display country, city, and the departments.