w3resource
SQL exercises

SQL JOINS on HR Database: Display the job title, full name of employee, and the difference between maximum salary for the job and salary of the employee

SQL JOINS on HR Database: Exercise-14 with Solution

14. Write a query in SQL to display job title, full name (first and last name ) of employee, and the difference between maximum salary for the job and salary of the employee.

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;

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

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the job title, full name of employee, and the difference between maximum salary for the job and salary of the employee - Duration

Rows:

Query visualization of Display the job title, full name of employee, and the difference between maximum salary for the job and salary of the employee - Rows

Cost:

Query visualization of Display the job title, full name of employee, and the difference between maximum salary for the job and salary of the employee - Cost

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

Previous: Write a query in SQL to display the job title, department name, full name (first and last name ) of employee, and starting date for all the jobs which started on or after 1st January, 1993 and ending with on or before 31 August, 1997.
Next: Write a query in SQL to display the name of the department, average salary and number of employees working in that department who got commission.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming