Simple SQL: Pt. 1 - Page 4

Other Comparison Functions

Other available comparison functions in SQL are:

Mathematical functions can be performed in SQL as they are in most languages. Taking the previous example, assume we want to find out what the differences are for everyone between the vacation days taken, and what they have accrued. Your statement would be:

SELECT FIRST_NAME,LAST_NAME,
       VACATION_TAKEN,VACATION_ACCRUED,
       (VACATION_ACCRUED-VACATION_TAKEN)
       FROM EMPLOYEES;

The resulting table would display the employees' first and last names, days taken, days accrued, and the difference between the two. Any employee with a negative in the last column might want to start job hunting.

Other mathematical functions include, but are not limited to:

The WHERE clause can be strengthened by the use of AND, OR, and NOT operators. Through these three logical operators, one can string together more powerful queries.

Using the previous imaginary EMPLOYEES table as a base, we can create a query that returns the vacation days available for all the employees whose last name begins with "J". This query would be written as such:

SELECT FIRST_NAME, LAST_NAME,VACATION TAKEN, VACATION_ACCRUED
       FROM EMPLOYEES
       WHERE LAST_NAME LIKE "J*"
       AND (VACATION_ACCRUED - VACATION_TAKEN) > 0;

Your output would be a list of first names, last names where the employee has at least one vacation day left.

Using OR allows you to be more flexible. Should you want the same data, but also for those employees with a last name starting with "K", you could use this statement:

SELECT FIRST_NAME, LAST_NAME,VACATION TAKEN, VACATION_ACCRUED
       FROM EMPLOYEES
       WHERE LAST_NAME LIKE "J*"
       OR LAST_NAME LIKE "K*"
       AND (VACATION_ACCRUED - VACATION_TAKEN) > 0;

As you can see by now, SQL allows for some very powerful data extraction capabilities. Along with simple SELECT statements, there are mathematical, logical, and comparison functions. In the next part of this series, I'll be covering grouping, ordering, sorting, and some advanced mathematical functions. Later, we'll look at queries that allow the user to add, modify, and delete data from databases.

Ted Brockwood is the Information Services Manager for a real estate listing service in Oregon. His experience covers Java, Linux, UNIX, NT, Win95/98, Win3.x, and DOS.