Oracle Employee Database Case Study: Where Conditions

In the previous article, we tried to fetch data by applying simple and basic queries. Now let's move ahead and explore more about where queries. This article will be based on fetching data using Where Conditions. 

All the queries we are going to execute in this Article will be solved independently by Employee Table.

Below are the queries with their solution to fetch the data.

QUERY 1:  LIST THE DETAILS ABOUT "SMITH"

As we all know that Smith belongs to the Last_Name category. So, we can fetch details about Smith using the following code:

Select * from Employee
where Last_Name='Smith';

OUTPUT:

QUERY 2: LIST OUT THE EMPLOYEES WHO ARE WORKING IN DEPARTMENT 20.

To find the values related to Department we have the Department_id column in the database. Below, is how to solve this particular query:

Select First_Name,
Last_Name,
Middle_Name from Employee
where Department_id=20;

OUTPUT:

QUERY 3: LIST OUT THE EMPLOYEES WHO ARE EARNING SALARY BETWEEN 3000 AND 4500.

We can perform this query by either using the < or > operator or if we want to find the solution for this query. We can also refer using BETWEEN Condition as follows:

Select * from employee
where Salary between 3000 AND 4500;

OUTPUT:

QUERY 4:  LIST OUT THE EMPLOYEES WHO ARE WORKING IN DEPARTMENT 10 OR 20.

we can use OR operator to find the solution to this query as:

Select * from Employee
where Department_id=10
OR
Department_id=20;

OUTPUT:

QUERY 5: FIND OUT THE EMPLOYEES WHO ARE NOT WORKING IN DEPARTMENT 10 OR 30.

NOT Operator basically Outputs the values that do not satisfy the given condition. So, NOT operator is used to solve the query as:

Select * from Employee
Where NOT(Department_id=10 Or Department_id=30);

OUTPUT:

QUERY 6: LIST OUT THE EMPLOYEES WHOSE NAME STARTS WITH 'J'.

Here, LIKE Operator can be used with % sign( that indicated containing zero or more character after). Follow the code below to perform the same operation:

Select Concat(Concat(First_Name,' '),Last_Name) AS Name
from Employee
Where First_name Like 'J%';

1) By using the Concat function we can join two strings together. As we can pass only 2 arguments in CONCAT function. We need to use nested CONCAT for joining 3 Strings ie. First_Name + (Space that is indicated by '  ')+ Last_Name.
2) By AS we set the alias as Name.

OUTPUT:

QUERY 7: LIST OUT THE EMPLOYEES WHOSE NAME STARTS WITH 'J' AND ENDS WITH 'H'

Performing the same task by using Like with % as:

Select Concat(Concat(First_Name,' '),Last_Name) AS Name
from Employee
Where First_name Like 'J%' AND Last_Name Like '%h';

OUTPUT:

QUERY 8: LIST OUT THE EMPLOYEES WHOSE FIRST NAME LENGTH IS 4 AND START WITH 'J'

In Oracle, LENGTH(COLUMN) is the function to find out the length of a particular attribute. So, we can use the following function to perform the following query:

Select Concat(Concat(First_Name,' '),Last_Name) AS Name
from Employee
where LENGTH(First_Name)=4 AND First_Name LIKE 'J%';

OUTPUT:

QUERY 9: LIST OUT EMPLOYEES WHO ARE WORKING IN DEPARTMENT 30 AND DRAW THE SALARIES OF MORE THAN 2000

We can use AND Operator to perform this query, as we need Employees who belong to department 30 and along with this they must have a salary more than 2000. 

Select * from Employee 
where Department_id=30 AND Salary>2000;

OUTPUT:

QUERY 10: LIST OUT THE EMPLOYEES WHO ARE NOT RECEIVING COMMISSION.

The employees who are not a part of receiving any commission can be considered to have null values in the particular column. So the code for this is as follows:

Select 
First_Name,Middle_Name,
Last_name, Comm 
from Employee
where Comm IS NULL;

OUTPUT: