Oracle

IN Function in Oracle SQL – PLSQL

October 24, 2012 by techhoneyadmin

The IN function in Oracle SQL / PLSQL is like using multiple OR conditions at once.

Oracle SQL / PLSQL Syntax for IN function in Oracle SQL SELECT Statement is:

SELECT column_name(s)
FROM table_name
WHERE column_name IN(value1, value2 . . . valueN);

Oracle SQL / PLSQL Syntax for IN function in Oracle SQL INSERT Statement is:

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

Oracle SQL / PLSQL Syntax for IN function in Oracle SQL UPDATE Statement is:

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

Oracle SQL / PLSQL Syntax for IN function in Oracle SQL DELETE Statement is:

DELETE FROM table_name
WHERE column_name IN(value1, value2 . . . valueN);

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

Example 1: Using Oracle SQL / PLSQL IN Function in SQL SELECT Statement

Suppose we want to see the records of employee(s) working either in ‘Sales’ or ‘IT’ department.

The same can be achieved as:

SELECT *
FROM employee
WHERE department IN ('Sales','IT');

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
105	Emp E	32000	Sales	10

The example of Oracle SQL IN Function above shows that we have retrieved the records pertaining to employee(s) working either in ‘Sales’ or ‘IT’ department using IN function in WHERE clause

Example 2: Using Oracle SQL / PLSQL IN Function with numbers.

IN function can also be used with numbers.

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 = ‘20000’, or Salary = ‘32000’.

We can achieve the same as using the SQL SELECT Statement with SQL IN Function as:

SELECT *
FROM employee
WHERE salary IN (20000,32000);

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
105	Emp E	32000	Sales	10

By using the IN Function in the example above we have successfully retrieved the records pertaining to employees who having Salary = ‘20000’, or Salary = ‘32000’.

Example 3:

Oracle SQL /IN function can also be used with other conditions.

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 = ‘20000’, or Salary = ‘32000’ and working in ‘Sales’ department.

We can achieve the same using Oracle SQL IN Function in SELECT SQL Statement with WHERE Clause as:

SELECT *
FROM employee
WHERE department = 'Sales'
AND salary IN (20000,32000);

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

Employee_ID	Employee_Name	Salary	Department	Commission
105	Emp E	32000	Sales	10

Above example shows Oracle SQL /PLSQL IN Function successfully to retrieving the records pertaining to employees who having Salary = ‘20000’, or Salary = ‘32000’ and working in ‘Sales’ department using the IN function in combination with WHERE clause.

Combining AND with OR Condition in Oracle SQL – PLSQL

October 24, 2012 by techhoneyadmin

The AND and OR condition in Oracle SQL / PLSQL can be combined in a WHERE clause to get the desired results from a SQL query.
The combination of AND with OR condition can be used with SELECT, INSERT, UPDATE and DELETE statements.
The most important part is to place the “(“ and “)” brackets at correct positions so that the required result can be fetched.

Syntax for the AND with OR condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE (condition1 and condition2)
OR (condition3 and condition4)
.
.
OR (conditionN-1 and conditionN);

INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
column_nameN)
WHERE (condition1 and condition2)
OR (condition3 and condition4)
.
.
OR (conditionN-1 and conditionN);

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE (condition1 and condition2)
OR (condition3 and condition4)
.
.
OR (conditionN-1 and conditionN);

DELETE FROM table_name
WHERE (condition1 and condition2)
OR (condition3 and condition4)
.
.
OR (conditionN-1 and conditionN);

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(s) having Salary more than ‘20000’ and working in the ‘IT’ department or the employee(s) having Salary less than ‘30000’ and working in ‘Sales’ department then we can achieve the same using OR condition as follows:

SELECT *
FROM employee
WHERE (salary > 20000 AND department = 'IT')
OR    (salary < 30000 AND department = 'Sales');

The result of the above query will be:

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

Here we have retrieved the records pertaining to employee having Salary more than ‘20000’ and working in the ‘IT’ department or the employee(s) having Salary less than ‘30000’ and working in ‘Sales’ department using combination of AND with OR condition along with WHERE clause.

Scenario 2:

We can exchange the place for AND / OR 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 > ‘20000’, and working in ‘IT’ department or having Salary < ‘30000’or working in Sales department. We can achieve the same as: [sourcecode language="sql" light="true"] SELECT * FROM employee [/sourcecode][sourcecode language="sql" light="true"] WHERE (salary > 20000 AND department = 'IT') OR (salary < 30000 OR department = 'Sales'); [/sourcecode] Once we have run the above code following will be the result:

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
105	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to employees who are having Salary > ‘20000’, and working in ‘IT’ department or having Salary < ‘30000’or working in 'Sales' department by using AND with OR condition in WHERE clause.

