Pages

Wednesday 20 May 2015

SQL Operators Revisited with examples

Comparison Condition Operators:


Comparison Condition Operators
Description
=
Equal to
<> 
Not equal to
> 
Greater than
>=
Greater than or equal to
< 
Less than
<=
Less than or equal to


SELECT first_name, last_name, salary
FROM employees
WHERE salary>=5000;


Result of the above query is as shown below:



Special Condition Comparison Operators:


Special Comparison Condition Operators
Description
BETWEEN…AND…
Inclusive between two values
IN(set)
Match any of the values in set
LIKE
Match a character pattern
IS NULL
Check NULL value true or false

BETWEEN…AND… operator:

This operator selects all the values mention within (BETWEEN… AND… )range. Both starting and ending values are inclusive while selection.
Let’s see a simply example using this operator:


SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 12000;

Result of the above query is as shown below:



IN(set) operator:

This operator filters data with all the values present in the set of IN operator.


SELECT department_id, first_name, salary
FROM employees
WHERE salary IN (10,20,60,70,90);


Result of the above query is as shown below:



LIKE operator:

This operator matches character patterns. The patterns may contain literal characters or numbers.
Below two special characters can be used with search pattern:

%
Denotes zero or many character
_(underscore)
Denotes one character

Below is the example query which displays results of employees whose first name starts with letter A.

SELECT department_id, first_name, salary
FROM employees
WHERE first_name LIKE ‘A%’;

Result of the above query is as shown below:



Let’s see an example combing both % and _ in filter condition. The below query will display the results of only those employees whose second letter in their first name will be ‘e’.


SELECT department_id, first_name, salary
FROM employees
WHERE first_name LIKE '_e%';

Result of the above query is as shown below:




IS NULL operator:

It displays all the records whose value is NULL.



//next post will be on LOGICAL operators, combing all the operators together to get more desired result and ORDER by clause. Stayed Tuned by LIKING blog’s fan page on FB or simply by subscribing.