Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Operators in Oracle – Part XVI

Posted by Ravi Varma Thumati on October 21, 2009

SQL *Plus having following operators.

  • Arithmetic Operators
  • Comparison Operators
  • Logical Operator

Arithmetic Operator

Arithmetic operators are used to perform calculations based on number values. The arithmetic operators are + (addition), – (subtraction), * (multiplication) and / (division). We can include them in sql command.

Example

SQL> select salary+comm from emp_master;

Salary+comm

—————-

11000

3700

8000

(Null)

4 rows selected.

Example:

SQL> select salary+comm net_sal from emp_master;

NET_Sal

———-

11000

3700

8000

(Null)

4 rows selected.

In above query, it will give output of salary+comm and net_sal is column alias, which is used to change column heading. So the output will be displayed under the net_sal heading. If you calculate any number value with null value, it will always return null value.

In arithmetic operators * and / have equal higher precedence. And + and – have equal lower precedence.

Check the following illustrates the precedence of operators.

SQL> Select 12*(salary+comm) annual_netsal from emp_master;

ANNUAL_

———-

132000

44400

96000

(Null)

4 rows selected.

If the parenthesis is omitted then multiplication will be performed first followed by addition. We can change the order of evaluation by using parenthesis.

Comparison Operators:

Comparison operators are used in condition to compare one expression with other. The comparison operators are =, >, <, >=, <=, ! =, between, like, is null and in operators.

Between Operator:

Between Operators is used to check between two values.

The syntax for the BETWEEN condition is:

SELECT columns
FROM tables
WHERE column1 between value1 and value2;

This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement – select, insert, update, or delete.

Example:

SQL> select * from emp_master where salary between 5000 and 8000;

EMPNO ENAME JOB HIREDATE SALARY DEPTNO COMM
1124 Martin Manager 30-aug-00 7000 20 1000

1 row selected.

The above select statement will display only those rows where salary of employee is between 5000 and 8000.

Example #1 – Numbers

The following is an SQL statement that uses the BETWEEN function:

SELECT * FROM suppliers
WHERE supplier_id between 5000 AND 5010;

This would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL statement:

SELECT * FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;

Example #2 – Dates

You can also use the BETWEEN function with dates.

SELECT * FROM orders
WHERE order_date between to_date (‘2003/01/01’, ‘yyyy/mm/dd’)
AND to_date (‘2003/12/31’, ‘yyyy/mm/dd’);

This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).

It would be equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_date >= to_date(‘2003/01/01’, ‘yyyy/mm/dd’)
AND order_date <= to_date(‘2003/12/31′,’yyyy/mm/dd’);

Example #3 – NOT BETWEEN

The BETWEEN function can also be combined with the NOT operator.

For example,

SELECT * FROM suppliers
WHERE supplier_id not between 5000 and 5500;

This would be equivalent to the following SQL:

SELECT * FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;

In this example, the result set would exclude all supplier_id values between the range of 5000 and 5500 (inclusive).

IN Operator:

The in operator can be used to select rows that match one of the values in a list.

The syntax for the IN function is:

SELECT columns
FROM tables
WHERE column1 in (value1, value2, …. value_n);

This SQL statement will return the records where column1 is value1, value2…, or value_n. The IN function can be used in any valid SQL statement – select, insert, update, or delete.

Example #1

The following is an SQL statement that uses the IN function:

SELECT * FROM suppliers
WHERE supplier_name in ( ‘IBM’, ‘Hewlett Packard’, ‘Microsoft’);

This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

It is equivalent to the following statement:

SELECT * FROM suppliers
WHERE supplier_name = ‘IBM’
OR supplier_name = ‘Hewlett Packard’
OR supplier_name = ‘Microsoft’;

As you can see, using the IN function makes the statement easier to read and more efficient.

Example #2

You can also use the IN function with numeric values.

SELECT * FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);

This SQL statement would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

It is equivalent to the following statement:

SELECT * FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

Example #3 using “NOT IN”

The IN function can also be combined with the NOT operator.

For example,

SELECT * FROM suppliers
WHERE supplier_name not in ( ‘IBM’, ‘Hewlett Packard’, ‘Microsoft’);

This would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

SQL>Select * from emp_master where deptno in(10,30);

EMPNO ENAME JOB HIREDATE SALARY DEPTNO COMM
1122 Allen Manager 1-jan-00 10000 10 1000
1125 Tanmay 16-sep-00 10000 10

2 rows selected.

The above query will retrieve only those rows where deptno is either in 10 or 30.

LIKE Operator:

Like operator is used to search character pattern, we need not know the exact character value. The like operator is used with special character % and _ (underscore).

The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement – select, insert, update, or delete.

The patterns that you can choose from are:

% allows you to match any string of any length (including zero length)

_ allows you to match on a single character

Examples using % wildcard

The first example that we’ll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with ‘Hew’.

SELECT * FROM suppliers WHERE supplier_name like ‘Hew%’;

You can also using the wildcard multiple times within the same string. For example,

SELECT * FROM suppliers WHERE supplier_name like ‘%bob%’;

In this example, we are looking for all suppliers whose name contains the characters ‘bob’.

You could also use the LIKE condition to find suppliers whose name does not start with ‘T’. For example,

SELECT * FROM suppliers WHERE supplier_name not like ‘T%’;

By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with ‘T’.

Examples using _ wildcard

Next, let’s explain how the _ wildcard works. Remember that the _ is looking for only one character.

For example,

SELECT * FROM suppliers WHERE supplier_name like ‘Sm_th’;

This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is ‘Sm’ and the last two characters is ‘th’. For example, it could return suppliers whose name is ‘Smith’, ‘Smyth’, ‘Smath’, ‘Smeth’, etc.

Here is another example,

SELECT * FROM suppliers WHERE account_number like ‘12317_’;

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

123170
123171
123172
123173
123174
123175
123176
123177
123178
123179.

Examples using Escape Characters

Next, in Oracle, let’s say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.

Please note that you can define an escape character as a single character (length of 1) ONLY.

For example,

SELECT * FROM suppliers WHERE supplier_name LIKE ‘!%’ escape ‘!’;

This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

Here is another more complicated example:

SELECT * FROM suppliers WHERE supplier_name LIKE ‘H%!%’ escape ‘!’;

This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as ‘Hello%’.

You can also use the Escape character with the _ character. For example,

SELECT * FROM suppliers WHERE supplier_name LIKE ‘H%!_’ escape ‘!’;

This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as ‘Hello_’.

SQL> select * from emp_master where job like ‘M%’;

EMPNO ENAME JOB HIREDATE SALARY DEPTNO COMM
1122 Allen Manager 1-jan-oo 10000 10 1000
1124 Martin Manager 30-aug-00 7000 20 1000

2 rows selected.

The above select statement will display only those rows where job is starts with ‘M’ followed by any number of any characters. % sign is used to refer number of characters (it similar to * asterisk wildcard

in DOS), while _ (underscore) is used to refer single character(it similar to ? question wildcard in DOS).

SQL>Select * from emp_master where job like ‘_lerk’;

EMPNO ENAME JOB HIREDATE SALARY DEPTNO COMM
1123 King Clerk 30-jun-00 3400 20 300

1 row selected.

In above query, it will display only those rows where job is start with any single character but ends with ‘lerk’.

Logical Operators:

Logical operators are used to combine the results of two conditions to produce a single result. The logical operators are AND, NOT and OR.

AND Operator:

The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement – select, insert, update, or delete.

The syntax for the AND condition is:

SELECT columns FROM tables
WHERE column1 = ‘value1’ and column2 = ‘value2’;

The AND condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal ‘value1’ and column2 has to equal ‘value2’.

The Oracle engine will process all rows in a table and display the result only when all the conditions specified using the AND operator are satisfied.

SQL> select * from emp_master where salary > 5000 and comm < 750 ;

No rows selected.

The select statement will return only those rows where salary is greater than 5000 and comm is less than 750. If both the conditions are true then only it will retrieve rows.

OR Operator:

The OR condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement – select, insert, update, or delete.

The syntax for the OR condition is:

SELECT columns FROM tables WHERE column1 = ‘value1’ or column2 = ‘value2’;

The OR condition requires that any of the conditions be must be met for the record to be included in the result set. In this case, column1 has to equal ‘value1’ OR column2 has to equal ‘value2’.

The Oracle engine will process all rows in a table and display the result only when any of the conditions specified using the OR operators are satisfied.

SQL>select * from emp_master where salary > 5000 or comm < 750;

EMPNO ENAME JOB HIREDATE SALARY DEPTNO COMM
1122 Allen Manager 1-jan-00 10000 10 1000
1123 King Clerk 30-jun-00 3400 20 300
1124 Martin Manager 20-aug-00 7000 10 1000
1125 Tanmay 16-sep-00 10000 10

4 rows selected.

This select statement will check either salary is greater than 5000 or comm is less than 750. ie it will return all the records either of any one condition returns true.

NOT Operator:

The Oracle engine will process all rows in a table and display the result only when none of the conditions specified using the NOT operator are satisfied.

SQL> select * from emp_master where not salary = 10000;

EMPNO ENAME JOB HIREDATE SALARY DEPTNO COMM
1123 King Clerk 30-jun-00 3400 20 300
1124 Martin Manager 30-aug-00 7000 20 1000

2 rows selected.

This select statement will return all the records where salary is NOT equal to 10000.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: