Oracle SQL in 10 Minutes - Asim Abbasi

Chapter 2: SQL Operators

Page #: 1 | 2 | 3

The next category of SQL operator is called Arithmetic operators and used if you want to perform some arithmetic operations. The thing to remember over her is that although there is some thing “order of precedence” but its always recommended to use parenthesis whenever your statement involves two or more arithmetic operators. Parenthesis over rides the “order of precedence”. Now the question is, what is order of precedence? It means if you are not using any parenthesis and you calculation involves two or more arithmetic operators so which operations should Oracle perform first.

According to “order of precedence” rule, Multiplication and Division will be preferred over Subtraction and Addition. Moving from left to right, if Multiplication comes first than it will be handled first otherwise division, it applies to subtraction and addition too.

Example:

SELECT ename, sal, sal + sal*5/100 “Next Year Sal”
FROM emp;

Logical Operators and the operators in the Other category can be best understood by looking at their respective real world examples.

Example:

SELECT sal
FROM emp
WHERE deptno = 30 AND sal > 2000;

The output of the query will be only one column i.e. sal and only those records will be displayed where department number is 30 and the salary is greater than 2000. So when you use AND operator it means both conditions needs to satisfy for the record to appear in the output but in case of OR, either first condition needs to be true or the second one e.g.

SELECT sal
FROM emp
WHERE deptno = 30 OR sal > 2000;

Example:

SELECT *
FROM emp
WHERE job IN ('CLERK','ANALYST');

The output of the query will be all the coulums of emp table but only those records where job column contains either “CLERK” or “ANALYST”. You can also use IN operator with as follows.

SELECT *
FROM emp
WHERE sal IN (SELECT sal
FROM emp
WHERE deptno = 30);

By having NOT before IN can complete invert the results like in the following example. Such types of queries fall under the category called “Sub-Queries” which we will discuss in the article ahead in this chapter. There is a special technique to interpret them.

SELECT *
FROM emp
WHERE sal NOT IN (SELECT sal
FROM emp
WHERE deptno = 30);

Example:

SELECT *
FROM emp
WHERE sal BETWEEN 2000 AND 3000;

Only those records will be displayed where the salary is between 2000 and 3000 including both 2000 and 3000.

Example:

SELECT ename, deptno
FROM dept
WHERE EXISTS (SELECT *
FROM emp
WHERE dept.deptno = emp.deptno);
TRUE if a sub-query returns at least one row. In other words the output will be two columns from dept table, all the records only if the query after EXISTS results in at least one record.

Example:

SELECT sal
FROM emp
WHERE ename LIKE 'SM%';

The output will be only those salaries from emp (employee) table where ename (employee name) begins with “SM”. Another variation of above query is as follows.

ename LIKE 'SMITH_'

The output will be only those records where ename begins with “SMITH” and there should not be more than one character after it.

Example:

SELECT ename, deptno
FROM emp
WHERE comm IS NULL;

The output will be ename and deptno but only those records where comm field has NULL value. NULL is a special value and just keep in mind that its not Zero. It can be visualized as empty field occupying zero byte.

Chapter 1: SQL Operators

Page #: 1 | 2 | 3

Share with others:

products

books

FREE VB, ASP Projects

Oracle SQL in 10 Minutes - Asim Abbasi