Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL JOINS on HR Database: 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 compute the average salary of employees for each job title.


Sample table: jobs


Sample Solution: 

SELECT job_title, AVG(salary) 
	FROM employees 
		NATURAL JOIN jobs 
			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

Relational Algebra Expression:

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

Relational Algebra Tree:

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

Pictorial Presentation:

SQL Exercises: Display the job title and average salary of employees.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the job title and average salary of employees - Duration

Rows:

Query visualization of Display the job title and average salary of employees - Rows

Cost:

Query visualization of Display the job title and average salary of employees - Cost

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

Previous: From the following tables, write a SQL query to find the department name and the full name (first and last name) of the manager.
Next: From the following table, write a SQL query to find those employees who earn $12000 and above. Return employee ID, starting date, end date, job ID and department ID.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3