SQL Exercises: Employees earning more than departments minimum wage
SQL SUBQUERY: Exercise-42 with Solution
From the following table, write a SQL query to find those employees who earn more than the minimum salary of a department of ID 40. Return first name, last name, salary, and department ID.
Sample table: employees
SELECT first_name, last_name, salary, department_id FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 40);
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 Adam Fripp 8200.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
The said query in SQL that retrieves the first name, last name, salary, and department ID of employees from a table named 'employees' where the salary is greater than any salary of employees in department 40.
The "WHERE" clause then filters the results to only include records where the salary is greater than any salary of employees in department 40. This is achieved by using the "ANY" operator, which compares the salary of each employee in the outer query to the result of the subquery.
Using JOIN with ANY Operator:
SELECT e1.first_name, e1.last_name, e1.salary, e1.department_id FROM employees e1 JOIN ( SELECT MAX(salary) as max_salary FROM employees WHERE department_id = 40 ) e2 ON e1.salary > e2.max_salary;
Using Subquery with a Comparison Operator:
SELECT first_name, last_name, salary, department_id FROM employees WHERE salary > ( SELECT MIN(salary) FROM employees WHERE department_id = 40 );
Using EXISTS and a Subquery:
SELECT first_name, last_name, salary, department_id FROM employees e1 WHERE EXISTS ( SELECT 1 FROM employees e2 WHERE e2.department_id = 40 AND e1.salary > e2.salary );
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Employees who work in the department Marketing.
Next SQL Exercise: Employees who was hired after the employee with ID 165.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to avoid the "divide by zero" error in SQL?
Select Case when divisor=0 then null Else dividend / divisor End ,,,
Select dividend / NULLIF(divisor, 0) ...
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook