SQL Operators

SELECT statement is used to fetch records from database.

Syntax:

       SELECT  [ COLUMNS_NAME1,COLUMNS_NAME2 ] FROM TABLE;

In SQL any data is enclosed in single quote becomes case sensitive like CHAR and DATE Datatypes.

Sorting:

To Arrange the data in ascending and Descending Order we make of Sorting. It can be done

by clause called 'ORDER BY '. Ascending as ASC, Descending as DESC. By defaults it sorts in ascending Order. Order By clause is used as the last statement in the query.

Ex: select sal,job from emp order by sal;

      select sal,job from emp order by 1,2;  / Can sort Columns with Numbers /

      select sal,job from emp order by 2,1 desc;

Alias :

Alias is done for columns and tables. Its done to make query a more readable.

If space is provided in alias , then an error (ora-00923: From keyword not found where expected) is thrown.

Ex: select ename,empno,job, hiredate  Join_Date from emp a;

    select ename,empno,job, hiredate "Join Date" from emp b;

Disctinct

Distinct is used to return the dictinct values from the table.

It is used before the FROM Clause in SELECT statement.

Ex: Select distinct job from emp;

      Select distinct job, deptno from emp;

NOTE:

-      When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression.

-      When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.

Where Clause

Where clause is used to filter the plain records from the table.

It’s used after the From Clause in SELECT statement.

Ex: select * from emp where deptno=20;

Note: '*' used to select all table columns.

SQL Operators

A operator is a reserve word or a character used in SQL Statement where clause to perform operations like arithmetic operations, comparisions, logical operation on data.

There are four operators

- Arithmetic Operators

- Relational Operators

- Logical Operators

- Special Operators

Arithmetic Operators:

Arithmetic Operators	Description
+	Addition - Adds values on either side of the operator
-	Subtraction - Subtracts right hand operand from left hand operand
*	Multiplication - Multiplies values on either side of the operator
/	Division - Divides left hand operand by right hand operand
%	Modulus - Divides left hand operand by right hand operand and returns remainder

Relational Operators:

Relational Operators	Symbolize	Description
=	equal to	Checks if the values of two operands are equal or not, if yes then condition becomes true.
<>, !=	is not equal to	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
<	less than	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.
>	greater than	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
>=	greater than or equal to	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
<=	less than or equal to	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
!<	Not Less Than	Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.
!>	Not Greater Than	Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

Logical Operators:

Relational Operators	Description
AND	The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause
OR	The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
NOT	The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

Special Operators:

Relational Operators	Description
IN	Is used to compare the List of Values
LIKE	Used for pattern matching, supports two wild card character like ‘%’ and ‘_’
BETWEEN	Used for comparing the range of values
IS NULL	Used to return null records
EXISTS	Used to search the specified row in a table that meets specified criteria.
NOT EXISTS	Used to search the specified row in a table that does not meets specified criteria.
ANY	Used to compare a value which is applicable in the list
ALL	Used to compare value to all values in a set.