w3resource

SQL Subquery Exercises: Display the department name and Id for all departments where they located, that Id is equal to the Id for the 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.

Sample table: departments


Sample Solution:

SELECT department_name, department_id
  FROM departments 
   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

Pictorial Presentation:

SQL Subqueries: Display the department name and Id for all departments where they located, that Id is equal to the Id for the location where department number 30 is located.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the department name and Id for all departments where they located, that Id is equal to the Id for the location where department number 30 is located - Duration

Rows:

Query visualization of Display the department name and Id for all departments where they located, that Id is equal to the Id for the location where department number 30 is located - Rows

Cost:

Query visualization of Display the department name and Id for all departments where they located, that Id is equal to the Id for the location where department number 30 is located - Cost

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

Previous: From the following table, write a SQL query to find those employees who earn more than the maximum salary of a department of ID 40. Return first name, last name and department ID.
Next: From the following table, write a SQL query to find those employees who work in that department where the employee works of ID 201. Return first name, last name, salary, and department ID.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

Ref: https://bit.ly/3cayyhJ