Discovering SQL: A Hands-On Guide for Beginners

IN NUMBERS, STRENGTH

Computers are good at keeping up appearances: text, pictures, sounds. Below the surface are electrons flowing 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 advantage of it. SQL is no exception. While it might not be as powerful in math as languages specifically designed for this purpose (for example, language “R” designed specifically for statistical calculations), 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 modifications. 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; OpenOffice.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.

Neither Microsoft Access nor OpenOffice ...