Datatype and Operators

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