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;
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;
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;
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;
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';
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;
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;
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';
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;
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;
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;
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;
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;
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;
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;
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';