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 ( ' ' ).

OperatorDescription
BETWEEN....AND..... It is used between the two values
IN (list) It is used to match any list of a value
LIKE It is used to match any list of a value
IS NULL It is used for the null value

 

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;
employee_namesalary
Karan20000
Sam23000
Varun24000

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);
employee_nameemployee_idsalary
Karan2320000
Varun2724000

 

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 

employee_noemployee_namejobemployee_idsalaryallowance
101VarunCEO2724000 
102Sammanager25230005000
103Karanclerk2320000 
104kavitaclerk29200001000

 

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;
employee_nametotal_salary
Varun 
Sam28000
Karan 
kavita21000

 

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;
employee_nametotal_salary
Varun24000
Sam28000
Karan20000
kavita21000

 

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
NameEmp_id
Varun27
Sam25
Karan23
kavita29

 

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;
employee_nameAnnual_salary
Varun288000
Sam276000
Karan240000
kavita240000

 

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

employee_noemployee_namejobemployee_id
101VarunCEO27
102Sammanager25
103Karanclerk23
104kavitaclerk29

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

SELECT * FROM Employees 
WHERE employee_name LIKE'S%'
employee_noemployee_namejobemployee_id
102Sammanager25

 

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

SELECT * FROM Employees 
WHERE employee_name LIKE'n'
employee_noemployee_namejobemployee_id
101VarunCEO27
103Karanclerk23

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%'
employee_noemployee_namejobemployee_id
101VarunCEO27
102Sammanager25
103Karanclerk23
104kavitaclerk29

 

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 SELECTstatement.
In ORDER BY clause ascending order is by default work.

Example :- Table Employees

employee_noemployee_namejobemployee_id
101VarunCEO27
102Sammanager25
103Karanclerk23
104kavitaclerk29

 

 In the given table display the employee_name in alphabetical order.

SELECT*FROM Employees 
ORDER BY employee_name;
employee_noemployee_namejobemployee_id
103Karanclerk23
104kavitaclerk29
102Sammanager25
101VarunCEO27

 

 In the given table display the employee_no in REVERSE order.

SELECT*FROM Employees 
ORDER BY employee_no;
employee_noemployee_namejobemployee_id
104kavitaclerk29
103Karanclerk23
102Sammanager25
101VarunCEO27

 

Using DISTINCT in SQL

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

Example :- Table Employees

SELECT job FROM Employees;
Job
CEO
manager
clerk
clerk

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

SELECT DISTINCT job FROM Employees;
Job
CEO
manager
clerk

 

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 

jobemployee_id
CEO27
manager25
clerk23
clerk29


Keywords: