Oracle Employee Database Case Study: Sub Queries

Ending the case study let's look forward to performing basic Sub Queries on the data present in the database. 

Below are different queries that are solved using subqueries:

QUERY 1: DISPLAY THE EMPLOYEE  WHO GOT THE MAXIMUM SALARY. 

This query needs to be solved using a subquery. We know that in Oracle we have an aggregate function MAX(), to find out the maximum among all the values given.

Select * from Employee
where salary=(select max(salary) from Employee);

OUTPUT:

QUERY 2: DISPLAY THE EMPLOYEES WHO ARE WORKING IN SALES DEPARTMENT.

Here, this query will be performed using two tables. Employee Table and Department table. Follow the below given code to solve the query:

Select First_Name,Middle_Name,Last_Name from Employee
where department_id=(select Department_id from department where Name='Sales')

OUTPUT:

QUERY 3: DISPLAY THE EMPLOYEES WHO ARE WORKING AS 'CLERK'.

Select First_Name,Middle_Name,Last_Name from Employee
where job_id=(select job_id from job where designation='Clerk');

OUTPUT:

QUERY 4: DISPLAY THE LIST OF EMPLOYEES WHO ARE LIVING IN "DALLAS".

The query will be solved using 2 subqueries. The code is as follows:

Select First_Name,Middle_Name,Last_Name from Employee
where department_id=( select Department_id from department
where location_id=(select Location_id from Location where City='Dallas'));

OUTPUT:

QUERY 5: FIND OUT NO. OF EMPLOYEES WORKING IN "RESEARCH" DEPARTMENT.

Select Count(*) AS TOTAL_IN_RESEARCH from Employee
where Department_id=(Select department_id from department where name='Research');

OUTPUT:

QUERY 6:UPDATE THE EMPLOYEES SALARIES, WHO ARE WORKING AS CLERK ON THE BASIS OF 10%. 

update Employee
Set Salary= (10/100 * Salary)+ Salary
where job_id=(Select job_id from job where designation='Clerk');
Select * from employee;

OUTPUT:

You can see, that salary of John Smith has been incremented by 10%. Previously it was 800, and after incrementation it became 880.

QUERY 7: DELETE THE EMPLOYEES WHO ARE WORKING IN SALES DEPARTMENT. 

To perform this query, firstly you need to disable the primary key constraint in the Employee table as:

alter table Employee disable primary key cascade;

OUTPUT: 

Now to perform this query. Follow the code below:

delete from employee
where department_id = (select department_id from department
where name ='Sales');

OUTPUT:

QUERY 8: DISPLAY THE SECOND HIGHEST SALARY DRAWING EMPLOYEE DETAILS. 

select * from employee 
where salary=(select max(salary) from employee 
where salary<(select max(salary) from employee));

OUTPUT:

QUERY 9: DISPLAY THE N'TH HIGHEST SALARY DRAWING EMPLOYEE DETAILS.

Select * from employee e1
where &N-1=(select count(distinct salary) from employee e where e.salary>e1.salary)

OUTPUT:
 

QUERY 10: LIST OUT THE EMPLOYEES WHO EARN MORE THAN EVERY EMPLOYEE IN DEPARTMENT 40.

Select * from employee where 
Salary>all(select Salary from employee where department_id=40);

OUTPUT:

QUERY 11: LIST OUT THE EMPLOYEES WHO EARN MORE THAN THE LOWEST SALARY IN DEPARTMENT 30.

select * from employee where
 salary>any(select salary from employee where department_id=30);

OUTPUT:

QUERY 12: FIND OUT WHICH DEPARTMENT DOES NOT HAVE ANY EMPLOYEES. 

Select * from Department 
where department_id NOT IN( select Department_Id from employee);

OUTPUT:

QUERY 13: FIND OUT THE EMPLOYEES WHO EARN GREATER THAN THE AVERAGE SALARY FOR THEIR DEPARTMENT.

 select * from employee e where 
 salary>(select avg(salary) from employee 
 where department_id=e.department_id);

OUTPUT: