SQL REFERENCE - Concepts of Database Management - page 347

LIKE Conditions
LIKE conditions use wildcards to select rows. Use the percent sign (%) to represent any collection of characters.
The condition LIKE '%Oxford%' will be true for data consisting of any character or characters followed by the
letters “Oxford” followed by any other character or characters. Another wildcard is the underscore charac-
ter (_), 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 wild-
card 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”:

347

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 following
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)
;

CREATE TABLE

Use the CREATE TABLE command to create a table by describing its layout. Figure B-3 describes the
CREATE TABLE command.