SQL Exercises: Location where department number 30 is located

SQL SUBQUERY: Exercise-38 with Solution

From the following table, write a SQL query to find departments for a particular location. The location matches the location of the department of ID 30. Return department name and department ID.

+---------------+----------------------+------------+-------------+
| 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 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |          0 |        1700 |
|           130 | Corporate Tax        |          0 |        1700 |
|           140 | Control And Credit   |          0 |        1700 |
|           150 | Shareholder Services |          0 |        1700 |
|           160 | Benefits             |          0 |        1700 |
|           170 | Manufacturing        |          0 |        1700 |
|           180 | Construction         |          0 |        1700 |
|           190 | Contracting          |          0 |        1700 |
|           200 | Operations           |          0 |        1700 |
|           210 | IT Support           |          0 |        1700 |
|           220 | NOC                  |          0 |        1700 |
|           230 | IT Helpdesk          |          0 |        1700 |
|           240 | Government Sales     |          0 |        1700 |
|           250 | Retail Sales         |          0 |        1700 |
|           260 | Recruiting           |          0 |        1700 |
|           270 | Payroll              |          0 |        1700 |
+---------------+----------------------+------------+-------------+

Sample Solution:

-- Selecting specific columns (department_name, department_id) from the 'departments' table
SELECT department_name, department_id

-- Filtering rows based on the condition that the 'location_id' matches the result of a subquery
FROM departments 

-- Subquery to find the 'location_id' where the 'department_id' is 30 in the 'departments' table
WHERE location_id = 
        (
          SELECT location_id 
            FROM  departments  
             WHERE department_id = 30
        );

Sample Output:

department_name	department_id
Administration		10
Purchasing		30
Executive		90
Finance			100
Accounting		110
Treasury		120
Corporate Tax		130
Control And Credit	140
Shareholder Services	150
Benefits		160
Manufacturing		170
Construction		180
Contracting		190
Operations		200
IT Support		210
NOC			220
IT Helpdesk		230
Government Sales	240
Retail Sales		250
Recruiting		260
Payroll			270

Code Explanation:

The said query in SQL that retrieves the department name and department id of all departments located at the same location as department id 30.
In this case, the WHERE clause checking if the location_id of each department matches the location_id of department id 30. It uses a subquery in the WHERE clause to retrieve the location_id of department id 30, and then compare that value with the location_id of each department in the query.

Visual Presentation:

Alternative Statements:

Using a Self-Join:

SELECT d1.department_name, d1.department_id
FROM departments d1
JOIN departments d2 ON d1.location_id = d2.location_id
WHERE d2.department_id = 30;

Using EXISTS:

SELECT department_name, department_id
FROM departments d1
WHERE EXISTS (
    SELECT 1
    FROM departments d2
    WHERE d2.department_id = 30 AND d1.location_id = d2.location_id
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Employees earn more than the maximum salary.
Next SQL Exercise: Department where the employee works with ID 201.