w3resource

SQL Subquery Exercises: Display 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

From the following tables, write a SQL query to find those departments where maximum salary is 7000 and above. The employees worked in those departments have already completed one or more jobs. Return all the fields of the departments.

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

Pictorial Presentation:

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

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: From the following tables, write a SQL query to find the city of the employee of ID 134. Return city.
Next: From the following tables, write a SQL query to find those departments where starting salary is at least 8000. Return all the fields of departments.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

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