SQL Operators

There are four types of SQL (structure query language) operator which is used to operate with all the data types. In the SQL if the attributes or data are used in the list they must be embedded in a single quote ( ' ' ).

The BETWEEN Operator

It is used to find the value between the comprehensive within the range of high and low.

Suppose the employee whose pay is between 20,000 to 25,000.

SELECT employee_name, salary FROM Employees WHERE salary BETWEEN 20000 AND 25000;

In this table the value of salary is described in low to high range.

The IN Operator

The IN operator is used to find the value in a given list.

To find all employees who have employee_id  23 and 27.

SELECT employee_name,employee_id,salary FROM Employees WHERE employee_id(23,27);

In Null Values

A null value is those values which are absent, unavailable, missing, or inapplicable. A null value means blank in the field, zero is a number so it is not included in a null value. In the table when there is a lack or absence of a data value for a specific column, that value is said to be a NULL.

In the table when any column value declares a null then the result is also null.

Example :-  Employee table showing  a null value 

To find all the employee who has no allowance,you are testing for a NULL.

SELECT employee_name,salary+allowance as total_salary FROM Employees;

In the table when any column value is null the result is also null and to find out all the results of all the employees we are going to convert the Null value into a number by using the NVL function. The  NVL function is used to convert the Null value from the earlier statement to zero.

SELECT employee_name,salary+NVL(allowance,0) as total_salary FROM Employees;

Changing of Column Headings

In the SQL we can rename the attributes as well as relations. We can change the name of the column heading by using a column alias. The column also called after the column in SELECT by using as a separator.

Example :- 

SELECT employee_name AS Name,employee_id Emp_id FROM Employees

If alias contains spaces or any other special character such as #  $  & and * then it should be enclosed with the double quotes " "

Example :- 

SELECT employee_name AS "Name", salary*2 "Annual_salary" FROM Employees;

Tuple Variables

As we know that section or clause is very convenient for describe a tuple variables. A tuple variables is linked with a specific relation. We can define tuple variables in FROM clause via the use of AS clause.

SELECT e.employee_no,e.employee_id  d.department_no,d.location FROM Employees as e, Department as d WHERE e.department_no = d.department_no;

String Operations

In the SQL 'Like' is one of the most important string Operators. The LIKE condition is used to define a search for an arrangement in the column.

A "%" sign is used to define a wild cards both before and after the arrangement in the column.

SELECT column FROM table
WHERE column LIKE pattern

Example :-  Table Employees

In the given SQL statement display the employee_name  start with 'S' 

SELECT * FROM Employees 
WHERE employee_name LIKE'S%'

Example :-  In the given SQL statement display the employee_name  end character with 'n'

SELECT * FROM Employees 
WHERE employee_name LIKE'n'

In the SQL the % and _ symbols can be used to combine the literal characters.

Example :-  In the given SQL statement display the employee_name  second character with 'a'

SELECT * FROM Employees 
WHERE employee_name LIKE'_a%'

Order by Clause

By using ORDER BY clause we, can arrange the rows in ascending and descending order.
ORDER BY clause is used to arrange the rows in proper format.
In SQL  ORDER BY clause come to the end of the SELECT statement.
In ORDER BY clause ascending order is by default work.

Example :-  Table Employees

 In the given table display the employee_name in alphabetical order.

SELECT*FROM Employees 
ORDER BY employee_name; 

 In the given table display the employee_no in REVERSE order.

SELECT*FROM Employees 
ORDER BY employee_no; 

Using DISTINCT in SQL

SQL server displays the query result without removing the duplicate entrance by default.

Example :-  Table Employees

SELECT job FROM Employees;

We can use the DISTINCT to remove the duplicate values from the table.

SELECT DISTINCT job FROM Employees;

In SQL we point out the multiple column after the DISTINCT attribute and DISTINCT effect all the selected columns from the table.

Example :- To display distinct job of employee_id