SQL - SPLessons

SQL Operators

Chapter 21

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Operators

SQL Operators

shape Description

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

shape Conceptual
figure

Arithmetic operator

shape Description

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

shape Examples

The following is an example for arithmetic operators.

 sql> select (20 + 4) as addition; 
24

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 operator

shape Description

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.

shape Examples

The below example describe the execution of Between operator.

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)

By viewing the above example, the concept of Between operator will be understood.

Relational operator

shape Description

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

shape Examples

By viewing the below example, the concept of Where Clause can be easily understood.

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)

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

shape Description

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.

shape Examples

By viewing the below example the concept of SQL AND operator an be easily understood.

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)

Here in the above example specific values can be retrieved by using AND operator.

String operator

shape Description

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, 

shape Conceptual
figure

Character large objects

These objects are variable-length strings that range to 128.

Fixed-length strings

While declaring a string, the developer will determine the length. The string is protected with spaces to the length as indicated.

Variable-length strings

The greatest length of variable-length string reaches to 32,767.

Summary

shape Key Points

  • 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.