﻿ SQL SUBQUERIES: Find the employees who earn more than the maximum salary of ID 40 for their department - w3resource

# SQL Subquery Exercises: Display the first and last name, salary, and department ID for those employees who earn more than the maximum salary of a department which ID is 40

## SQL SUBQUERY: Exercise-37 with Solution

From the following table, write a SQL query to find those employees who earn more than the maximum salary for a department of ID 40. Return first name, last name and department ID.

Sample table: employees

Sample Solution:

``````SELECT first_name, last_name, salary, department_id
FROM employees
WHERE salary > ALL
(SELECT salary
FROM employees
WHERE department_id = 40);
``````

Sample Output:

```first_name	last_name	salary	department_id
Steven		King		24000.00	90
Neena		Kochhar		17000.00	90
Lex		De Haan		17000.00	90
Alexander	Hunold		9000.00		60
Nancy		Greenberg	12000.00	100
Daniel		Faviet		9000.00		100
John		Chen		8200.00		100
Ismael		Sciarra		7700.00		100
Jose 	Manuel	Urman		7800.00		100
Luis		Popp		6900.00		100
Den		Raphaely	11000.00	30
Matthew		Weiss		8000.00		50
Payam		Kaufling	7900.00		50
John		Russell		14000.00	80
Karen		Partners	13500.00	80
Alberto		Errazuriz	12000.00	80
Gerald		Cambrault	11000.00	80
Eleni		Zlotkey		10500.00	80
Peter		Tucker		10000.00	80
David		Bernstein	9500.00		80
Peter		Hall		9000.00		80
Christopher	Olsen		8000.00		80
Nanette		Cambrault	7500.00		80
Oliver		Tuvault		7000.00		80
Janette		King		10000.00	80
Patrick		Sully		9500.00		80
Allan		McEwen		9000.00		80
Lindsey		Smith		8000.00		80
Louise		Doran		7500.00		80
Sarath		Sewall		7000.00		80
Clara		Vishney		10500.00	80
Danielle	Greene		9500.00		80
Mattea		Marvins		7200.00		80
David		Lee		6800.00		80
Lisa		Ozer		11500.00	80
Harrison	Bloom		10000.00	80
Tayler		Fox		9600.00		80
William		Smith		7400.00		80
Elizabeth	Bates		7300.00		80
Ellen		Abel		11000.00	80
Alyssa		Hutton		8800.00		80
Jonathon	Taylor		8600.00		80
Jack		Livingston	8400.00		80
Kimberely	Grant		7000.00		0
Michael		Hartstein	13000.00	20
Hermann		Baer		10000.00	70
Shelley		Higgins		12000.00	110
William		Gietz		8300.00		110
```

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