﻿ SQL SUBQUERIES: Display the details of departments which max salary is 7000 or above for those employees who already done one or more jobs - 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:

## Query Visualization:

Duration:

Rows:

Cost:

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

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