Oracle Employee Database Case Study: Joins

As we all are done with Group by and Having Clause queries, Let's jump to another section. Here, we need to fetch information not directly from a single table. Therefore we are now going to see how Joins in Oracle works.

QUERY 1: LIST OUT EMPLOYEES WITH THEIR DEPARTMENT NAMES:

We see that Employee Table that we have contains Department ID that acts as a foreign key for Department Id in Department Table. Department Table too contains Department names if particular department IDs. So we can perform the query by the following code:

Select * from Employee E NATURAL JOIN Department D;

Output

QUERY 2: DISPLAY EMPLOYEES WITH THEIR DESIGNATIONS.

Now, Designation lies under Job table. Also, we have Job ID that acts as a primary key in Job table and a foreign key in Employee Table. So we can perform this query as:

Select E.First_name,E.job_id,J.designation from
Employee E Inner join Job J On E.job_id=J.job_id;

Output

QUERY 3: DISPLAY THE EMPLOYEES WITH THEIR DEPARTMENT NAMES AND REGIONAL GROUPS

Here we need to combine three tables to solve this query. Employee, Department, and Location are the three tables that will help us to solve this query.

Select E.First_Name,E.Middle_name, E.Last_name, D.Name,L.city 
From Department D,Employee E, Location L
where D.Department_id=E.Department_id AND D.Location_id=L.Location_id;

Output

QUERY 4: HOW MANY EMPLOYEES WHO ARE WORKING IN DIFFERENT DEPARTMENTS AND DISPLAY WITH DEPARTMENT NAMES

In this query, we are asked to find the total number of employees present with a particular department name. We have this as a first method to execute  this query:

Select D.Name,count(*) No_of_Employee from Employee E,Department D
where E.department_id=D.department_id
group by D.Name
ORDER BY D.Name;

This is the implicit method, wherein we use to perform simple join without using keywords. Therefore we use WHERE and FROM to perform a query. However, this way of executing queries is now depreciated. Nowadays, the Explicit method(Specifying Keywords instead of using FROM /WHERE) is used to perform the same task.

Below is the explicit method for this query to be solved:

Select D.Name,count(*) No_of_Employee from Employee E INNER JOIN Department D
ON E.department_id=D.department_id
group by D.Name
ORDER BY D.Name;

Output

QUERY 5: HOW MANY EMPLOYEES, WHO ARE WORKING IN THE SALES DEPARTMENT

Select D.name DEPARTMENT, Count(*) TOTAL_EMPLOYEE from 
Employee E INNER JOIN Department D
On E.department_id=D.department_id
group By D.name having D.name='Sales';

Output

QUERY 6: WHICH IS THE DEPARTMENT HAVING GREATER THAN OR EQUAL TO 2 EMPLOYEES AND DISPLAY THE DEPARTMENT NAMES IN ASCENDING ORDER

Select D.name DEPARTMENT, Count(*) TOTAL_EMPLOYEE from 
Employee E INNER JOIN Department D
On E.department_id=D.department_id
Group by D.name having Count(*)>=2
Order by D.name;

Output

QUERY 7: HOW MANY JOBS IN THE ORGANIZATION WITH DESIGNATIONS

Select J.designation , count(*) from Employee E INNER JOIN Job J
ON E.Job_id=J.Job_id
Group By J.designation;

Output

QUERY 8: HOW MANY EMPLOYEES ARE WORKING IN "Chicago".

Select L.city , Count(*) as EMPLOYEE from Employee E,Department D,Location L
where L.Location_id=D.Location_id AND D.department_id=E.department_id
Group by L.city having L.city='Chicago';

Output

QUERY 9: DISPLAY THE EMPLOYEE DETAILS WITH SALARY GRADES.

To perform this query, we have another table named Salary. Following is the code to create and insert values into Salary Table:

Create Table Salary(grade number(20), Min_salary number(20), Max_salary number(20));
insert into Salary Values(1,500,1000);
insert into Salary Values(2,1000,2000);
insert into Salary Values(3,2000,3000);
select * from Salary;

Output

Now that we are done with the creation of the Salary table, let's find a solution to the above query as:

Select * from Employee E INNER JOIN Salary S
ON E.salary between Min_salary AND Max_Salary; 

Output

QUERY 10: LIST OUT THE NO. OF EMPLOYEES ON GRADE-WISE. 

Select S.grade,Count(*) EMPLOYEES from Employee E INNER JOIN Salary S
ON E.salary between Min_salary AND Max_Salary
GROUP BY S.grade; 

Output

QUERY 11: DISPLAY THE EMPLOYEE SALARY GRADES AND NO. OF EMPLOYEES BETWEEN 2000 TO 3000 RANGE OF SALARY.

Select S.grade,Count(*) EMPLOYEES from Employee E INNER JOIN Salary S
ON E.salary between Min_salary AND Max_Salary
GROUP BY S.grade having s.grade=3; 

Output

QUERY 12: DISPLAY THE EMPLOYEE DETAILS WITH THEIR MANAGER NAMES. 

Here, We need to work on those columns that occur as Primary key and foreign key in the same table. So, we will create a duplicate copy of the same table by the use of an alias. follow the code for the same:

Select 
CONCAT( E.first_name,(CONCAT( ' ',E.Last_name) )) AS Manager, 
CONCAT(A.First_name,(CONCAT(' ',A.Last_name))) AS Employee,
E.job_id, E.Hire_date, E.salary,E.Comm, E.department_id
from employee E inner Join Employee A
On E.ID=A.manager_id;

Output:

QUERY 13: DISPLAY THE EMPLOYEE DETAILS WHO EARN MORE THAN THEIR MANAGERS SALARIES. 

Select E.First_Name,E.Middle_Name, 
E.Last_name,E.Hire_Date, E.Salary, E.Department_id,E.ID,E.manager_id
from employee E inner Join Employee A
On E.ID=A.manager_id AND A.salary<E.salary;

Output

QUERY 14: SHOW THE NO. OF EMPLOYEES WORKING UNDER EVERY MANAGER. 

Select E.last_Name as EMPLOYEE,A.Last_Name as manager,Count(*)
From employee E inner join Employee A
On E.ID=A.manager_id
Group By E.Last_name,A.Last_name;

Output

QUERY 15: DISPLAY ALL EMPLOYEES IN SALES OR OPERATION DEPARTMENTS. 

Select D.name, Count(*)EMPLOYEE from Employee E INNER JOIN Department D
on E.department_id=D.department_id
group by D.Name having D.Name='Sales' OR D.Name='Operations';

Output