Skip to main content

Logical Operator and other Operators uses in Oracle SQL #Diksha Oracle Training Centre

  

Logical Operator and other Operators uses in Oracle SQL.

 

Hello Everyone,

Today I will discuss about Logical Operator used in Oracle SQL. This Topic is important as you can use Logical Operator in several SQL queries to get the result set as per your requirement.  I suggest you to please go through my previous blogs which you will able to see in archive folder so that you can use Logical Operator in SQL Queries. Let’s start with Logical Operators used in SQL. SQL operators are reserved keywords which basically used in the WHERE clause of a  SQL Statement to perform arithmetic, logical and comparison operations.

Since, there are different types of operators in SQL, let us understand different type of operator and its uses in SQL.

Below are the Logical Operators used in SQL .

AND

OR

NOT

BETWEEN

LIKE

IN

EXIST

ALL

ANY

  

Uses of above operator in SQL

1.      AND Operator: AND operator displays the records, which satisfy all the condition separated by AND, and give the output True. Also this operator filters records that rely on more than one condition.

Syntax :

  SELECT Column1, Column2

FROM TableName

WHERE Condition1 AND Condition2 AND Condition3 ...;

--- Note for filter we can put more AND operator to filter the data as per requirement.

 

Let’s take example, I have created  2 table employee and department where I want to filter the data by using AND operator.

Note : For reference you can check my previous blogs for aggregate function and Sub query/Joins blog where I defined functions as well as IN, ANY and ALL operator.

Below are the Query for your reference for AND operator.

select dept_name, salary

from department

where SALARY >=45000

AND emp_id IN(1000,2000,3000,4000)

AND dept_name IN ('IT','SALES','HR','FINANCE','MARKETING');

 

If I want to get total amount as salary for each department and suppress duplicate records we can use below query to get the result .

select  SUM(salary) TOTAL_SALARY , dept_name

from department

where SALARY >=30000

AND emp_id IN(1000,2000,3000,4000)

AND dept_name IN ('IT','SALES','HR','FINANCE','MARKETING')

GROUP BY DEPT_NAME;

Below is the output of both Queries:

 


Another Query for AND operator by using Join :

     select  sum(a.salary) ,a.dept_name ,

            b.emp_name,b.designation

      from department a , employee b

      where a.emp_id=b.emp_id

      AND a.dept_name IN ('IT','SALES','HR','FINANCE','MARKETING')

      AND b.emp_id IN(1000,2000,3000,4000)

group by a.dept_name , b.emp_name,b.designation

      HAVING sum(a.salary) >=40000;

 

Below is the output for above query :



 

 OR Operator: OR operator displays all the records which satisfy any of the conditions separated by OR in query and give the output TRUE. In another way if any condition satisfied it will give the output.

Syntax in SQL for OR Operator:

SELECT Column1, Column2, ..., ColumnN

FROM TableName  WHERE Condition1 OR Condition2 OR ...;

 

Let's write query by using tables with both simple as well as join query to see the output.

select dept_name, salary

from department

where salary>= 50000

OR emp_id=1000

OR dept_name IN ('IT','SALES','HR','FINANCE','MARKETING');

Note : Here any of the condition is true it will give the result accordingly.

Output of above Query is as below:

 


Using Join with Aggregate function for OR operator.

  select  sum(a.salary) ,a.dept_name ,

            b.emp_name,b.designation

      from department a , employee b

      where a.emp_id=b.emp_id

      OR a.dept_name ='IT'

      OR b.emp_id IN(1000,2000,3000,4000)

group by a.dept_name , b.emp_name,b.designation

      HAVING sum(a.salary) >=40000;

Below is the output for above query :

 


 

Here you clearly see the difference between AND /OR operator as in AND operator it will check all the condition whereas in OR operator any condition matches it will give the result.

NOT Operator: The NOT operator is used, when you want to display the records which do not satisfy a condition/criteria.

 

Syntax

SELECT Column1, Column2, ...

FROM TableName

