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:
SELECT job_title, AVG(salary)
FROM employees
NATURAL JOIN jobs
GROUP 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.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework