Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

Combining AND with OR Condition in Oracle SQL – PLSQL

by

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);

Or

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);

Or

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);

Or

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

by

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;

Or

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

Or

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

Or

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

by

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;

Or

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

Or

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

Or

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

by

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;

Or

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

Or

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

Or

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.

DISTINCT Clause in Oracle SQL – PLSQL

by

The DISTINCT clause in SQL / PLSQL allows us to remove duplicate records from the result of a query. It can be used only with a SELECT statement.

Syntax for the DISTINCT clause in PLSQL is:

SELECT DISTINCT column_name(s)
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:

Here we can see that ‘Sales’ and ‘IT’ departments occurs twice in the department column of ‘employee’ table.
If we wish to view only unique departments from the employee table we can achieve the same by using the DISTINCT clause as:

SELECT DISTINCT department
FROM employee;

The result of the above query will be:

Department
Sales
IT
Support

Here we have retrieved only unique department names from the ‘employee’ table and eliminated the duplicates using DISTINCT clause.

Scenario 2:

DISTINCT 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

Here we can see that we have 2 occurrences of ‘Sales’ department and the corresponding commission for them is 10.
If we want to get all the unique departments along with their commissions we can achieve the same as:

SELECT DISTINCT department
                ,commission
FROM employee;

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

Department Commission
Sales 10
IT 20
Support

Here we have successfully retrieved the unique combination of columns using the DISTINCT clause.