PostgreSQL JOINS: Make a join with two tables employees and jobs to display the job title and average salary of employees

10. Write a query to make a join with two tables employees and jobs to display the job title and average salary of employees.

Sample Solution:

Code:

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

SELECT job_title, -- Selects the job_title column
AVG(salary) -- Calculates the average salary for each job title
FROM employees -- Specifies the first table from which to retrieve data, in this case, the employees table
NATURAL JOIN jobs -- Performs a natural join with the jobs table to include associated job information
GROUP BY job_title; -- Groups the results by job_title

Explanation:

• This SQL query calculates the average salary for each job title by joining the employees and jobs tables.
• The SELECT statement selects the job title column and calculates the average salary for each job title.
• The FROM clause specifies the first table from which to retrieve data, which is the employees table.
• A NATURAL JOIN operation is performed with the jobs table to include associated job information.
• The GROUP BY clause groups the results by job title.

Sample table: employees

Output:

pg_exercises=# SELECT job_title, AVG(salary)
pg_exercises-# FROM employees
pg_exercises-# NATURAL JOIN jobs
pg_exercises-# GROUP BY job_title;

            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 Representative            |  8350.0000000000000000
 Sales Manager                   |     12200.000000000000
 President                       |     24000.000000000000
 Stock Manager                   |  7280.0000000000000000
 Human Resources Representative  |  6500.0000000000000000
 Accounting Manager              | 12000.0000000000000000
 Stock Clerk                     |  2785.0000000000000000
(18 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

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

Previous: Write a query to make a join with three tables departments, employees, and locations to display the department name, manager name, and city.
Next: Write a query to make a join with two tables employees and jobs to display the job title, employee name, and the difference between salary and the minimum salary of the employees.