w3resource
SQL exercises

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. Write a query in SQL to display the full name (first and last name ) of employees, job title and the salary differences to their own job for those employees who is working in the department ID 80.

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

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the full name, and job title of those employees who is working in the department which ID is 80 - Duration

Rows:

Query visualization of Display the full name, and job title of those employees who is working in the department which ID is 80 - Rows

Cost:

Query visualization of Display the full name, and job title of those employees who is working in the department which ID is 80 - Cost

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

Previous: 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.
Next: Write a query in SQL to display the name of the country, city, and the departments which are running there.

What is the difficulty level of this exercise?



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