SQL Operators are symbols that instructs the compiler to execute some particular logical manipulation and mathematical operation. SQL has some particular operators by default, such as ## **SQL Operators**

- Arithmetic operators
- Comparison operators
- Relational operators
- String operators
- Logical operators

Arithmetic operators execute one or more numeric values and returns the output of a numeric value. The following are the arithmetic operators:

Operator | Description | Example |
---|---|---|

+ | Performs addition of two numbers | A+B,Example 10+5=15 |

- | Performs subtraction of two numbers | A-B,Example 10-5=5 |

* | Performs multiplication of two numbers | A*B,Example 10*5=50 |

/ | Performs division of two numbers | A/B,Example 10/5=2 |

The following is an example for arithmetic operators.
[c]
sql> select (20 + 4) as addition;
24
[/c]
The above example explains addition of two numbers such as a=20 and b=4, then it will choose those numbers and display the outputs.

Comparison operators compares one expression with another, thereby giving a consistent result as NULL, TRUE OR FALSE.

Operator | Description |
---|---|

BETWEEN | Check whether the given value is in the range between min and max. |

IN | The IN operator tests a collection of membership. |

LIKE | Compares a string, character or CLOB values. |

IS NULL | IS NULL operator gives back the boolean result i.e, if the value is NULL, it returns TRUE or if the value is not NULL, it returns FALSE. |

The below example describe the execution of Between operator.
[c]sql> select * from employee;
+--------+--------+--------+
| emp_id | ename | salary |
+--------+--------+--------+
| 1001 | Mike | 15000 |
| 1002 | Martin | 16000 |
| 1003 | Jack | 17000 |
| 1004 | James | 18000 |
| 1005 | Kite | 19000 |
+--------+--------+--------+
5 rows in set (0.00 sec)
sql> select * from employee where salary between 16000 and 18000;
+--------+--------+--------+
| emp_id | ename | salary |
+--------+--------+--------+
| 1002 | Martin | 16000 |
| 1003 | Jack | 17000 |
| 1004 | James | 18000 |
+--------+--------+--------+
3 rows in set (0.00 sec)
sql> select * from employee where salary between 15000 and 17000;
+--------+--------+--------+
| emp_id | ename | salary |
+--------+--------+--------+
| 1001 | Mike | 15000 |
| 1002 | Martin | 16000 |
| 1003 | Jack | 17000 |
+--------+--------+--------+
3 rows in set (0.00 sec)[/c]
By viewing the above example, the concept of Between operator will be understood.

Relational operators analyse two explanations and gives back the outcome in the form of boolean operation.

Operator | Description | Example |
---|---|---|

= | Analyse whether the two values are True or False, if true, then the case will be true. | A=B |

< | Check whether the left esteem is not exactly the right esteem, assuming genuine, then the case turns out to be valid. | A<B |

> | Examine whether the left esteem is more prominent than the right esteem, assuming genuine, then the case will be valid. | A>B |

<= | Examine whether the left esteem is not exactly or equivalent to the right esteem, if yes, then the case turns out to be valid. | A<=B |

>= | Investigate whether the left esteem is more noteworthy than or equivalent to the right esteem, assuming genuine, then the case turns out to be valid. | A>=B |

By viewing the below example, the concept of Where Clause can be easily understood.
[sql]
sql> select * from employee;
+--------+-------+-------+
| emp_id | ename | sal |
+--------+-------+-------+
| 1001 | mike | 12000 |
| 1002 | rambo | 13000 |
+--------+-------+-------+
2 rows in set (0.00 sec)
sql> select * from employee where ename='rambo';
+--------+-------+-------+
| emp_id | ename | sal |
+--------+-------+-------+
| 1002 | rambo | 13000 |
+--------+-------+-------+
1 row in set (0.00 sec)
sql> select * from employee where sal=12000;
+--------+-------+-------+
| emp_id | ename | sal |
+--------+-------+-------+
| 1001 | mike | 12000 |
+--------+-------+-------+
1 row in set (0.00 sec)
[/sql]
The above example tells that, when SQL Where Clause operation is performed on a column name like rambo, then it automatically displays the entire details of rambo like emp_id and salary.And then in another query where clause is performed on column name like salary,then it automatically displays the entire details of that employee like emp_id,ename and salary.

Logical operator operates on two operands and returns a boolean value as the output. The accessible logical operators are AND, OR and NOT.

Operator | Description |
---|---|

AND | It is called as logical AND operator. If two values are true, then the case becomes true. |

OR | It is called as logical OR operator, suppose any one value is true, then the case become true. |

NOT | It is called as logical NOT operator, if a case is true, then the logical NOT operator will make it false. |

By viewing the below example the concept of SQL AND operator an be easily understood.
[c]
SQL>Select * from Employee;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Mike | 32 | Texas | 2000 |
| 2 | James | 25 | New York | 1500 |
| 3 | Jack | 23 | Edmond | 2000 |
| 4 | Maddie | 25 | Washington| 6500 |
| 5 | Wiston | 27 | Villas | 8500 |
| 6 | Mack | 22 | Swiss | 4500 |
| 7 | Muffy | 24 | Indore | 10000 |
+----+----------+-----+-----------+----------+
7 rows in set (0.00 sec)
SQL> SELECT ID, NAME, SALARY FROM Employee WHERE SALARY > 2000 AND age SELECT ID, NAME, SALARY FROM Employee WHERE SALARY > 6500 AND age > 23;
+----+-------+----------+
| ID | NAME | SALARY |
+----+-------+----------+
| 5 | Wiston| 8500.00 |
| 7 | Muffy | 10000.00 |
+----+-------+----------+
2 rows in set (0.00 sec)
[/c]
Here in the above example specific values can be retrieved by using AND operator.

A string is defined as the succession of characters with a discretionary size specifications. The characters can be special characters, letters, numeric, and accumulation of whole sequence or blanks. There are three sorts of strings. For example,

- Character large objects
- Fixed-length strings
- Variable-length strings

These objects are variable-length strings that range to 128.
While declaring a string, the developer will determine the length. The string is protected with spaces to the length as indicated.
The greatest length of variable-length string reaches to 32,767.

- SQL Operators - It is a symbol that instructs the compiler to execute some particular operations.
- Arithmetic operator - Executes one or more numeric values and returns the result set.
- Comparison operator - Compares one expression with another.
- Relational operator - Relational operators compare two expressions and returns the result set.
- Logical operator - Operates on two operands and returns a boolean output.
- String operator - A sequence of characters with an discretionary size specifications.

© 2013 - 2023 SPLessons.com