Operators | Arithmetic Operators | Relational operators | Logical Operators | Special Operators | Extended Comparisons| Clause | AND| OR | NOT | BETWEEN | IN | LIKE | IS NULL

OPERATORS 

Arithmetic Operators:

•       Arithmetic Operators are used in number datatype column.

• Addition(+).
• Subtraction (-).
• Division(/).
• Multiplication(*).
• Modulus(%).

• We can also use Arithmetic operator in “WHERE” conditions.

Addition of two columns:

    Example: Select emp_id, empsal, empid+empsal as "sal+id" from emp;

Subtraction:

    Example: Select emp_id, empsal, empsal - 100 as "sal - 100" from emp;

Multiplication:

    Example: Select emp_id, empsal, empsal * 100 as "sal * 100" from emp;

Modulus:

    Example: Select emp_id, empsal, empsal % 100 as "sal % 100" from emp;

 

Relational Operator:

Relational operators are allowed to use only in WHERE clause.

WHERE Clause

The WHERE clause is optional. When specified, it always follows the FROM clause. The WHERE clause filters rows from the FROM clause tables. Omitting the WHERE clause specifies that all rows are used.

Following the WHERE keyword is a logical expression, also known as a predicate.

Generally, a comparison expression compares the contents of a table column to a literal. A comparison expression may also compare two columns to each other.

The = (equals) comparison operator compares two values for equality. Additional comparison operators are:

> -- greater than

< -- less than

>= -- greater than or equal to

<= -- less than or equal to                                    

<> -- not equal to

Examples:

Comparison operator (=):

SELECT * FROM Emp WHERE Empid = 18;

Greater than operator (>):

SELECT * FROM Emp  WHERE Empid > 30;

Less than Operator (<):

SELECT * FROM Emp  WHERE Empid < 30;

Greater than or equal to (>=):

SELECT * FROM Emp  WHERE Empid >= 30;

Less than or equal to (<=):

SELECT * FROM Emp  WHERE Empid <= 30;

Not equals to (<>):

SELECT * FROM Emp  WHERE Empid <> 18;

Logical Operators :

‘AND’
The AND operator combines two logical operands. The operands are comparisons or logical

 expressions.

Example : SELECT * FROM sp WHERE sno='S3' AND qty < 500;

‘OR’

The OR operator display a record either 1^st or 2^nd condition is true ^.

Example : SELECT * FROM sp WHERE sno='S3' OR city = 'London‘;

‘NOT’
The NOT operator inverts the result of a comparison expression or a logical expression.

Example : SELECT * FROM sp WHERE NOT sno = 'S3‘;

Special Operators / Extended Comparisons

BETWEEN Operator

  Example : SELECT * FROM sp WHERE qty BETWEEN 50 and 500;

IN Operator

   Example : SELECT name FROM s WHERE city IN ('Rome','Paris');

LIKE Operator

  Example :  SELECT name FROM s WHERE city  LIKE ‘Par%’;

IS NULL Operator

   Example : SELECT name FROM s WHERE city IS NULL;