w3resource

SQL Exercises: Display the details of departments managed by Susan


From the following table, write a SQL query to find the departments managed by Susan. Return all the fields of departments.

Sample table: departments

+---------------+----------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME      | MANAGER_ID | LOCATION_ID |
+---------------+----------------------+------------+-------------+
|            10 | Administration       |        200 |        1700 |
|            20 | Marketing            |        201 |        1800 |
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|            60 | IT                   |        103 |        1400 |
|            70 | Public Relations     |        204 |        2700 |
|            80 | Sales                |        145 |        2500 |
|            90 | Executive            |        100 |        1700 |
|           100 | Finance              |        108 |        1700 |
......
+---------------+----------------------+------------+-------------+

View the table

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 'departments' table
SELECT * 

-- Filtering rows from the 'departments' table based on the condition that the 'manager_id' is in the result set of a subquery
FROM departments 

-- Subquery to find 'employee_id' values where the 'first_name' is 'Susan' in the 'employees' table
WHERE manager_id IN 
	(SELECT employee_id 
	 FROM employees 
	 WHERE first_name='Susan');

Sample Output:

department_id	department_name	manager_id	location_id
40		Human Resources	203 		2400

Code Explanation:

The said query in SQL that selects all the rows from the 'departments' table where the "manager_id" column matches any of the values returned by the subquery. The subquery selects the "employee_id" values from the 'employees' table where the "first_name" column equals 'Susan'.

Visual Presentation:

SQL Subqueries Exercises: Display the details of departments managed by Susan.

Alternative Solutions:

Using JOINs:


SELECT d.*
FROM departments d
JOIN employees e ON d.manager_id = e.employee_id
WHERE e.first_name = 'Susan';

Using EXISTS:


SELECT d.*
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.employee_id = d.manager_id
    AND e.first_name = 'Susan'
);

Using Scalar Subquery in the WHERE Clause:


SELECT * 
FROM departments 
WHERE manager_id = (
    SELECT employee_id 
    FROM employees 
    WHERE first_name = 'Susan'
);

Go to:


PREV : Employees who earn second lowest salary of all.
NEXT : Highest salary drawar in a department.


Practice Online



Query Visualization:

Duration:

Query visualization of Display the details of departments managed by Susan - Duration

Rows:

Query visualization of Display the details of departments managed by Susan - Rows

Cost:

Query visualization of Display the details of departments managed by Susan - Cost

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.



Follow us on Facebook and Twitter for latest update.