SQL Exercises: Employees whose salary is more than 3700

SQL SUBQUERY: Exercise-21 with Solution

From the following table, write a SQL query to check whether there are any employees with salaries exceeding 3700. Return first name, last name and department ID.

Sample table: employees

Sample Solution:

SELECT first_name, last_name, department_id 
FROM employees 
WHERE EXISTS 
(SELECT * 
FROM employees 
WHERE salary >3700 );

Sample Output:

first_name	last_name	department_id
Steven		King		90
Neena		Kochhar		90
Lex		De Haan		90
Alexander	Hunold		60
Bruce		Ernst		60
David		Austin		60
Valli		Pataballa	60
Diana		Lorentz		60
Nancy		Greenberg	100
Daniel		Faviet		100
John		Chen		100
Ismael		Sciarra		100
Jose 	Manuel	Urman		100
Luis		Popp		100
Den		Raphaely	30
Alexander	Khoo		30
Shelli		Baida		30
Sigal		Tobias		30
Guy		Himuro		30
Karen		Colmenares	30
Matthew		Weiss		50
Adam		Fripp		50
Payam		Kaufling	50
Shanta		Vollman		50
Kevin		Mourgos		50
Julia		Nayer		50
Irene		Mikkilineni	50
James		Landry		50
Steven		Markle		50
Laura		Bissot		50
Mozhe		Atkinson	50
James		Marlow		50
TJ		Olson		50
Jason		Mallin		50
Michael		Rogers		50
Ki		Gee		50
Hazel		Philtanker	50
Renske		Ladwig		50
Stephen		Stiles		50
John		Seo		50
Joshua		Patel		50
Trenna		Rajs		50
Curtis		Davies		50
Randall		Matos		50
Peter		Vargas		50
John		Russell		80
Karen		Partners	80
Alberto		Errazuriz	80
Gerald		Cambrault	80
Eleni		Zlotkey		80
Peter		Tucker		80
David		Bernstein	80
Peter		Hall		80
Christopher	Olsen		80
Nanette		Cambrault	80
Oliver		Tuvault		80
Janette		King		80
Patrick		Sully		80
Allan		McEwen		80
Lindsey		Smith		80
Louise		Doran		80
Sarath		Sewall		80
Clara		Vishney		80
Danielle	Greene		80
Mattea		Marvins		80
David		Lee		80
Sundar		Ande		80
Amit		Banda		80
Lisa		Ozer		80
Harrison	Bloom		80
Tayler		Fox		80
William		Smith		80
Elizabeth	Bates		80
Sundita		Kumar		80
Ellen		Abel		80
Alyssa		Hutton		80
Jonathon	Taylor		80
Jack		Livingston	80
Kimberely	Grant		0
Charles		Johnson		80
Winston		Taylor		50
Jean		Fleaur		50
Martha		Sullivan	50
Girard		Geoni		50
Nandita		Sarchand	50
Alexis		Bull		50
Julia		Dellinger	50
Anthony		Cabrio		50
Kelly		Chung		50
Jennifer	Dilly		50
Timothy		Gates		50
Randall		Perkins		50
Sarah		Bell		50
Britney		Everett		50
Samuel		McCain		50
Vance		Jones		50
Alana		Walsh		50
Kevin		Feeney		50
Donald		OConnell	50
Douglas		Grant		50
Jennifer	Whalen		10
Michael		Hartstein	20
Pat		Fay		20
Susan		Mavris		40
Hermann		Baer		70
Shelley		Higgins		110
William		Gietz		110

Code Explanation:

The said query in SQL that is used to retrieve the first name, last name, and department ID of employees from the 'employees' table who have a salary greater than 3700.
The query uses the EXISTS operator, which checks if the result of a subquery is empty or not. In this case, the subquery is selecting all columns from the 'employees' table where the salary is greater than 3700. If the result of the subquery is not empty, then the EXISTS operator returns true and the rows from the outer query with the matching criteria are returned.

Visual Presentation:

Alternative Statements:

Using JOIN:

SELECT e1.first_name, e1.last_name, e1.department_id 
FROM employees e1
JOIN employees e2 ON e2.salary > 3700
WHERE e1.employee_id = e2.employee_id;

Using Subquery with IN:

SELECT first_name, last_name, department_id 
FROM employees 
WHERE employee_id IN (
    SELECT employee_id
    FROM employees
    WHERE salary > 3700
);

Using Subquery with EXISTS:

SELECT first_name, last_name, department_id 
FROM employees e1
WHERE EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.salary > 3700
    AND e1.employee_id = e2.employee_id
);

Note: These alternative statements will return only those rows whose salary is more than 3700.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Salary exceeds the average salary of any department.
Next SQL Exercise: Departments which contains at least one employee.