| Team Fly | |
PROMO_CATEGORY_ID NOT NULL NUMBER PROMO_COST NOT NULL NUMBER(10,2) PROMO_BEGIN_DATE NOT NULL DATE PROMO_END_DATE NOT NULL DATE PROMO_TOTAL NOT NULL VARCHAR2(15) PROMO_TOTAL_ID NOT NULL NUMBER
The PRODUCTS table contains more than 70 products for sale. The following select statement will retrieve only one record for product ID (prod_id) 117.
SQL> select prod_id, prod_name, prod_category, prod_list_price
2 from products
3 where prod_id = 117;
PROD_ID PROD_NAME
---------- --------------------------------------------------
PROD_CATEGORY PROD_LIST_PRICE
-------------------------------------------------- ---------------
117 CD-R, Professional Grade, Pack of 10
Software/Other 8.99
(2)A where Clause with and/or
A where clause instructs Oracle to search the data in a table and return only those rows that meet your criteria. In the preceding example, we searched the products table for one specific record with a product ID equal to 117. This was accomplished with where prod_id = 117;.
You will often be interested in retrieving rows that meet multiple criteria—for example, if you want to retrieve a list of customers from Utah who have a credit limit greater than $10,000. The SQL statement and results would produce the following output:
SQL> select cust_id, cust_state_province, cust_credit_limit
2 from customers
3 where cust_state_province = 'UT'
4 and cust_credit_limit > 10000;
CUST_ID CUST_STATE_PROVINCE CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
50601 UT 11000
24830 UT 15000
28983 UT 15000
100267 UT 11000
100207 UT 11000
103007 UT 15000
6 rows selected.
In the previous example, we retrieved records that met all the criteria. You may be interested in retrieving records that meet one criteria or another. For example,
| Team Fly | |