w3resource

MySQL Joins Exercises: Display job title, employee name, and the difference between salary of the employee and minimum salary for the job

MySQL Joins: Exercise-11 with Solution

Write a MySQL query to display job title, employee name, and the difference between salary of the employee and minimum salary for the job.

Sample table: employees


Sample table: jobs


Code:

-- This SQL query selects specific columns from the 'employees' and 'jobs' tables and calculates the difference between each employee's salary and the minimum salary for their job title.

SELECT 
    job_title, -- Selecting the 'job_title' column from the result set.
    first_name, -- Selecting the 'first_name' column from the result set.
    salary - min_salary AS 'Salary - Min_Salary' -- Calculating the difference between the salary and the minimum salary for each job title and aliasing it as 'Salary - Min_Salary'.
FROM 
    employees -- Specifying the 'employees' table.
NATURAL JOIN 
    jobs; -- Performing a natural join with the 'jobs' table.

Explanation:

  • This SQL query retrieves the job title, employee's first name, and calculates the difference between each employee's salary and the minimum salary for their respective job title.
  • It performs a natural join between the 'employees' and 'jobs' tables based on any common columns they share.
  • The 'salary - min_salary' expression calculates the difference between the salary of each employee and the minimum salary defined for their job title in the 'jobs' table.
  • The result set includes job title, employee's first name, and the calculated difference between salary and minimum salary, which is aliased as 'Salary - Min_Salary'.

Sample Output:

job_title				first_name		Salary - Min_Salary
President				Steven			4000
Administration Vice President		Neena			2000
Administration Vice President		Lex			2000
Administration Assistant		Jennifer		1400
Finance Manager				Nancy			3800
Accountant				Daniel			4800
Accountant				John			4000
Accountant				Ismael			3500
Accountant				Jose Manuel		3600
Accountant				Luis			2700
Accounting Manager			Shelley			3800
Public Accountant			William			4100
Sales Manager				John			4000
Sales Manager				Karen			3500
Sales Manager				Alberto			2000
Sales Manager				Gerald			1000
Sales Manager				Eleni			500
Sales Representative			Peter			4000
Sales Representative			David			3500
Sales Representative			Peter			3000
Sales Representative			Christopher		2000
Sales Representative			Nanette			1500
Sales Representative			Oliver			1000
Sales Representative			Janette			4000
Sales Representative			Patrick			3500
Sales Representative			Allan			3000
Sales Representative			Lindsey			2000
Sales Representative			Louise			1500
Sales Representative			Sarath			1000
Sales Representative			Clara			4500
Sales Representative			Danielle		3500
Sales Representative			Mattea			1200
Sales Representative			David			800
Sales Representative			Sundar			400
Sales Representative			Amit			200
Sales Representative			Lisa			5500
Sales Representative			Harrison		4000
Sales Representative			Tayler			3600
Sales Representative			William			1400
Sales Representative			Elizabeth		1300
Sales Representative			Sundita			100
Sales Representative			Ellen			5000
Sales Representative			Alyssa			2800
Sales Representative			Jonathon		2600
Sales Representative			Jack			2400
Sales Representative			Kimberely		1000
Sales Representative			Charles			200
Purchasing Manager			Den			3000
Purchasing Clerk			Alexander		600
Purchasing Clerk			Shelli			400
Purchasing Clerk			Sigal			300
Purchasing Clerk			Guy			100
Purchasing Clerk			Karen			0
Stock Manager				Matthew			2500
Stock Manager				Adam			2700
Stock Manager				Payam			2400
Stock Manager				Shanta			1000
Stock Manager				Kevin			300
Stock Clerk				Julia			1200
Stock Clerk				Irene			700
Stock Clerk				James			400
Stock Clerk				Steven			200
Stock Clerk				Laura			1300
Stock Clerk				Mozhe			800
Stock Clerk				James			500
Stock Clerk				TJ			100
Stock Clerk				Jason			1300
Stock Clerk				Michael			900
Stock Clerk				Ki			400
Stock Clerk				Hazel			200
Stock Clerk				Renske			1600
Stock Clerk				Stephen			1200
Stock Clerk				John			700
Stock Clerk				Joshua			500
Stock Clerk				Trenna			1500
Stock Clerk				Curtis			1100
Stock Clerk				Randall			600
Stock Clerk				Peter			500
Shipping Clerk				Winston			700
Shipping Clerk				Jean			600
Shipping Clerk				Martha			0
Shipping Clerk				Girard			300
Shipping Clerk				Nandita			1700
Shipping Clerk				Alexis			1600
Shipping Clerk				Julia			900
Shipping Clerk				Anthony			500
Shipping Clerk				Kelly			1300
Shipping Clerk				Jennifer		1100
Shipping Clerk				Timothy			400
Shipping Clerk				Randall			0
Shipping Clerk				Sarah			1500
Shipping Clerk				Britney			1400
Shipping Clerk				Samuel			700
Shipping Clerk				Vance			300
Shipping Clerk				Alana			600
Shipping Clerk				Kevin			500
Shipping Clerk				Donald			100
Shipping Clerk				Douglas			100
Programmer				Alexander		5000
Programmer				Bruce			2000
Programmer				David			800
Programmer				Valli			800
Programmer				Diana			200
Marketing Manager			Michael			4000
Marketing Representative		Pat			2000
Human Resources Representative		Susan			2500
Public Relations Representative		Hermann			5500

 

MySQL Code Editor:

Structure of 'hr' database :

hr database

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

Previous:Write a MySQL query to display the job title and average salary of employees.
Next:Write a MySQL query to display the job history that were done by any employee who is currently drawing more than 10000 of salary.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.