Oracle has some interesting data types and operators. I don't use all of them while writing SQL queries but nevertheless they are helpful in database programming. Here are some of them:

**Number(p,s)**'Number' is used for any numeric datatype. The

*s* is for precision and

*s* is for scale.

*p* has a range from 1 to 38, while

*s* can take values from -84 to 127. For example Number(4,2) can take values from -99.99 to 99.99. At first this datatype looks simple but it gets interesting when some different combinations of

*p* and

*s* are used. Here are some of them:

If

*s* is greater than

*p*, then the precision defines the maximum number of digits to the right of the decimal point after (s-p) zeros. For examples, Number(3,5) can take values from -0.00999 to 0.00999. Any number out of this range will generate error, e.g. 0.999 or 0.01 will result in raising an exception.

If

*s *is negative then s zeros are added to the left of the decimal and the number is rounded. So if the datatype is Number(4,-2) and the value is 12355.5 then it will be changed to 12300. However 123555.5 will raise an error becuse the value should not have more than (s+p) digitgs.

**Operators**

I have used many operators in SQL queries but some of them that I don't use often are as follows:

ANY (or SOME) is used to compare a value to each value in a list or subquery. It must always be preceded by a comparision operator:

SELECT empno, empname FROM emp

WHERE deptno <=ANY(10,20,25) or, SELECT empname, salary FROM emp WHERE salary >=ANY( SELECT salary FROM emp

WHERE deptn0=10);

ALL operator is used to compare a value to every value in a list or subquery. It must be preceded by a comparision operator.

SELECT empno FROM emp

WHERE salary > ALL(SELECT salary FROM emp

WHERE deptno=10);

NOTE: '=ANY' is equivalent to IN and '!=ALL' is equivalent to NOT IN

EXISTS is also an interesting operator that I don't remember using in my academic or professional life but it can be very helpful under some circumstances. EXISTS is always followed by a subquery and is evaluated to TRUE if the subquery returns atleast one row.

SELECT e.empno, e.ename FROM emp e

WHERE EXISTS ( SELECT 1 FROM dept d

WHERE d.deptno= e.deptno

AND d.dname='Administration');

I hope you found this helpful. I'll keep adding useful things as I come across during my study.

- Raza

~~ ~~