w3resource

SQL SORTING and FILTERING on HR Database: Display the job ID for those jobs which average salary is above 8000

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 higher 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)

Relational Algebra Expression:

Relational Algebra Expression: Display the job ID for those jobs which average salary is above 8000.

Relational Algebra Tree:

Relational Algebra Tree: Display  the job ID for those jobs which average salary is above 8000.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the job ID for those jobs which average salary is above 8000 - Duration

Rows:

Query visualization of Display the job ID for those jobs which average salary is above 8000 - Rows

Cost:

Query visualization of Display the job ID for those jobs which average salary is above 8000 - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees who have no commission percentage and salary within the range 7000, 12000 (Begin and end values are included.) and works in the department number 50. Return all the fields of employees.
Next: From the following table, write a SQL query to find those job titles where the difference between minimum and maximum salaries is in the range the range 12000, 18000 (Begin and end values are included.). Return job_title, max_salary-min_salary.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

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