SQL Exercises: Employee who works in that department which ID is 40
SQL SUBQUERY: Exercise-40 with Solution
From the following table, write a SQL query to find those employees whose salary matches that of the employee who works in department 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 IN ( SELECT salary FROM employees WHERE department_id = 40);
first_name last_name salary department_id Shanta Vollman 6500.00 50 Susan Mavris 6500.00 40
The said query in SQL that retrieves the first name, last name, salary, and department id of all employees whose salary is equal to the salary of at least one employee who belongs to department id 40.
The WHERE clause specified is checking if the salary of each employee is found in the list of salaries of employees who belong to department id 40. Using the IN operator and a subquery in the WHERE clause, the condition retrieves the list of salaries of employees in department id 40 and checks each employee's salary.
SELECT e1.first_name, e1.last_name, e1.salary, e1.department_id FROM employees e1 JOIN employees e2 ON e1.salary = e2.salary WHERE e2.department_id = 40;
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to combine date from one field with time from another field - MS SQL Server
You can simply add the two.
- if the Time part of your Date column is always zero
- and the Date part of your Time column is also always zero (base date: January 1, 1900)
Adding them returns the correct result-
SELECT Combined = MyDate + MyTime FROM MyTable
- 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