Database Reference
In-Depth Information
BETWEEN Conditions
You can use the BETWEEN operator to determine whether a value is within a range of values. The following
WHERE clause is true if the balance is between 1,000 and 5,000:
349
WHERE Balance BETWEEN 1000 AND 5000
LIKE Conditions
LIKE conditions use wildcards to select rows. Use the percent sign (%) to represent any collection of charac-
ters. The condition LIKE
'
%Oxford%
'
will be true for data consisting of any character or characters followed
by the letters
followed by any other character or characters. Another wildcard is the underscore
character (_), which represents any individual character. For example,
'
T_m
'
represents the letter T followed
by any single character followed by the letter m and would be true for a collection of characters such as Tim,
Tom, or T3m.
Note: In Access SQL, the asterisk (*) is used as a wildcard to represent any collection of characters. (In
MySQL, the percent sign (%) is used as a wildcard to represent any collection of characters.) Another wildcard
in Access SQL is the question mark (?), which represents any individual character. Many versions of SQL,
including MySQL, use the underscore (_) instead of the question mark to represent any individual character.
The following WHERE clause is true if the value in the Street column is Ox, ford, Oxford, or any other
value that contains
“
Oxford
”
“
Oxford
”
:
WHERE Street LIKE
'
%Oxford%
'
Access version:
WHERE Street LIKE
'
*Oxford*
'
IN Conditions
You can use the IN operator to determine whether a value is in some specific collection of values. The fol-
lowing WHERE clause is true if the credit limit is 7,500, 10,000, or 15,000:
WHERE CreditLimit IN (7500, 10000, 15000)
The following WHERE clause is true if the part number is in the collection of part numbers located in
warehouse 3:
WHERE PartNum IN
(SELECT PartNum
FROM Part
WHERE Warehouse=
'
3
'
)
CREATE INDEX
Use the CREATE INDEX command to create an index for a table. Figure B-2 describes the CREATE INDEX
command.
Clause
Description
Required?
CREATE INDEX
index name
Indicates the name of the index.
Yes
ON
table name
Indicates the table for which the index is to be created.
Yes
column list
Indicates the column or columns on which the index is to be tested.
Yes
FIGURE B-2
CREATE INDEX command
The following CREATE INDEX command creates an index named RepBal for the Customer table on the
combination of the RepNum and Balance columns:
CREATE INDEX RepBal
ON Customer (RepNum, Balance)
;
