Single-row functions in ORACLE

Oracle 9i has many old and new single-row functions that are very useful. Some of them that I find very interesting are:



NVL(x1, x2)
NULLs are of great importance and I am pretty sure that I will get atleast one question on NULLs in the test. One of the ways that NULL values are handled in ORACLE is by using the function NVL(x1,x2). This function takes 2 values x1 and x2 and return x2 if x1 is NULL else it returns x1. Eg., the following query will return the employee name and salary+bonus for each employee:

SELECT ename, salary + NVL(bonus,0) FROM emp

NVL2(x1,x2,x3)
NVL2 is also used to handle NULL values. It takes 3 values (x1,x2,x3) and return x3 if x1 is NULL and x2 if x1 is not NULL. So we can rewrite the above query as follows:

SELECT ename, NVL2(bonus, salary+bonus, salary) from EMP

DECODE
Decode is used to incorporate IF.. THEN.. ELSE functionality in Oracle. Eg., the following query will display the employee name and the department name:

SELECT ename,
DECODE(deptno
, 10,'Accounting'
,20,'Administration'
,'Other')
FROM emp

- Raza

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

Oracle 9i Certification

I started using Oracle almost 8 years ago and I was hooked. I really liked the field of databases and to this day I like to write SQL qeries more than anything else. I have been using Oracle every now and then but I never got the chance to explore it in depth. So in order to increase my knowledge I made the decision to pursue Oracle 9i certification. I have a long way to go but the first step is to prepare for the 1Z0-007 Exam. I have the book and other material, so all I need to do is.... STUDY. I am hoping to take this test before January 18 when my Spring '08 semester starts .

Inaugural Post

I have been thinking about blogging for sometime now. There is a lot that I have read, studied and practiced that I would love to share with everyone. So I will try to post anything that I find interesting and hope that others can benefit from it.

Thanks.

- Raza