Team Fly

Page 43

the and keyword (all three need to evaluate to true) and one of the conditions would use the range search between keyword. The following example illustrates the query and resulting output:

SQL> select cust_id, cust_gender, cust_year_of_birth
  2  from   customers
  3  where  cust_state_province = 'CT'
  4  and    cust_gender = 'M'
  5  and    cust_year_of_birth between 1936 and 1939;

   CUST_ID C CUST_YEAR_OF_BIRTH
---------- - ------------------
     20058 M               1937
     17139 M               1936
      1218 M               1938
      3985 M               1939

The where Clause with a Search List

Oracle also supports searching for records that meet criteria within a list. If you wanted to find all customers in Utah and Connecticut with a credit limit of $15,000, this can be done with a search list. The following query represents a search list condition:

SQL> select cust_id, cust_state_province, cust_credit_limit
  2  from   customers
  3  where  cust_credit_limit = 15000
  4  and    cust_state_province in ('UT','CT');

   CUST_ID CUST_STATE_PROVINCE                      CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
     24830 UT                                                   15000
     28983 UT                                                   15000

Team Fly