w3resource

MySQL Joins Exercises: Display the job title and average salary of employees

MySQL Joins: Exercise-10 with Solution

Write a MySQL query to display the job title and average salary of employees.

Sample table: employees


Sample table: jobs


Code:

-- This SQL query calculates the average salary for each job title by joining the 'employees' and 'jobs' tables.

SELECT 
    job_title, -- Selecting the 'job_title' column from the result set.
    AVG(salary) -- Calculating the average salary and selecting it from the result set.
FROM 
    employees -- Specifying the 'employees' table.
NATURAL JOIN 
    jobs -- Performing a natural join with the 'jobs' table.
GROUP BY 
    job_title; -- Grouping the result set by job title to calculate the average salary for each job.

Explanation:

  • This SQL query retrieves the job title and the average salary for each job title.
  • It performs a natural join between the 'employees' and 'jobs' tables based on any common columns they share.
  • The 'AVG(salary)' function is used to calculate the average salary for each job title group.
  • The 'GROUP BY' clause groups the result set by job title to calculate the average salary for each job.

Relational Algebra Expression:

Relational Algebra Expression: Join: Display the job title and average salary of employees.

Relational Algebra Tree:

Relational Algebra Tree: Join: Display the job title and average salary of employees.

Sample Output:

job_title					AVG(salary)
Accountant					7920
Accounting Manager				12000
Administration Assistant			4400
Administration Vice President			17000
Finance Manager					12000
Human Resources Representative			6500
Marketing Manager				13000
Marketing Representative			6000
President					24000
Programmer					5760
Public Accountant				8300
Public Relations Representative			10000
Purchasing Clerk				2780
Purchasing Manager				11000
Sales Manager					12200
Sales Representative				8350
Shipping Clerk					3215
Stock Clerk					2785
Stock Manager					7280

 

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 department name, manager name, and city.
Next:Write a MySQL query to display job title, employee name, and the difference between salary of the employee and minimum salary for the job.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.