w3resource
SQL exercises

SQL Subquery Exercises: Display the the details of departments which max salary is 7000 or above for those employees who already done one or more jobs

SQL SUBQUERY : Exercise-48 with Solution

Write a query in SQL to display the the details of those departments which max salary is 7000 or above for those employees who already done one or more jobs.

Sample table: departments


Sample table: employees


Sample table: job_history


Sample Solution:

SELECT *
FROM departments
WHERE DEPARTMENT_ID IN
    (SELECT DEPARTMENT_ID
     FROM employees
     WHERE EMPLOYEE_ID IN
         (SELECT EMPLOYEE_ID
          FROM job_history
          GROUP BY EMPLOYEE_ID
          HAVING COUNT(EMPLOYEE_ID) > 1)
     GROUP BY DEPARTMENT_ID
     HAVING MAX(SALARY) > 7000);

Sample Output:

department_id	department_name	manager_id	location_id
80		Sales		145		2500
90		Executive	 100	1700

Practice Online


Query Visualization:

Duration:

Query visualization of Display the the details of departments which max salary is 7000 or above for those employees who already done one or more jobs - Duration

Rows:

Query visualization of Display the the details of departments which max salary is 7000 or above for those employees who already done one or more jobs - Rows

Cost:

Query visualization of Display the the details of departments which max salary is 7000 or above for those employees who already done one or more jobs - Cost

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

Previous: Write a query in SQL to display the city of the employee whose ID 134 and works there.
Next: Write a query in SQL to display the detail information of those departments which starting salary is at least 8000.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming