SQL JOINS on HR Database: Display the full name, job title and salary differences of those employees who is working in the department ID 80

SQL JOINS on HR Database: Exercise-16 with Solution

16. From the following tables, write a SQL query to compute the difference between maximum salary and salary of all the employees who works the department of ID 80. Return job title, employee name and salary difference.

Sample table: employees

Sample table: jobs

Sample Solution:

SELECT job_title, first_name || ' ' || last_name AS Employee_name, 
	max_salary-salary AS salary_difference
	FROM employees 
		NATURAL JOIN jobs 
			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

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: From the following table, write a SQL query to compute the average salary, number of employees received commission in that department. Return department name, average salary and number of employees.
Next: From the following table, write a SQL query to find the name of the country, city, and departments, which are running there.