WHERE NOT Condition;

 

Let's write query by using tables with both simple as well as join query to see the output.

select dept_name, salary

from department

where NOT dept_name IN('IT','HR') ;

Here you see the output apart from IT and HR all the records displayed by using NOT operator.

Below is the Output of above Query for your reference.

 


Note: You can also combine the all three operators and write the query:

Query:

SELECT * FROM DEPARTMENT

WHERE NOT EMP_ID='1000'

AND (EMP_ID=2000

OR salary >40000);

Below is the output of above query.

 


 

Hope you are able to understand AND , OR and NOT operator in SQL .

Below are some different operator used in SQL.

BETWEEN Operator:

The BETWEEN operator is used, when you want to select values within a given range. In Between  both the starting and ending values are considered.

Below is the query  we can use for Between Operator.

select * from department

 where salary between 40000 AND 120000 ;

Output of the above query is as below

 


 

LIKE Operator

 

The LIKE operator also known as wild card  used in a WHERE clause to search for a specified pattern . There are mainly two wildcards that are used in the LIKE operator:

%   – It’s for zero or more character

_    – It’s matches exactly one character.

Syntax

SELECT ColumnName

FROM TableName where column_name like pattern;

 

select * from department

 where dept_name like 'F%';

 

Output of above query is as below :

 


Like Operator Condition and description and when to use like is as below :

 

If we put condition in SQL --WHERE CustomerName LIKE 'a%

output  --Finds any values that start with “a”

 

If we put condition in SQL -- WHERE CustomerName LIKE '%b'

 output --Finds any values that end with “b”

If we put condition in SQL -- WHERE CustomerName LIKE '%abc%'

output -- Finds any values that have “abc” in any position

If we put condition in SQL -- WHERE CustomerName LIKE '_c%'

output -- Finds any values that have “c” in the second position.

If we put condition in SQL -- WHERE CustomerName LIKE 'd_%_%'

output--Finds any values that start with “d” and are at least 3 characters in length

If we put condition in SQL --WHERE ContactName LIKE 'a%e'

output--Finds any values that start with “a” and end with “e”   

 

EXIST Operator:

 

Exist operator is basically used to test whether record exist or not.

 

Below is the syntax of SQL using Exist operator.

 

SELECT dept_name,salary

FROM department

WHERE EXISTS (SELECT emp_id FROM

department where emp_id=1000 AND dept_name IN('IT','HR') );

 

Output of above Query is as below .

 


IN SQL we also use below operators for calculation and logical use:

 

Arithmetic operator :

 %  -- Modulus

 / -- Divide

 * --Multiplication

 -  -- Subtraction

 + -- Addition

 

This operator we can use in SQL for calculation and check process in select statement

 

Bitwise Operator :

^  --  Biitwise Excusive OR

|   -- Bitwise OR

 

& - Bitwise AND

 

 Comparison Operator

 

< >  -- Not equals to

 

<=  -- Less than or equals to

 

> = -- Greater than equals to

 

< --     Less than

 

> --     Greater than

 

= --    Equals to

 

Compound Operators :

 

|*=    -- Bitwise or Equals , ^-= -- Bitwise exclusive Equals , &= -- Bitwise and equals , %= --

Modulo equals , /= -- Divide equals , *= Multiplies equals etc.

All this operators we can use in SQL statement , sub queries , co-related sub queries for calculation and Logical issues as per our Requirement.

 

IN/ANY and ALL operator:

 

Please see my previous blog as I well defined all the operator with examples and explanation. It’s big so you can follow my below blog for your reference.

 

https://dikshaoracletraining.blogspot.com/2020/10/sql-subquery-and-co-related-subquery-in.html

 

This all are the operators commonly used  in SQL , Go through all the Operators and try to write some queries using Logical operators .

Please go through this blog carefully and let me know if you have any questions. This Topic is important for candidate who is preparing for interview and also good for students to explore logical operator in SQL Query.

Thanks

Comments

Post a Comment

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li