SQL Exercise: Display the job title and average salary of employees

SQL JOINS on HR Database: Exercise-19 with Solution

19. From the following table, write a SQL query to calculate the average salary of employees for each job title.

Sample table: jobs

Sample Solution:

-- Selecting specific columns (job_title, AVG(salary)) from the 'employees' table
SELECT job_title, AVG(salary) 

-- Performing a NATURAL JOIN between the 'employees' table and the 'jobs' table, where the join is implicitly based on columns with the same name in both tables
FROM employees 

NATURAL JOIN jobs 

-- Grouping the result set by the 'job_title' column
GROUP BY job_title;

Sample Output:

job_title			avg
Marketing Manager		13000.0000000000000000
Marketing Representative	6000.0000000000000000
Finance Manager			12000.0000000000000000
Shipping Clerk			3215.0000000000000000
Public Accountant		8300.0000000000000000
Administration Vice President	17000.000000000000
Programmer			5760.0000000000000000
Accountant			7920.0000000000000000
Purchasing Clerk		2780.0000000000000000
Public Relations Representative	10000.0000000000000000
Purchasing Manager		11000.0000000000000000
Administration Assistant	4400.0000000000000000
Sales Manager			12200.000000000000
Sales Representative		8350.0000000000000000
President			24000.000000000000
Stock Manager			7280.0000000000000000
Human Resources Representative	6500.0000000000000000
Accounting Manager		12000.0000000000000000
Stock Clerk			2785.0000000000000000

Code Explanation:

The said query in SQL that retrieves the average salary for each job title in the employees table by joining it with the jobs table on their common columns, and grouping the results by job_title.
The NATURAL JOIN clause joins the employees table with the jobs table on their common column(s). A natural join matches columns with the same name in both tables.
The GROUP BY groups the result set by the job_title. column.

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation:

Alternative Solutions:

Using INNER JOIN with Aliases and GROUP BY:

SELECT j.job_title, AVG(e.salary) AS average_salary
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
GROUP BY j.job_title;

Explanation:

This query combines the 'employees' and 'jobs' tables using an INNER JOIN. It then calculates the average salary for each job title using GROUP BY.

Using INNER JOIN with Explicit Column Names and GROUP BY:

SELECT jobs.job_title, AVG(employees.salary) AS average_salary
FROM employees 
JOIN jobs ON employees.job_id = jobs.job_id
GROUP BY jobs.job_title;

Explanation:

This query uses INNER JOINs but explicitly specifies column names. It calculates the average salary for each job title using GROUP BY.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Department name and the full name of the manager.
Next SQL Exercise: Employees who earn a salary over 12000.