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.

Tuesday 19 May 2015

Operators in SQL

Comparison Condition Operators:


      These operators are used to compare values. The table below shows the comparison conditions operators used in SQL.


Comparison Condition Operators
Description
=
Equal to
<> 
Not equal to
> 
Greater than
>=
Greater than or equal to
< 
Less than
<=
Less than or equal to
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


Check out a simple example query for Comparison Operators.


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

Output of above query:




Logical Operators:

    These opeators are used for logical comparison of values. The table below shows the logical operators used in SQL.


Logical Operators
Description
AND
Returns true if all the conditions are true
OR
Returns true if either of the conditions are true
NOT
Returns true if condition is false or vice versa

Arithmetic Operators:


Arithmetic Operators
Description
+
Addition
-
Subtraction
*
Multiplication
/
Division
%
Modulus

Rules of Precedence:


Evaluation Order of Operators
Operator Name
1
Arithmetic
2
Concatenation { || }
3
Comparison
4
Special Comparison Condition
5
Logical Condition


In the next post I will illustrate all the operators once again with example queries. Stayed tuned to my blog by hitting the facebook LIKE button or subscribe free to blog. 

Saturday 16 May 2015

Data Retrieval and Restriction

The SELECT command of SQL is used to retrieve data from the tables of the database and display those data in a table format.


The general form of SELECT statement is as shown below:

       
SELECT department_id, employee_id, first_name

FROM employees

WHERE department_id=60;




WHERE clause usage:

    The WHERE clause is used to restrict the rows returned by SELECT statement. It helps to filter the data as per your requirement.

    The general form of select statement that limits the retrieval of data by using WHERE clause is as shown below:

SELECT  *||([DISTINCT] column||expression [alias],...)

FROM tableName

WHERE condition();


   The WHERE clause is placed right after FROM clause as you can see in the above general form.

 Consider the below SQL query used to retrieve data from EMPLOYEE table:


SELECT department_id, employee_id, first_name

FROM employees;

  The output of the above query is as shown below:



Now let us use WHERE clause to filter the data as per our requirement. Suppose my requirement is to get data only for department_id=60, in that case I can modify above SQL query as shown below:

SELECT department_id, employee_id, first_name

FROM employees

WHERE department_id=60;

The output of the above query is as shown below:



You can see in the above query result, only data for department_id=60 is present.