Arithmatic Operators
SQL Tutorials

What are Operators in SQL | SQL Operators

Amit S March 28, 2017

Operators in SQL:

In previous article i have explained the Functions in SQL with real life examples.The another important topic while learning SQL is Operators in SQL.Operators are nothing but the the statement which are used to perform SQL Operations.SQL Operators are mostly used in Where clause of SQL statement.Operators are basically used to specify the conditions to SQL Statement.

“Operators are nothing but the the statement which are used to perform SQL Operations”

There are following different types of Operators in SQL:

1. Arithmetic Operators

2.Logical Operators

3.Comparison Operators

4.Negation Operators

CLICK HERE TO GET INFORMATION ABOUT NORMALIZATION

1. Arithmetic Operators in SQL:

Operators in SQL

Arithmetic operators are basically used to perform the arithmetic calculations between 2 variables or numbers.

Following are arithmetic Operators:

1.Plus Operator (+ Operator):

Plus Operator is used to perform the addition of two numbers or variables.

Example:

Select 1+1 as Sum from Dual;

Will give answer as Sum column and value is 2.

2.Minus Operator (- Operator):

Minus Operator is used to perform the subtraction of two numbers or variables.

Example:

Select 1+1 as Subtract from Dual;

Will give answer as Subtract column and value is 0.

3.Multiplication Operator (* Operator):

Multiplication Operator is used to perform the multiplication of two numbers or variables.

Example:

Select 1*1 as Multiply from Dual;

Will give answer as Multiply column and value is 1.

4.Division Operator (/ Operator):

Division Operator is used to perform the division of two numbers or variables.

Example:

Select 1/1 as Division from Dual;

Will give answer as Division column and value is 1.

2.Logical Operators in SQL:

Logical Operators are used to perform the logical operations between two variables.

1.AND Operator:

AND operator is used to give the multiple conditions at a same time to SQL Statement.The And operator is used in where clause of SQL statement.

Example:

Select * from Employee where name=’Divya’ and ‘Yodhini’;

It will display all the records from Employee table where name is Divya and Yodhini.

2.OR Operator:

OR Operator is used to combine multiple conditions in the database.It simply means whether this or this.

Example:

Select * from Employee where name=’Divya’ OR ‘Yodhini’;

It will display all the records from Employee table where name is Divya OR Yodhini.

3.IN Operator:

IN Operator is used to fetch the 2 or more specific records from the database.

Example:

Select * from Employee where name IN (‘Divya’,’Yodhini’);

It will display specific records for the employees where name is Divya and Yodhini.

4.ALL Operator:

ALL Operator is used to compare all the values from another dataset and fetch the specific records from the table.

Example:

Select * from Student where 10000 >ALL (Select Fees from Student_Fees);

The above statement will display all the records from Student table where Student fees is > 10000 which we are fetching from different table named ‘Student_Fees’

5.Any Operator:

Any Operator is used to compare the values from Any value from another table with that condition.

Example:

Select * from Student where 10000 >ANY (Select Fees from Student_Fees);

The above statement will display ANY of the records from Student table where Student fees is > 10000 which we are fetching from different table named ‘Student_Fees’

6.Between..And Operator:

Between..And Operator is used to fetch the records from the table where values between first_value (minimum value) and second value(maximum value).

Example:

Select * from Student where fees between 1000 and 2000;

The above statement will fetch all the Students information from student table where Fees is greater than 1000 and less than 2000.

7.LIKE Operator:

Like Operator is most used operator which is used to compare the values from the table using wildcard operators like ‘%’ ‘_’ etc.

Example:

select * from Student where name like ‘Ami%’;

All student information will be displayed where name starts with Ami.

select * from Student where name like ‘A_ _ _’;

All Students information will be displayed where name starts with A which has length 4.

8.Exist Operator:

Exist Operator is used to specify the presence of specific row which has some specific condition.

Select * from Student where name Exist(‘Amit’);

The above statement will display the records where name Existing Amit.

CLICK HERE TO GET MORE INFORMATION ABOUT PIVOT IS SQL

3.Comparison Operators in SQL:

Comparison Operators are used to compare different variables or different values from the database table.

1.Equal To (=):

Checks the values between 2 variables or operands are equal to or not.If the values are equal to then it will return true else false.

Example:

Select * from Employee where EMPNO=10;

The statement will fetch the record of employee whose EMPNO is 10.

2.Not Equal to(!=,<>):

Checks the values between 2 variables or operands are equal to or not.If the values are not equal to then it will return true else false.

Example:

Select * from Employee where EMPNO!=10;

The statement will fetch all records of employee whose EMPNO is not 10.

3.Greater than (>):

Checks the values between 2 variables or operands if Left side operand or variable value is Greater than Right side operand or variable then it will fetch true or false.

Example:

Select * from Employee where Salary >10000;

The statement will fetch all records of employee whose Salary is greater than 10000.

4.Less than (<):

Checks the values between 2 variables or operands if Left side operand or variable value is less than Right side operand or variable then it will fetch true or false.

Example:

Select * from Employee where Salary <10000;

The statement will fetch all records of employee whose Salary is less than 10000.

5.Greater than and Equal to (>=):

Checks the values between 2 variables or operands if Left side operand or variable value is Greater than  and Right side operand or variable then it will fetch true or false.

Example:

Select * from Employee where Salary =>10000;

The statement will fetch all records of employee whose Salary is greater than and equal to 10000.Using this operator the employees whose salary is equal to 10000 are also fetched.

6.Less than (<=):

Checks the values between 2 variables or operands if Left side operand or variable value is less than and equal to Right side operand or variable then it will fetch true or false.

Example:

Select * from Employee where Salary <=10000;

The statement will fetch all records of employee whose Salary is less than and 10000.Using this operator the employees whose salary is equal to 10000 are also fetched.

4.Negation Operators in SQL:

Negation Operators are operators which has negation condition like ‘Not’ Condition.

NOT Operator:

Not operator is negation operator which reverses the meaning of logical operator.The Not operator is used before Logical operator like Not In,Not Exist etc.

Example:

Select * from Student where name not in (‘Divya’);

The above statement will fetch all the records from Student table where name is not Divya.

CLICK Here to get 20 Most important SQL Queries for Interview

Check  out your SQL topics :

Unix Tutorials :

Oracle Business Intelligence Tutorial :

Click Here for SQL interview Questions

Hope You like this article on Operators in SQL.If you like this article kindly comment in comment section.

HOME

2 Replies to “What are Operators in SQL | SQL Operators”

Comments are closed.