Team Fly 

Page 46

between A and B

Greater than or equal to A and less than or equal to B.

select * from sales where amount_sold is between 100 and 500;

not between A and B

Not greater than or equal to A, and not less than or equal to B.

select * from sales where amount_sold is not between 100 and 500;

like '%tin%'

Contains given text (for example, 'tin').

select * from customer where cust_last_name is like '%tin%';

TABLE 2-2. Common Comparison Operators

CRITICAL SKILL 2.4
Use Basic update and delete Statements

While select will likely be the command you use the most; you'll use the update and delete commands regularly, too. As you will in Chapter 6, your programs will have a mixture of DML statements. In this section, we'll take a closer look at the update and delete commands.

update

It is often necessary to change data stored within a table. This is done using the update command. There are three parts to this command:

1. The word update followed by the table to which you want to apply the change. This part is mandatory.

2. The word set followed by one or more columns in which you want to change the values. This part is also mandatory.

3. A where clause followed by selection criteria. This is optional.

Let's imagine that one of our customers has requested an increase in their credit limit and our accounting department has approved it. An update statement will have to be executed to alter the credit limit. For illustration purposes, a customer record will be displayed before and after the update. The following example illustrates a simple update for one customer:

SQL> select cust_id, cust_credit_limit
  2  from   customers
Team Fly 
0065-CRITICAL SKILL 2.4 Use Basic update and delete Statements