SQL Exercise: Jobs which average salary is above 8000

Last update on March 31 2023 06:07:42 (UTC/GMT +8 hours)

SQL SORTING and FILTERING on HR Database: Exercise-34 with Solution

34. From the following table, write a SQL query to compute the average salary of each job ID. Exclude those records where average salary is on or lower than 8000. Return job ID, average salary.

Sample table : employees

Sample Solution:

SELECT job_id, AVG(salary) 
     FROM employees 
         GROUP BY job_id 
               HAVING AVG(salary)>8000;

Sample Output:

   job_id   |          avg
------------+------------------------
 AC_ACCOUNT |  8300.0000000000000000
 SA_MAN     |     12200.000000000000
 AD_PRES    |     24000.000000000000
 AC_MGR     | 12000.0000000000000000
 FI_MGR     | 12000.0000000000000000
 MK_MAN     | 13000.0000000000000000
 PR_REP     | 10000.0000000000000000
 AD_VP      |     17000.000000000000
 SA_REP     |  8350.0000000000000000
 PU_MAN     | 11000.0000000000000000
(10 rows)

Code Explanation:

The said query in SQL that selects the "job_id" column and the average of the "salary" column, grouping the data by "job_id". The "HAVING" clause is used to specify a condition for the groups, in this case, the average salary must be greater than 8000. There will be a table as a result of the search which will contain a "job_id" and the average salary for each job, but only those jobs that have average salaries greater than 8000.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: A department 50 employee without a commission %.
Next SQL Exercise: Salary ranges for jobs with a minimum and maximum.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

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

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook