Team Fly

Page 42

if you wanted to find all the product IDs in the products table that are either in the Hardware product category or have a weight class of 4, you would generate the following SQL statement and output:

SQL> select prod_id, prod_category, prod_weight_class
  2  from   products
  3  where  prod_category = 'Hardware'
  4  or     prod_weight_class = 4;
   PROD_ID PROD_CATEGORY
PROD_WEIGHT_CLASS
---------- -------------------------------------------------- --------- 
        15 Hardware
1
        18 Hardware
1
       139 Electronics
4

The and and or are known as logical operators. They are used to tell the query how the multiple criteria affect each other. Compound conditions connected by the and keyword all have to evaluate to true for records to be retrieved. Records are returned by compound conditions connected by the or keyword when either one of the conditions are true.

The where Clause with NOT

The ability also exists within Oracle to retrieve records with negative criteria. The ''not equals" operator is !=. For example, you might want to see all the products that are not in weight class 1. The following query and its output illustrate this example:

SQL> select prod_id, prod_category, prod_weight_class
  2  from   products
  3  where  prod_weight_class != 1;
   PROD_ID PROD_CATEGORY
PROD_WEIGHT_CLASS
---------- -------------------------------------------------- ---------
       139 Electronics
4

The where Clause with a Range Search

Oracle also supports range searches so you can query for records that are between two values. If you want to find all male customers in Connecticut who were born between 1936 and 1939, you would write a query with three conditions joined by

Team Fly