Programming the Perl DBI

Queries and Condition Clauses

The previous example relied on retrieving all the rows within a table, whereas the more ordinary, everyday database operations will usually require more accurate targeting of specific rows. For example, “Tell me the names of all the stone circles in Wiltshire” is a more specific query than “Tell me about all the stone circles in the database.” To achieve this task, SQL provides the ability to specify conditions that must be met before a row is returned to the user.

SQL’s syntax regarding condition clauses is just as straightforward and obvious as that for specifying which columns are of interest. The condition clauses that narrow the query are specified after the list of tables from which data is being retrieved, i.e., after the FROM clause and table list.

Therefore, a query that retrieves the name and location columns from rows that contain the string ``Wiltshire'' in the location column, can be written as:

SELECT name, location
FROM megaliths
WHERE location LIKE '%Wiltshire%'

The information returned from this query would be:

+--------------------------------------------------+
| name         | location                          |
+--------------------------------------------------+
| Stonehenge   | Near Amesbury, Wiltshire, England |
| Avebury      | Avebury, Wiltshire, England       |
+--------------------------------------------------+

The returned information shows just the columns specified for the sites that have a location value containing the string, ``Wiltshire.'' The WHERE keyword is the ...