Computers are good at keeping up appearances: text, pictures, sounds. Below the surface are elec-trons fl owing through electronic circuits, participating in billions of calculations per second. In short, computers are all about math, and all programming languages were designed to take advan-tage of it. SQL is no exception. While it might not be as powerful in math as languages specifi cally designed for this purpose (for example, language “R” designed specifi cally for statistical calcula-tions), the built in mathematical functions allow for rather sophisticated mathematical expressions to be inserted into your queries.
Let’s take a look at SQL’s most useful numeric functions. To illustrate the functionality we are going to use Microsoft SQL Server syntax; to make it work with your RDBMS you might need to make some modifi cations. SQL Server, Microsoft Access, and PostgreSQL allow you to execute a SELECT statement containing an expression without pointing to an actual table, but Oracle and IBM DB2 require you to SELECT from something (add “FROM dual;” and “FROM sysibm.sysdummy1”
at the end of your query, respectively); MySQL would also want you to use DUAL pseudo-table;
OpenOffi ce.org BASE does not allow free form expressions. Table 4-1 presents a matrix of the numeric (mathematical) functions for the RDBMSs discussed in the book.
c04.indd 104
c04.indd 104 3/15/2011 12:22:32 PM3/15/2011 12:22:32 PM
www.it-ebooks.info
Neither Microsoft Access nor OpenOffi ce implementations of the SQL contain all functions described in the chapter, even those considered standard by the SQL committee. Please see vendor’s documentation to fi nd out whether a spe-cifi c function is implemented in your RDBMS.
While it is safe to assume that libraries will only deal with positive numbers, you might cross into a negative territory at some point. The SIGN function will tell you where you stand:
SELECT SIGN(5) AS positive, SIGN(-5) AS negative;
positive negative ---1 ---1
Interestingly enough, the sign indicator will change depending on the data type of the numeric values you’re passing into the function: Integers will return simple 1,-1, but real numbers (FLOAT, DECIMAL, REAL) will display as many zeroes as there are decimal places in the number.
SELECT SIGN(5) AS positive, SIGN(-5.0001) AS negative;
positive negative ---1 ---1.0000
Sometimes you have to be sure that the numbers you deal with are always in positive territory, there-fore you might need the ABS() function, which returns the absolute value of the number.
SELECT ABS(5) AS former_positive, ABS (-5.0001) AS former_negative;
former_positive former_negative -- ---5 ---5.0001
The other common mathematical functions help calculate the square root and evaluate the expres-sion to a specifi c power.
SELECT POWER(2,2) as two_squared, POWER(2,3) as two_cubed;
two_squared two_cubed -- ---4 8
The SQRT function will extract the square root from a number.
SELECT POWER(2,2) as two_squared, SQRT(POWER(2,2)) as square_root;
two_squared square_root -- ---4 2
c04.indd 105
c04.indd 105 3/15/2011 12:22:33 PM3/15/2011 12:22:33 PM
www.it-ebooks.info
While there is no equivalent CURT function to extract cubic root, you can use fractional values in the POWER function to achieve the same result. Even though integer values worked in the previous examples, for fractional values we must use decimals (FLOAT).
SELECT CAST(POWER(27.0,1/3.0)AS FLOAT) as 3rd_root 3rd_root
---3
Generating random numbers could come in handy in many situations. The RAND() functions generate a pseudo-random number between 0 and 1. For most practical purposes, it could be con-sidered “random enough,” but keep in mind that its uniqueness is not guaranteed; sooner or later this function is bound to produce identical values. Some RDBMSs have implemented more rigorous functions to produce truly unique identifi ers (for example, the SQL Server NEWID() function), but this functionality is nonstandard. The RAND() function accepts one optional argument: seed, of the INTEGER data type. If the argument is not specifi ed, the MS SQL Server would assign the seed randomly, producing different results each time the function is called. With a seed specifi ed, the value will always be the same. For example:
SELECT RAND() AS random, RAND(10) as seed10, RAND(10) as seed10more random seed10 seed10more
---0.182458908613686 0.713759689954247 0.713759689954247
In addition, many RDBMSs have implemented more sophisticated mathemati-cal capabilities used in statistimathemati-cal analysis such as STDDEV(), which returns standard deviation of a sample; VAR(), which returns the variance of a sample;
or VARP(), returning variance of a population. These are outside of the scope for this book, but give you an idea of the possibilities.
Rounding is an essential math operation. With a little help from the tree of available functions, CEIL[ING](), FLOOR(), and ROUND, quite a bit can be accomplished in SQL. For instance, you could round up or round down the price of the books in the LIBRARY database while selecting records by running this query:
SELECT bk_title ,CEILING(bk_price) ,FLOOR(bk_price) ,ROUND(bk_price, 1) FROM books;
The output is two integers and a decimal rounded to one decimal place. Note that the calculations were performed per row. The CEIL function (short for CEILING; some RDBMSs require full name, some are content with the short version) returns an integer value that is closer to positive infi nity;
FLOOR returns an integer value that is closer to negative infi nity, and ROUND just does what it was asked to do: round to the nearest number with one decimal place. Table 4-1 lists selected mathematical functions across the RDBMS servers.
c04.indd 106
c04.indd 106 3/15/2011 12:22:33 PM3/15/2011 12:22:33 PM
Download from Wow! eBook <www.wowebook.com>
www.it-ebooks.info
TABLE 4-1: Select Mathematical SQL Functions
SQL FUNCTION DESCRIP TION RDBMS SUPPORT
ABS Returns the absolute value of a numeric input argument.
All
POWER Returns the argument X raised to the power Y.
Microsoft Access uses ‘^’ operator, and SQL Server has both the func-tion and the operator
SQRT Returns the square root of the argument X. Microsoft Access uses function SQR RAND Generates some random numbers
between 0 and 1.
Microsoft Access uses function RND
FLOOR Rounds numeric arguments down to the nearest integer value.
All
CEIL Rounds numeric arguments up to the nearest integer value.
All; some support also CEILING synonym
ROUND Returns the numeric argument rounded to the integer number of decimal places.
All