Oracle Employee Database Case Study: Set Operations

As we have discussed in the previous articles about the SET operators. You can click here to check about SET Operators in Oracle.

Let's move ahead with the case study and solve queries using SET Operator as follows:

QYERY 1:LIST OUT THE DISTINCT JOBS IN SALES AND RESEARCH DEPARTMENTS

Distinct Jobs here means that there should not be any duplication. We will solve this query using the UNION Operator in Oracle.

Select * from job where job_id in (Select job_id from employee 
where department_id=(select department_id from department where name='Sales'))
UNION 
Select * from job where job_id in (Select job_id from employee 
where department_id=(select department_id from department where name='Research'))

Output
set_operator-1

QUERY 2:LIST OUT ALL THE JOBS IN SALES AND RESEARCH DEPARTMENTS

In this we need to list all the jobs present in the Sales and Research Departments. So, here the result can contain duplications too. As same job designations can be present in 2 different departments.

Therefore, we will proceed with this query using UNION ALL Operator in Oracle.

Select * from job where job_id in (Select job_id from employee 
where department_id=(select department_id from department where name='Sales'))
UNION ALL 
Select * from job where job_id in (Select job_id from employee 
where department_id=(select department_id from department where name='Research'))

Output
set_operator-2

QUERY 3:LIST OUT THE COMMON JOBS IN RESEARCH AND ACCOUNTING DEPARTMENTS IN ASCENDING ORDER

This Query demands to find common Jobs. Hence, we will use INTERSECTION Operator to solve this:

Select designation from job where job_id in (Select job_id from employee 
where department_id=(select department_id from department where name='Research'))
INTERSECT
Select designation from job where job_id in (Select job_id from employee 
where department_id=(select department_id from department where name='Operation')) 
order by designation

Output
set_operator-3