Monday, December 24, 2007

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' 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.

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 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


oakleyses said...

jordan shoes, ugg boots, longchamp outlet, uggs on sale, ray ban sunglasses, replica watches, ray ban sunglasses, ray ban sunglasses, christian louboutin shoes, nike free run, louboutin pas cher, michael kors pas cher, louis vuitton outlet, air max, prada outlet, tory burch outlet, christian louboutin, louis vuitton, nike air max, nike roshe, replica watches, tiffany and co, oakley sunglasses wholesale, nike free, longchamp pas cher, christian louboutin uk, louis vuitton, burberry pas cher, chanel handbags, nike outlet, nike air max, polo ralph lauren outlet online, gucci handbags, oakley sunglasses, jordan pas cher, prada handbags, oakley sunglasses, cheap oakley sunglasses, louis vuitton outlet, longchamp outlet, polo outlet, ugg boots, tiffany jewelry, longchamp outlet, christian louboutin outlet, louis vuitton outlet, sac longchamp pas cher, oakley sunglasses, polo ralph lauren

oakleyses said...

true religion jeans, ray ban pas cher, michael kors, oakley pas cher, polo lacoste, michael kors outlet online, ray ban uk, mulberry uk, michael kors outlet online, coach outlet, burberry handbags, nike air force, vans pas cher, true religion outlet, michael kors outlet, abercrombie and fitch uk, nike roshe run uk, replica handbags, burberry outlet, michael kors outlet online, kate spade, hollister pas cher, michael kors outlet online, sac hermes, nike air max uk, true religion outlet, nike air max uk, michael kors outlet, guess pas cher, north face, hollister uk, new balance, nike tn, uggs outlet, uggs outlet, ralph lauren uk, michael kors, true religion outlet, nike air max, timberland pas cher, nike blazer pas cher, sac vanessa bruno, converse pas cher, coach purses, coach outlet store online, north face uk, nike free uk, hogan outlet, michael kors outlet

oakleyses said...

wedding dresses, longchamp uk, new balance shoes, insanity workout, gucci, nike roshe run, nike huaraches, north face outlet, celine handbags, giuseppe zanotti outlet, ghd hair, ralph lauren, nike trainers uk, mac cosmetics, iphone cases, asics running shoes, nfl jerseys, mcm handbags, north face outlet, ray ban, reebok outlet, converse outlet, timberland boots, hermes belt, mont blanc pens, ferragamo shoes, hollister, hollister clothing, nike air max, abercrombie and fitch, vans outlet, vans, converse, instyler, louboutin, lululemon, p90x workout, herve leger, soccer shoes, soccer jerseys, nike air max, jimmy choo outlet, beats by dre, oakley, baseball bats, valentino shoes, babyliss, hollister, chi flat iron, bottega veneta

oakleyses said...

ugg, louis vuitton, hollister, ugg uk, links of london, canada goose outlet, toms shoes, thomas sabo, moncler outlet, doudoune moncler, coach outlet, canada goose, ugg,uggs,uggs canada, ugg,ugg australia,ugg italia, pandora charms, replica watches, louis vuitton, supra shoes, pandora jewelry, juicy couture outlet, canada goose outlet, barbour, swarovski crystal, pandora uk, montre pas cher, juicy couture outlet, lancel, canada goose, moncler, canada goose jackets, louis vuitton, canada goose uk, marc jacobs, moncler, pandora jewelry, canada goose, swarovski, canada goose outlet, moncler uk, moncler, louis vuitton, karen millen uk, ugg pas cher, louis vuitton, barbour uk, wedding dresses, moncler outlet, moncler