OR Condition in Oracle Oracle SQL – PLSQL

October 23, 2012 by techhoneyadmin

The OR condition in Oracle SQL / PLSQL allows us to filter the records from the result of a query based on 2 or more than 2 conditions.
OR condition can be used with SELECT, INSERT, UPDATE and DELETE statements.

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

SELECT column_name(s)
FROM table_name
WHERE condition1
OR condition2
OR condition3
.
.
OR conditionN;

INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
Column_nameN)
WHERE condition1
OR condition2
OR condition3
.
.
OR conditionN;

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE condition1
OR condition2
OR condition3
.
.
OR conditionN;

DELETE FROM table_name
WHERE condition1
OR condition2
OR condition3
.
.
OR conditionN;

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 more than ‘20000’ or working in the ‘Sales’ department then we can achieve the same using OR condition as follows:

SELECT *
FROM employee
WHERE department = 'Sales'
OR    salary > 20000;

The result of the above query will be:

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

Here we have retrieved the records pertaining to employee having Salary more than ‘20000’ or working in the ‘Sales’ department using OR condition along with WHERE clause.

Scenario 2:

We can use multiple OR 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 less than ‘30000’, or working in ‘Sales’ department or having commission less than ’20’.

We can achieve the same as:

SELECT *
FROM employee
WHERE department = 'Sales'
OR    salary     < 30000
OR    commission < 20;

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

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
105	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to employees who are having Salary less than ‘30000’, or working in ‘Sales’ department or having commission less than ’20’ using OR clause in combination with WHERE clause.

AND Condition in Oracle SQL – PLSQL

October 23, 2012 by techhoneyadmin

The AND condition in Oracle SQL / PLSQL allows us to filter the records from the result of a query based on 2 or more than 2 conditions.
AND condition can be used with SELECT, INSERT, UPDATE and DELETE statements.

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

SELECT column_name(s)
FROM table_name
WHERE condition1
AND condition2
AND condition3
.
.
AND conditionN;

INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
Column_nameN)
WHERE condition1
AND condition2
AND condition3
.
.
AND conditionN;

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE condition1
AND condition2
AND condition3
.
.
AND conditionN;

DELETE FROM table_name
WHERE condition1
AND condition2
AND condition3
.
.
AND conditionN;

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 more than ‘20000’ and is working in the ‘Sales’ department then we can achieve the same using AND condition as follows:

SELECT *
FROM employee
WHERE department = 'Sales'
AND   salary > 20000;

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
105	Emp E	32000	Sales	10

Here we have retrieved the records pertaining to employee having Salary more than ‘20000’ and is working in the ‘Sales’ department using AND condition along with WHERE clause.

Scenario 2:

We can use multiple AND 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 an employee who is having Salary greater than 30000, is working in Sales department and having commission less than 20.

We can achieve the same as:

SELECT *
FROM employee
WHERE department = 'Sales'
AND   salary     > 30000
AND   commission < 20;

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

Employee_ID	Employee_Name	Salary	Department	Commission
105	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to an employee who is having Salary greater than ‘30000’, is working in ‘Sales’ department and having commission less than ’20’ using AND clause in combination with WHERE clause.

WHERE Clause in Oracle SQL – PLSQL

October 23, 2012 by techhoneyadmin

The WHERE clause in SQL / PLSQL allows us to filter the records from the result of a query; WHERE clause can be used with a SELECT, INSERT, UPDATE and DELETE statements.

Syntax for the WHERE clause in SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE conditions;

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

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

DELETE FROM table_name
WHERE conditions;

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 only ‘Sales’ department then we can achieve the same using WHERE clause as follows:

SELECT *
FROM employee
WHERE department = 'Sales';

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
105	Emp E	32000	Sales	10

Here we have retrieved the records pertaining to ‘Sales’ department only using WHERE clause.

Scenario 2:

WHERE clause can also be used with combination of columns
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 from the departments ‘Sales’ and ‘IT’.

We can achieve the same as:

SELECT *
FROM employee
WHERE department = 'Sales'
OR    department ='IT';

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

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
105	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to ‘Sales’ and ‘IT’ department using WHERE clause.