SQL Exercise: List employee id of all the departments 1001 and 2001
SQL employee Database: Exercise-94 with Solution
[An editor is available at the bottom of the page to write and execute the scripts.]
94. From the following table, write a SQL query to find those employees who work in the department ID 1001 or 2001. Return employee ID, employee name, department ID, department location, and department name.
Sample table: employees
Sample table: department
Sample Solution:
SELECT e.emp_id,
e.emp_name,
e.dep_id,
d.dep_location,
d.dep_name
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
AND e.dep_id IN (1001,
2001);
Sample Output:
emp_id | emp_name | dep_id | dep_location | dep_name --------+----------+--------+--------------+---------- 68319 | KAYLING | 1001 | SYDNEY | FINANCE 67832 | CLARE | 1001 | SYDNEY | FINANCE 65646 | JONAS | 2001 | MELBOURNE | AUDIT 68736 | ADNRES | 2001 | MELBOURNE | AUDIT 69324 | MARKER | 1001 | SYDNEY | FINANCE 67858 | SCARLET | 2001 | MELBOURNE | AUDIT 69062 | FRANK | 2001 | MELBOURNE | AUDIT 63679 | SANDRINE | 2001 | MELBOURNE | AUDIT (8 rows)
Explanation:
The said query in SQL that selects the emp_id, emp_name, dep_id, dep_location, and dep_name from the employees and department tables where the dep_id in the employees table is either 1001 or 2001.
The WHERE clause makes an implicit join between 'employees' and 'department' tables based on the dep_id column and includes only rows where the dep_id in the 'employees' table is either 1001 or 2001.
Relational Algebra Expression:

Relational Algebra Tree:

Practice Online
Sample Database: employee

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: List id, name, department id, location of all employees.
Next SQL Exercise: List employees salary within min_salary and max_salary.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Concatenate strings of a string field in a PostgreSQL 'group by' query:
Input:
ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave
SELECT company_id, string_agg(employee, ', ') FROM mytable GROUP BY company_id;
Output:
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave
Database: PostgreSQL
Ref: https://bit.ly/2XTiRjq
- 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
- JavaScript String Exercises
- JavaScript HTML Form Validation
- 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