SQL Operators

SQL Operators:-

Operators and conditions are used to perform operations such as addition  subtraction or comparison on the data items in as SQL statement

Different types of SQL Operators are:-

·        Arithmetic Operators

·        Comparison Operators

·        Logical Operators

·        Set Operators

·        Operator Precedence

1) Arithmetic Operators

  Arithmetic operators are used in SQL expressions to add, subtract, multiply, divide and negate data values, The result of this expression is a number value.

Unary operators (B)
+,-	Denotes a positive or negative expression
Binary operator (B)
*	Multiplication
/	Division
+	Addition
-	Subtraction

Example:-

   Update Emp_Salary

   Set salary=salary*1.05;

2) Comparison Operators

  These are used to compare one expression with another. The comparison operators are given below:

Operator	Definition
=	Equality
!=,<>	Inequality
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
IN	Equal to any member of set
NOT IN	Not equal to any member of set
Is NULL	Test for nulls
Is NOT NULL	Test for anything other than nulls
LIKE	Returns true when the first expression matches the pattern of the second expression
ALL	Compares a value to every value in a list
ANY, SOME	Compares a value each value in a list
EXISTS	True if subquery returns at least one row
BETWEEN x and y	>= x and <=y

Example:-

  1) Get the name of students who have secured first class

      => select student_names

          from studen_tb

          Where percentage >= 60 and percentage < 67

2) Get the out of state student name

   => select student_names

         from student_names

         where state<> ‘Maharashtra’;

3) Get the names of students living in ‘Pune’.

   => select student_names

         from student

         where city=’Pune’;

4) Display the names of students with no contact phone number

   => select student_names

         from student

         where ph_no is null;

5) Display the names of students who have secured second class in exam

   => select student_names

        from student

        where perecentage BETWEEN 50 AND 55;

3) Logical Operators

  A logical operator is used to produce a single result from combining the two separate conditions. Following figure shows logical operators and their definitions.

Operator	Definition
AND	Returns true if both component conditions are true; otherwise return false.
OR	Returns true if either component conditions is true; otherwise return false.
NOT	Returns true if condition is false; otherwise returns false.

Example:-

  1) Display the names of students living in Pune and Bombay

   => select student_names

         from student

         where city=’Pune’ or city=’Bombay’;

  2) Display the names of students who have secured higher second class in exam.

   => select student_names

         from student

         where percentage >= 55 and percentage < 60

4) Set Operators

  Set operators combine the results of two separate queries into a simple result.

Operator	Definition
UNION	Returns all distinct rows from both queries
INTERSECT	Returns common row selected by both queries
MINUS	Returns all distinct rows that are in the first query, but not in second one

Example:-

  Consider following two relations-

  Permanent_Emp {Emp_Code,Name,Salary}

  Temporary_emp={Emp_Code,Name,Daily_Wages}

 1) Display name of all employees.

  => select Name

       from Permanent_Emp

       UNION

       select Name

       from Temporary_Emp;

6) Operator Precedence

  Precedence defines the order that the DBMS uses when evaluating the different operators in the same expression. The DBMS evaluates operators with the highest precedence first before evaluating the operators of lower precedence. Operators of equal precedence are evaluated from the left to right

Operator	Definition
:	Prefix for host variable
,	Variable separator
()	Surrounds subqueries
“	Surrounds a literal
“  “	Surrounds a table or column alias or literal text
( )	Overrides the normal operator precedence
+, -	Unary operators
*, /	Multiplication and division
+ , -	Addition and subtraction
||	Character concatenation
NOT	Reverses the result of an expression
AND	True if both conditions are true
OR	True if either conditions are true
UNION	Returns all data from both queries
INTERSECT	Returns only rows that match both queries
MINUS	Returns only row that do not match both queries