SQL Exercises: Salary and reporting person id is 3000 and 121

Last update on March 12 2025 12:12:08 (UTC/GMT +8 hours)

From the following table, write a SQL query to find the employee whose salary is 3000 and reporting person’s ID is 121. Return all fields.

Sample table: employees

+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   | EMAIL    | PHONE_NUMBER       | HIRE_DATE  | JOB_ID     | SALARY   | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven      | King        | SKING    | 515.123.4567       | 2003-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |            90 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 2005-09-21 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 2001-01-13 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 2006-01-03 | IT_PROG    |  9000.00 |           0.00 |        102 |            60 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 2007-05-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 2005-06-25 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 2006-02-05 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 2007-02-07 | IT_PROG    |  4200.00 |           0.00 |        103 |            60 |
|         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 2002-08-17 | FI_MGR     | 12008.00 |           0.00 |        101 |           100 |
|         109 | Daniel      | Faviet      | DFAVIET  | 515.124.4169       | 2002-08-16 | FI_ACCOUNT |  9000.00 |           0.00 |        108 |           100 |
|         110 | John        | Chen        | JCHEN    | 515.124.4269       | 2005-09-28 | FI_ACCOUNT |  8200.00 |           0.00 |        108 |           100 |
....................
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

Sample Solution:

-- Selecting all columns (*) from the 'employees' table
SELECT * 

-- Filtering rows based on the condition that the combination of 'salary' and 'manager_id' is equal to the result of a subquery
FROM employees 

-- Subquery to find the row where 'salary' is 3000 and 'manager_id' is 121
WHERE (salary, manager_id) =
(SELECT 3000, 121);

Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
187		Anthony		Cabrio		ACABRIO	650.509.4876	2007-02-07	SH_CLERK	3000.00	0.00	121		50

Code Explanation:

The said query in SQL that selects all columns from the 'employees' table. The result is filtered using the "WHERE" clause, which uses a subquery to compare the values of the "salary" and "manager_id" columns to the values "3000" and "121", respectively. The subquery in the parentheses retrieves a single row with these two values. The result of this query will be a table containing all columns for the employees whose salary is equal to 3000 and whose manager ID is equal to 121.

Visual Presentation:

SQL Subqueries: Display all the information of an employee whose salary and reporting person id is 3000 and 121 respectively

Alternative Statements:

Using JOIN:


SELECT e.*
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary = 3000 AND m.employee_id = 121;

Using IN Subquery:


SELECT *
FROM employees
WHERE (salary, manager_id) IN ((3000, 121));

Using EXISTS Subquery:


SELECT *
FROM employees e
WHERE e.salary = 3000 
  AND EXISTS (SELECT 1 FROM employees m WHERE m.employee_id = 121 AND e.manager_id = m.employee_id);

Practice Online