RELATIONAL DATA RETRIEVAL: SQL - Fundamentals of Database Management Systems - page 79

mean any number of letters followed by ''a'', which is not what you want. In
order to make sure that there is just one character followed by ''a'', which is the
same thing as saying that ''a'' is the second letter, you would specify '_a%'. The
''_'' wildcard character means that there will be exactly one letter (any one letter)
followed by the letter ''a''. The ''%'', as we already know, means that any string
of characters can follow afterwards.

SELECT *
FROM CUSTOMER
WHERE CUSTNAME LIKE '_a%';

The result would be:

CUSTNUM CUSTNAME SPNUM HQCITY

0121 Main St. Hardware 137 New York

0839 Jane's Stores 186 Chicago

Notice that both the words ''Main'' and ''Jane's'' have ''a'' as their second
letter. Also notice that, for example, customer number 2267 was not included in the
result. Its name, ''Central Stores'', has an ''a'' in it but it is not the second letter
of the name. Again, the single ''_'' character in the operator LIKE '_a%' specifies
that there will be one character followed by ''a''. If the operator had been LIKE
'%a%', then Central Stores would have been included in the result.

Filtering the Results of an SQL Query Two ways to modify the results of an SQL SELECT
command are by the use of DISTINCT and the use of ORDER BY . It is important
to remember that these two devices do not affect what data is retrieved from the
database but rather how the data is presented to the user.

DISTINCT There are circumstances in which the result of an SQL query may contain
duplicate items and this duplication is undesirable. Consider the following query:

''Which cities serve as headquarters cities for General Hardware customers?''

This could be taken as a simple relational Project that takes the HQCITY column of
the CUSTOMER table as its result. The SQL command would be:

SELECT HQCITY
FROM CUSTOMER;

which results in:

HQCITY New York

Chicago
Los Angeles
Los Angeles
Atlanta
Washington
New York
New York
New York