eXtropia: the open web technology company
Technology | Support | Tutorials | Development | About Us | Users | Contact Us
 ::   Tutorials
 ::   Presentations
Perl & CGI tutorials
 ::   Intro to Perl/CGI and HTML Forms
 ::   Intro to Windows Perl
 ::   Intro to Perl 5
 ::   Intro to Perl
 ::   Intro to Perl Taint mode
 ::   Sherlock Holmes and the Case of the Broken CGI Script
 ::   Writing COM Components in Perl

Java tutorials
 ::   Intro to Java
 ::   Cross Browser Java

Misc technical tutorials
 ::   Intro to The Web Application Development Environment
 ::   Introduction to XML
 ::   Intro to Web Design
 ::   Intro to Web Security
 ::   Databases for Web Developers
 ::   UNIX for Web Developers
 ::   Intro to Adobe Photoshop
 ::   Web Programming 101
 ::   Introduction to Microsoft DNA

Misc non-technical tutorials
 ::   Misc Technopreneurship Docs
 ::   What is a Webmaster?
 ::   What is the open source business model?
 ::   Technical writing
 ::   Small and mid-sized businesses on the Web

Offsite tutorials
 ::   ISAPI Perl Primer
 ::   Serving up web server basics
 ::   Introduction to Java (Parts 1 and 2) in Slovak


Introduction to Databases for Web Developers
And, Or, and Not  
So far, the WHERE clauses we have discussed have been pretty darn simple. And as such, they are not all that useful in real world situations in which multiple conditions affect our needs. Actually, SQL provides a set of operators that allow you to combine multiple predicate statements. Specifically, a WHERE clause can be made up of multiple predicates by chaining them together with the AND, OR, and NOT operators.

These operators work as you might expect. The AND operator specifies multiple conditions which a column must match in order to be returned. The OR operator specifies multiple acceptable conditions. And the NOT operator specifies negativity in a match. However, These are best understood by example.

Consider the case in which we want to return only rows from the SALES table in which the salesman was salesman number "101", but we only want sales in which the price was greater than $100.00

    WHERE E_NUM = 101 AND S_AMOUNT > 100;

In this case, our database would return:

    002		865.99		101

In this case, the row was returned because S_AMOUNT was greater than "100" "AND" E_NUM was equal to "101". Now consider the same query using OR:

    WHERE E_NUM = 101 OR S_AMOUNT > 100;

In this case, we would get the following response:

    001		99.99		101
    001		199.98		102
    003		865.99		101

Notice that the first row satisfied the condition because E_NUM was equal to "101" even though S_AMOUNT was less than "100". Likewise, the second row was selected because S_AMOUNT was greater than "100" even though E_NUM was not equal to "101"

Another important thing to note about AND and OR is that if you decide to combine multiple conditions, you must be very careful about how you compose your predicate. AND and OR conditions can be defined logically using parentheses as in most languages, but the order of the parentheses has meaning. Thus

     SEX = "M" AND (WEIGHT > 150 OR AGE < 35)
is much different than saying

     (SEX = 'M' AND WEIGHT > 150) OR AGE < 35

In the first case, no female subject could be returned whereas in the second case, a female could be returned if she was younger than 35.

Finally, the NOT operator is used to specify the reverse condition.

Thus, if you want a list of all the employees other than Lim Sing Yuen from the sample database, you would use:

     WHERE NOT (EMP_NAME = 'Lim Sing Yuen');

In this case, the database would return the following:

    001		Lim Li Chuen
    003		Loo Soon Keat

Previous | Next | Table of Contents