Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Aug 18, 2000

Simple SQL: Pt. 1 - Page 4

By Ted Brockwood

Other Comparison Functions

Other available comparison functions in SQL are:

>greater than
<less than
>=greater than or equal to
<=less than or equal to
=equal to

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:

+addition
/division
*multiplication

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.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date