condition

IS NOT NULL in Oracle SQL PLSQL

November 22, 2012 by techhoneyadmin

In Oracle SQL / PLSQL IS NOT NULL is used to check whether the value of a literal IS NOT NULL or not.

Example 1:
Syntax to use IS NOT NULL in IF statement is:

IF literal_name IS NOT NULL THEN
<business_logic>
END IF;

Here if the literal_name does not have NULL value then the IF condition will evaluate to TRUE, then the “THEN” section of the code will get executed.

Example 2:

Using IS NOT NULL is Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Suppose we want to see the records having commission as NOT NULL, then we can achieve the same as:

SELECT *
FROM employee
WHERE commission IS NOT NULL;

We will get the following results:

Employee_ID	Employee_Name	Salary	Department	Commission
102	Emp B	IT	20000	20
103	Emp C	IT	28000	20
104	Emp D	Support	30000	5
105	Emp E	Sales	32000	10
106	Emp F	Sales	20000	5

Here we can see that we have successfully retrieved records having NOT NULL commission using IS NOT NULL in Oracle SQL PLSQL SELECT statement.

IS NULL in Oracle SQL PLSQL

November 22, 2012 by techhoneyadmin

In Oracle SQL / PLSQL IS NULL is used to check whether the value of a literal is NULL or not.

Example 1:
Syntax to use IS NULL in IF statement is:
IF literal_name IS NULL THEN
<business_logic>
END IF;

Here if the literal_name has NULL value then the IF condition will evaluate to TRUE, then the “THEN” section of the code will get executed.

Example 2:

Using IS NULL is Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Suppose we want to see the records having commission as NULL, then we can achieve the same as:

SELECT *
FROM employee
WHERE commission IS NULL;

We will get the following results:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
107	Emp G	12000	Sales
108	Emp H	12000	Support

Here we can see that we have successfully retrieved records having NULL commission using IS NULL in Oracle SQL PLSQL SELECT statement.

LIKE Condition in Oracle SQL -PLSQL

October 24, 2012 by techhoneyadmin

The LIKE condition in Oracle SQL / PLSQL is used in WHERE clause to place wildcard characters while fetching records.
LIKE condition can be used with SELECT, INSERT, UPDATE and DELETE in SQL statements.

LIKE condition have 2 flavors:
1. % – allows us to match string of any length including zero length.
2. _ allows us to match only a single character.

Syntax for the LIKE condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE condition;

INSERT INTO table_name
VALUES(column_name1
,column_name2
,column_name3
.
.
column_nameN)
WHERE column_name LIKE condition;

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE column_name LIKE condition;

DELETE FROM table_name
WHERE column_name LIKE condition;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Using ‘%’ in LIKE condition
Suppose we want to see the records of employee(s) who work in departments that starts with ‘S’.

The above can be achieved as:

SELECT *
FROM employee
WHERE department LIKE 'S%';

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we have retrieved the records pertaining to employee(s) who work in departments starting with ‘S’ using LIKE condition in WHERE clause.

Note that the % sign after ‘S’ in query acts as a wildcard character for String of any length.

Scenario 2:

Using ‘_’ in LIKE condition
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
114	Emp E	32000	Sales	10

Suppose we want to see the records of employee(s) whose ‘employee_id’ is of 3 characters which starts with ‘1’ and ends with ‘4’

We can achieve the same as:

SELECT *
FROM employee
WHERE employee_id LIKE '1_4';

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
104	Emp D	30000	Support
114	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to employee whose ‘employee_id’ has 3 characters that starts with ‘1’ and ends with ‘4’ using LIKE condition in WHERE clause.

EXISTS Condition in Oracle SQL – PLSQL

October 24, 2012 by techhoneyadmin

The EXISTS condition in Oracle SQL / PLSQL will return any records whenever the “exists” condition is met.
EXISTS condition can be used with SELECT, INSERT, UPDATE and DELETE SQL statements.

Syntax for the EXISTS condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

INSERT INTO table_name
VALUES(column_name1
,column_name2
,column_name3
.
.
column_nameN)
WHERE EXISTS (subquery);

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE EXISTS (subquery);

DELETE FROM table_name
WHERE EXISTS (subquery);

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

And suppose we also have a table named “comm” as shown below:

Emp_ID	Commission_Percent
101	10
102	20
103	20

Scenario 1:

Suppose we want to see the records of employee(s) from ‘employee’ table that also have records in ‘comm’ table.

The above can be achieved as:

SELECT *
FROM employee
WHERE EXISTS(SELECT *
             FROM comm
             WHERE employee.employee_id = comm.emp_id
             );

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20

Here we have retrieved the records pertaining to employee(s) from ‘employee’ table that also have records in ‘comm’ table using EXISTS condition along with WHERE clause.

Scenario 2:

EXISTS condition can also be used with NOT operator.
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

And suppose we also have a table named “comm” as shown below:

Emp_ID	Commission_Percent
101	10
102	20
103	20

Suppose we want to see the records of employee(s) from ‘employee’ table that do not have records in ‘comm’ table.
We can achieve the same as:

SELECT *
FROM employee
WHERE NOT EXISTS(SELECT *
                 FROM comm
                 WHERE employee.employee_id = comm.emp_id
                 );

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to employee(s) from ‘employee’ table that do not have records in ‘comm’ table using EXISTS condition with NOT operator in combination with WHERE clause.

BETWEEN Condition in Oracle SQL – PLSQL

October 24, 2012 by techhoneyadmin

The BETWEEN condition in Oracle SQL / PLSQL allows us to filter the records within a range of values including values provided for the range.
BETWEEN condition can be used with SELECT, INSERT, UPDATE and DELETE SQL statements.

Syntax for the BETWEEN condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
column_nameN)
WHERE column_name BETWEEN value1 AND value2;

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE column_name BETWEEN value1 AND value2;

DELETE FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Suppose we want to see the records of employee having Salary >= ‘20000’ and Salary <= '31000'. [sourcecode language="sql"] SELECT * FROM employee WHERE salary BETWEEN 20000 AND 31000; [/sourcecode] The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support

Here we have retrieved the records pertaining to employee having Salary more than ‘20000’ and less than ‘31000’ including the values provided in the range using BETWEEN condition along with WHERE clause.

Scenario 2:

We can use multiple BETWEEN conditions in a query.
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Suppose we want to view all the records of employees who are having Salary more than ‘20000’ and less than ‘31000’ and having commission more than ’15’ and less than ’25’ including values provided in range.

We can achieve the same as:

SELECT *
FROM employee
WHERE salary     BETWEEN 20000 AND 31000
AND   commission BETWEEN 15    AND 25;

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20

Here we have successfully retrieved the records pertaining to employees who are having Salary >= ‘20000’ and Salary <= '31000' and having commission more than '15' and less than '25' using BETWEEN condition in combination with WHERE clause.