The Cold Fusion Web Database Construction Kit
In Chapter 7, "Creating Databases and Tables," you created a database and tables for A2Z Books. In this chapter, you will learn how to retrieve data from those tables.
Structured Query Language is the language used by Cold Fusion for all database interaction. To harness the power of Cold Fusion you need a thorough understanding of SQL. The SQL statement you use to retrieve data from a table is the SELECT statement. Using SELECT, you also can sort and filter data to retrieve exactly the information you need.
SQL, pronounced sequel, is an acronym for Structured Query Language. SQL is a language you use to access and manipulate data in a relational database. It is designed to be both easy to learn and extremely powerful, and its mass acceptance by so many database vendors proves that it has succeeded in both.
In 1970, Dr. E. F. Codd, the man credited with being the father of the relational database, described a universal language for data access. In 1974, engineers at IBM's San Jose Research Center created the Structured English Query Language, or SEQUEL, built on Codd's ideas. This language was incorporated into System R, IBM's pioneering relational database system.
Toward the end of the 1980s, two of the most important standards bodies, the American National Standards Institute (ANSI) and the International Standards Organization (ISO), published SQL standards, opening the door to mass acceptance. With these standards in place, SQL was poised to become the de facto standard used by every major database vendor.
Although SQL has evolved a great deal since its early SEQUEL days, the basic language concepts and its founding premises have remained the same. The beauty of SQL is its simplicity. But don't let that simplicity deceive you. SQL is a powerful language, and it encourages you to be creative in your problem solving. You can almost always find more than one way to perform a complex query or to extract desired data. Each solution has pros and cons, and no solution is explicitly right or wrong.
Before you panic at the thought of learning a new language, let me reassure you that SQL really is easy to learn. In fact, you need to learn only four statements to be able to perform almost all the data manipulation that you will need on a regular basis. Table 8.1 lists these statements.
| Statement | Description |
| SELECT | Query a table for specific data. |
| INSERT | Add new data to a table. |
| UPDATE | Update existing data in a table. |
| DELETE | Remove data from a table. |
Each of these statements takes one or more keywords as parameters. By combining different statements and keywords, you can manipulate your data in as many different ways as you can imagine.
Cold Fusion provides you with all the tools you need to create Web-based interaction to your databases. Cold Fusion itself, though, has no built-in database. Instead, it communicates with whatever database you select, passing updates and requests and returning query results.
The communication between Cold Fusion and the database is via a database interface called Open Database Connectivity, or ODBC. ODBC is a standard application pro-gramming interface (API) for accessing information from different database systems and different storage formats. The purpose of ODBC is to enable you to access a diverse selection of databases and data formats without having to learn the features and peculiarities of each. ODBC provides a layer of abstraction, accomplished using database drivers, between your client application and the underlying database. The database drivers create a database-independent environment, as illustrated in Figure 8.1. This way, you can write one program and have it work with almost any major database system.
Of course, differences exist between database systems. Microsoft SQL Server, for example, requires you to log in to the database server before you are able to manipulate any data. Based on your login, you are granted or denied access to specific tables or other objects. Microsoft Access, on the other hand, has no concept of login-based security. If you have access to the data file (the MDB file), then you have full access to all data in it.
Figure 8.1 ODBC creates a database-independent development environment.
There are other differences, too. To access Microsoft SQL Server, your client application must know the address of the server. This might be an IP address or an NT Server name. To use Microsoft Access data files, you just need to know the drive and path to the data file.
Part of the job of ODBC is to hide these differences from your client application. And to accomplish this, each ODBC driver has its own configuration options. When you select the SQL Server ODBC driver, you are asked for a server name, a server login name, and a password, as shown in Figure 8.2. When you select the Access ODBC driver, you are prompted for a file path, as shown in Figure 8.3.
Figure 8.2 The Microsoft SQL Server ODBC driver prompts you for login information.
Figure 8.3 The Microsoft Access ODBC driver prompts you for the file path to the Access data file.
This way, your client software can load any ODBC driver and connect to a database. The ODBC driver you select will handle opening the database, whether it's opening a network file or logging in to a server. All your client software knows is that it must connect to a database; the details of how this process occurs are all hidden.
The ODBC StoryODBC was created in an effort to allow Microsoft Excel, Microsoft's popular spreadsheet program, to access diverse data stores.
In April 1988, Microsoft's Kyle Geiger proposed a model that used database drivers to isolate the native data types of different database applications. This model, in conjunction with a standard application interface, would allow client software to communicate with any message store. To access a particular data store, all that would be required is a driver designed specifically for that data store.
While Geiger worked on his proposal, engineers at DEC, Lotus, and Sybase were working on much the same ideas. The four companies joined forces, and between 1988 and 1992 they helped shape the specification.
The original name for this project was Microsoft Data Access API. In early 1989, the effort was renamed Open SQL, and then in the summer of 1989, it was renamed again to SQL Connectivity. Finally, in the winter of 1992, the name was changed one last time to Open Database Connectivity, or ODBC.
The beta version of ODBC 1.0 was released in March 1992, and in September 1992, version 1.0 finally was released. Shortly thereafter, in October 1992, the specification was reviewed and accepted by the ANSI SQL committee.
ODBC itself is not a language; the language used by ODBC is SQL. Part of the magic of the ODBC database driver is that it understands SQL and converts it to whatever is appropriate for that specific database. This way, you can use SQL commands to work with xBASE-based databases, such as Microsoft FoxPro and Borland dBASE, even though they have an entirely different native language.
Herein lies the power of ODBC. The combination of database independence and a common standard language grants ODBC clients. . . a tremendous level of freedom--freedom to use any database they want, freedom to use different databases for different tasks seamlessly and simultaneously, and the freedom to concentrate on application development without having to learn database-specific languages and API's.
Cold Fusion is an ODBC client. ODBC enables you to use Cold Fusion with whatever database you choose. If you're using Microsoft Access, then Cold Fusion uses the Access driver; if you're using Oracle, then the Oracle ODBC driver is used instead. You can even use ODBC to read and write plain-text files. As long as you have the correct ODBC driver, Cold Fusion will support that data store.
Because Cold Fusion is an ODBC client, the database language used by Cold Fusion is SQL. To truly exploit the power of Cold Fusion, you must have a thorough understanding of SQL. Fortunately, by the end of this chapter, you should be enough of a SQL expert to start generating world-class Cold Fusion applications.
ODBC client applications do not directly load ODBC drivers. In fact, they have no knowledge of what driver to use with any specific database. Rather, the application connects to a data source. A data source appears to your application as a virtual database. Within the data source, all the ODBC settings are configured, including specifying which ODBC driver to use.
Before your application can use an ODBC driver, you must create a data source. Doing so involves the following steps:
After you create your data source, any ODBC client application can use it to access or manipulate the database with which it is associated.
NOTE: The ODBC Control Panel applet and basic ODBC drivers are installed by many applications. If you have Microsoft Office installed, then you should have the applet and half dozen drivers installed, too.
If you need to obtain the applet, new ODBC drivers, or updated versions of existing drivers, the best place to start is the Microsoft FTP server at ftp.microsoft.com.
You configure ODBC data sources from within the ODBC applet in the Windows Control Panel. Try bringing up the Windows Control Panel. You should see an applet called ODBC or 32bit ODBC. Double-click the ODBC applet to open the ODBC Data Sources dialog box, as shown in Figure 8.4.
Figure 8.4 In the ODBC Data Sources dialog box, you can create and configure data sources and obtain driver version information.
The User Data Sources box shows the currently installed ODBC data sources, including generic data sources for accessing Microsoft Excel, Microsoft FoxPro, and Text Files. Double-clicking any data source opens the ODBC Setup window for the driver associated with that data source.
Figures 8.5, 8.6, and 8.7 show the Microsoft Excel Setup window, the dBASE Setup window, and the Microsoft SQL Server Setup window, respectively. Each Setup window has a required Data Source Name field and an optional Description field. All other options are driver-specific, and, therefore, vary from one driver to the next.
Figure 8.5 The Microsoft Excel ODBC driver setup prompts for Excel-specific information, including the version of Excel and worksheet-related options.
Figure 8.6 The dBASE ODBC driver setup prompts for dBASE-specific information, including the dBASE version.
Figure 8.7 The Microsoft SQL Server ODBC driver setup prompts for network login and address information.
The ODBC Data Sources dialog box is also used to configure system wide ODBC options. These options are all accessed via the buttons listed in Table 8.2.
| Button | Description |
| Options... | Configure system-wide ODBC options, such as tracing. |
| System DSN... | Set up data sources that the system, or any user, can use, rather than the local user. |
| Close | Close the ODBC Data Source dialog box. |
| Help | Obtain help. |
| Setup... | Configure the selected data source, which is the same as double-clicking a data source. |
| Delete | Permanently remove the selected data source. |
| Add... | Add a new data source using an existing ODBC driver. |
| Drivers... | Display a list of available ODBC drivers. |
Try clicking the Drivers... button now. A Drivers dialog box similar to the one shown in Figure 8.8 should appear. In this dialog box, select any driver by clicking it, and then click the About... button. An About dialog box like the one shown in Figure 8.9 should then appear.
Figure 8.8 You can check to see what ODBC drivers are installed on your system by clicking the Data Source dialog box's Drivers button.
Figure 8.9 ODBC drivers include descriptions of themselves, vendor information, the release date, and a version number.
NOTE: If you look at the About information for the Access, dBASE, Excel, FoxPro, Paradox, and Text drivers, you may notice that they are all in fact the same driver. Microsoft supplies all these drivers as part of its ODBC Desktop Driver Pack, and they are installed automatically with the ODBC applet.
Now that you've learned about data sources, you're ready to put all this newly acquired knowledge to use. In Chapter 8, "Creating Databases and Tables," you created a Microsoft Access database called A2Z. Now you're going to create an ODBC data source for this data file. Here are the steps:
That's all there is to it. The ODBC Data Sources dialog box now shows the new data source, A2Z, in the list of available User Data Sources, as shown in Figure 8.10.
Figure 8.10 When you add new data sources, they appear in the list of available User Data Sources.
NOTE: Don't confuse ODBC data sources and ODBC drivers. ODBC drivers are dynamic link libraries, or DLLs, that communicate with a specific data store type. A data source is a complete database configuration that uses an ODBC driver to communicate with a specific database.
A data source communicates with only one database. To use an ODBC driver to communicate with two or more of the same types of databases, you need to create multiple data sources that all use the same ODBC driver.
Now that you have a data source, all you need is a client application with which to access the data. Ultimately, the client you will use is Cold Fusion--after all, that is why you're reading this book. But to start learning SQL without having to learn Cold Fusion, you need to start with Microsoft Query.
Microsoft Query is an SQL query utility. It is a simple ODBC database front end that Microsoft supplies with many of their other applications, including Microsoft Office. With Microsoft Query, you can test ODBC connectivity, interactively build SQL statements, and view the results of SQL queries, all in an easy-to-use environment. Microsoft Query is therefore a useful development and prototyping tool, and one well worth learning.
NOTE: If you set up Microsoft Office using the minimum setup, then you might not have Microsoft Query installed. If this is the case, run the Office setup program again and select Microsoft Query from the database tools option.
TIP: As you start developing Cold Fusion applications, you will find that most data-retrieval problems are in fact caused by incorrect SQL statements. Microsoft Query is a useful debugging tool because it enables you to test SQL statements interactively. Using Microsoft Query is a powerful way to validate SQL queries and to isolate data-retrieval problems.
Now run Microsoft Query. When the program loads, you should see a screen similar to the one shown in Figure 8.11. Along the top of the screen is the toolbar that gives you quick access to commonly used functions. The toolbar buttons are described in Table 8.3.
Figure 8.11 Microsoft Query is a multiple document interface (MDI) application. Using it, you can open multiple documents, or in this case queries, at once.
| Button | Effect |
 |
Create a new query. |
 |
Open a saved query. |
 |
Save the currently selected query. |
 |
View or edit a query's SQL statement directly. |
 |
Show or hide the available tables pane. |
 |
Show or hide the selection criteria pane. |
 |
Include additional tables in the currently selected query. |
 |
Show only records that match the value of the selection. |
 |
Cycle through available for the currently selected column. |
 |
Sort the table via the currently selected column in ascending order. |
 |
Sort the table via the currently selected column in descending order. |
 |
Execute the query immediately. |
 |
Automatically execute the query as it is created and changed, and show results immediately. |
 |
Display Microsoft Query online help. |
You are now ready to create your first query. Click the New Query button to open the Select Data Source dialog box, as shown in Figure 8.12.
Figure 8.12 In the Microsoft Query Select Data Source dialog box, you can select the data source for your new query.
The first time Microsoft Query uses a data source you need to add it to the Select Data Source dialog box. To do so, click the Other... button to view the currently available data sources, then select the A2Z data source that you just created and click OK.
The A2Z data source then appears in the Select Data Source dialog box, as shown in Figure 8.13. At this point, select the A2Z data source, and then click Use.
Figure 8.13 The first time Microsoft Query uses a data source you need to add it to the Select Data Source dialog box.
With all the preliminaries taken care of, you can roll up your sleeves and start writing SQL. The SQL statement that you will most use is the SELECT statement. You use SELECT, as its name implies, to select data from a table.
Most SELECT statements require at least the following two parameters:
When you click Use in the Select Data Source dialog box to open a data source in a new query, Microsoft Query prompts you for the tables to include in this query. This feature is useful for interactively building queries. But because you're going to learn how to create queries by writing SQL statements yourself, don't select any tables now. Just click the Close button.
Once you have selected your data source, Microsoft Query will display the Query window, as shown in Figure 8.14. The top half is used by Microsoft Query to show tables in use and to display their relationships graphically if any are defined. In the bottom half of the screen, the results of your query are displayed.
Figure 8.14 The Microsoft Query window is split into a table pane and a data pane.
Click the View SQL button (or choose SQL... from the View menu) to open the SQL window. Here, you can view the SQL statement that produced the query results shown, and you also can create and modify SQL statements directly.
The first SQL SELECT you will create is a query for a list of employees' last names and phone extensions. Type the code in Listing 8.1 into the SQL Statement box, as shown in Figure 8.15, and then click OK.
SELECT Employees.LastName, Employees.FirstName, Employees.PhoneExtension FROM A2Z.Employees
Figure 8.15 In the SQL window, you can view generated SQL or enter SQL statements directly.
That's it! You've written your first SQL statement. Microsoft Query shows the table you are using in the top half of the screen, and the results of your query appear in the bottom half. You should have 10 records listed, the same 10 records you entered into Microsoft Access directly, as shown in Figure 8.16.
Figure 8.16 Microsoft Query displays query results in the data pane, the bottom part of the Query window.
NOTE: You an enter SQL statements on one long line or break them up over multiple lines. All white-space characters (spaces, tabs, new-line characters) are ignored when the command is processed. If you break a statement onto multiple lines and indent parameters, you make the statement easier to read and debug.
Before going any further, take a closer look at the SQL code you entered. The first parameter you pass to the SELECT statement is a list of three columns you want to see. A column is specified as table.column, such as Employees.LastName, where Employees is the table name and LastName is the column name.
Because you want specify three columns, you have to separate them with commas. No comma appears after the last column name, so if you have only one column in your select list, you don't need a comma.
Right after the select list, you specify the table on which you want to perform the query. You always precede the table name with the keyword FROM. The table name itself is fully qualified, meaning it is specified as database.table, or in this case A2Z.Employees.
NOTE: SQL statements are not case-sensitive; that is, you can specify the SELECT statement as SELECT, select, Select, or however you want. Common practice, however, is to enter all SQL keywords in uppercase and parameters in lowercase or mixed case. This way, you can read the SQL code and spot typos more easily.
Now modify the SELECT statement so it looks like the code in Listing 8.2. Click the SQL button to make the code changes, and then click OK.
SELECT Employees.* FROM A2Z.Employees
This time, instead of specifying explicit columns to select, you use an asterisk. The asterisk is a special select list option that represents all columns. The data pane now shows every column in the table in the order in which they appear in the table itself.
CAUTION: Generally, you should not use an asterisk in the select list unless you really need every column. Each column you select requires its own processing, and retrieving unnecessary columns can dramatically affect the retrieval times as your tables get larger.
When you use the SELECT statement, the results are returned to you in the order in which they appear in the table. This is usually the order in which the rows were added to the table, typically not a sort order that is of much use to you. More often than not, when you retrieve data with a SELECT statement, you want to sort the query results. To sort rows, you need to add the ORDER BY clause. ORDER BY always comes after the table name; if you try to use it before, you generate an SQL error.
Now click the SQL button, and enter the SQL code shown in Listing 8.3. Then click OK.
SELECT Employees.LastName, Employees.FirstName, Employees.PhoneExtension FROM A2Z.Employees ORDER BY PhoneExtension
Your output is then sorted by the PhoneExtension column, as shown in Figure 8.17.
Figure 8.17 You use the ORDER BY clause to sort SELECT output.
What if you need to sort by more than one column, as you did in the beginning of Chapter 7? No problem. You can pass multiple columns to the ORDER BY clause. And once again, if you have multiple columns listed, you need to separate them with a comma. The SQL code in Listing 8.4 demonstrates how to sort on more than one column by sorting the employee list by last name plus first name. The sorted output is shown in Figure 8.18.
SELECT Employees.LastName, Employees.FirstName, Employees.PhoneExtension FROM A2Z.Employees ORDER BY LastName, FirstName
Figure 8.18 Using the ORDER BY clause, you can sort output by more than one column.
You also can use ORDER BY to sort data in descending order (from Z to A). To sort a column in descending order, just use the DESC (short for descending) parameter. Listing 8.5 retrieves all the employee records and sorts them by extension in reverse order. Figure 8.19 shows the output that this SQL SELECT statement generates.
SELECT Employees.LastName, Employees.FirstName, Employees.PhoneExtension FROM A2Z.Employees ORDER BY PhoneExtension DESC
Figure 8.19 Using the ORDER BY clause, you can sort data in a descending sort sequence.
So far, all your queries have retrieved all the rows in the table. You also can use the SELECT statement to retrieve only data that matches a specific search criteria. To do so, you must use the WHERE clause and provide a restricting condition. If a WHERE clause is present, when the SQL SELECT statement is processed, every row is evaluated against the condition. Only rows that pass the restriction are selected.
If you use a WHERE clause, it must appear after the table name. If you use both the ORDER BY and WHERE clauses, the WHERE clause must appear after the table name but before the ORDER BY.
To demonstrate filtering, modify the SELECT statement to retrieve only employees whose last name is Smith. Listing 8.6 contains the SELECT statement, and the resulting output is shown in Figure 8.20.
SELECT Employees.LastName, Employees.FirstName, Employees.PhoneExtension FROM A2Z.Employees WHERE LastName = "Smith"
Figure 8.20 Using the WHERE clause, you can restrict the scope of a SELECT search.
The WHERE clause also can take multiple conditions. To search for Jack Smith, you can specify a search condition in which the last name is Smith and the first name is Jack, as shown in Listing 8.7. As Figure 8.21 shows, only Jack Smith is retrieved.
SELECT Employees.LastName, Employees.FirstName, Employees.PhoneExtension FROM A2Z.Employees WHERE LastName = "Smith" AND FirstName = "Jack"
Figure 8.21 Using multiple WHERE clauses, you can narrow down your search.
Multiple WHERE clauses can be evaluated as AND conditions or OR conditions. The example in Listing 8.7 is an AND condition. Only rows in which both the last name is Smith and the first name is Jack will be retrieved. If you change the clause to the following, other employees with a last name of Smith are retrieved no matter what the first name:
WHERE LastName = "Smith" OR FirstName = "Jack"
Similarly, any employee named Jack is retrieved, regardless of the last name.
You can combine the AND and OR operators to create any search condition you need. Listing 8.8 and 8.9 show two different WHERE clauses that accomplish the exact same thing--specifically, retrieving only Jack Smith and Kim Black.
SELECT Employees.LastName, Employees.FirstName, Employees.PhoneExtension FROM A2Z.Employees WHERE (LastName = "Smith" AND FirstName = "Jack") OR (LastName = "Black" AND FirstName = "Kim")
SELECT Employees.LastName, Employees.FirstName, Employees.PhoneExtension FROM A2Z.Employees WHERE (LastName = "Smith" OR LastName = "Black") AND (FirstName = "Jack" OR FirstName = "Kim")
When a WHERE clause is processed, the operators are evaluated in the following order of precedence:
What does this mean? Well, look at the WHERE clause in Listing 8.9. The clause reads WHERE (LastName = "Smith" OR LastName = "Black") AND (FirstName = "Jack" OR FirstName = "Kim"). This clause evaluates to the following:
(LastName = "Smith" OR LastName = "Black"). This clause retrieves only people whose last name is Smith or Black.
AND (FirstName = "Jack" OR FirstName = "Kim"). Of the names retrieved, this clause keeps only those whose first name is Jack or Kim. The rest are discarded.
The results of this query are shown in Figure 8.22. As you can see, only Jack Smith and Kim Black are retrieved, which is exactly the result you want.
Figure 8.22 With parentheses, you can control the precedence with which operators are evaluated.
Without the parentheses, the clause would read WHERE LastName = "Smith" OR LastName = "Black" AND FirstName = "Jack" OR FirstName = "Kim". Because the AND operator takes precedence over the OR operator, this clause would be evaluated as follows:
WHERE LastName = "Smith". This clause retrieves anyone whose last name is Smith, regardless of first name.
OR LastName = "Black" AND FirstName = "Jack". This clause also retrieves anyone whose last name is Black and whose first name is Jack.
OR FirstName = "Kim". And finally, this clause also retrieves anyone whose first name is Kim.
The results of this query are shown in Figure 8.23. As you can see, Jane Smith is also retrieved. Because no parentheses bind the Smith restriction with the Jack restriction, the Smith restriction is evaluated by itself. Jane Smith is therefore a valid match.
Figure 8.23 Without parentheses, the default order of precedence is used, and the results might not be what you expect.
Obviously, this result is not what you want. To force the correct evaluation precedence for your operators, you must use parentheses. This way, there is no doubt as to what you are trying to retrieve.
TIP: Always using parentheses whenever you have more than one WHERE clause is good practice. They make the SQL statement easier to read and easier to debug.
For the examples to this point, you have used only the = (equal) operator. You filtered rows based on their being equal to a specific value. Many other operators and conditions can be used with the WHERE clause; they're listed in Table 8.4.
| Condition | Description |
| = | Equal to. Tests for equality |
| <> | Not equal to. Tests for nonequality. |
| < | Less than. Tests that the value on the left is less than the value on the right. |
| <= | Less than or equal to. Tests that the value on the left is less than or equal to the value on the right. |
| > | Greater than. Tests that the value on the left is greater than the value on the right. |
| >= | Greater than or equal to. Tests that the value on the left is greater than or equal to the value on the right. |
| BETWEEN | Tests that a value is in the range between two values; the range is inclusive. |
| EXISTS | Tests for the existence of rows returned by a subquery. |
| IN | Tests to see whether a value is contained within a list of values. |
| IS [NOT] NULL | Tests to see whether a column contains a NULL value (or a non-NULL value). |
| LIKE | Tests to see whether a value matches a specified pattern. |
| NOT | Negates any test. |
You use the = operator to test for value equality. The following example retrieves only employees whose last name is Smith:
WHERE LastName = "Smith"
You use the < > operator to test for value nonequality. The following example retrieves only employees whose first name is not Kim:
WHERE FirstName < > "Kim"
Using the < operator, you can test that the value on the left is less than the value on the right. The following example retrieves only employees whose last name is less than C, meaning that their last name begins with an A or a B:
WHERE LastName < "C"
Using the <= operator, you can test that the value on the left is less than or equal to the value on the right. The following example retrieves only employees whose phone extension is 4500 or less:
WHERE PhoneExtension < "4500"
You use the > operator to test that the value on the left is greater than the value on the right. The following example retrieves only employees whose phone extension is greater than 4800:
WHERE LastName > "4800"
You use the <= operator to test that the value on the left is greater than or equal to the value on the right. The following example retrieves only employees whose first name begins with the letter J or higher:
WHERE FirstName >= "J"
Using the BETWEEN condition, you can to test whether a value falls into the range between two other values. The following example retrieves only employees whose phone extensions are between 4500 and 4600. Because the test is inclusive, extensions 4500 and 4600 are also retrieved.
WHERE PhoneExtension BETWEEN "4500" AND "4600"
The BETWEEN condition is actually nothing more than a convenient way of combining >= and <= conditions. You also could specify the preceding example as follows:
WHERE PhoneExtension >= "4500" AND PhoneExtension <= "4600"
The advantage of using the BETWEEN condition is that it makes the statement easier to read.
Using the EXISTS condition, you can check whether a subquery returns any rows. Subqueries are explained in Chapter 16, "Advanced SQL."
You can use the IN condition to test whether a value is part of a specific set. The set of values must be surrounded by parentheses and separated by commas. The following example retrieves employees whose last names are Black, Jones, or Smith:
WHERE LastName IN ("Black", "Jones", "Smith")
The preceding example is actually the same as the following:
WHERE LastName = "Black" OR LastName = "Jones" OR LastName = "Smith"
Using the IN condition does provide two advantages. First, it makes the statement easier to read. Second, and more important, you can use the IN condition to test whether a value is within the results of another SELECT statement. This issue is explained in Chapter 18.
A NULL value is the value of a column that is empty. The IS NULL condition tests for rows that have a NULL value; that is, the rows have no value at all in the specified column. IS NOT NULL tests for rows that have a value in a specified column.
The following example retrieves all employees whose PhoneExtension is left empty:
WHERE PhoneExtension IS NULL
To retrieve only the employees who do have a phone extension, use the following example:
WHERE PhoneExtension IS NOT NULL
Using the LIKE condition, you can test for string pattern matches using wild cards. Two wild-card types are supported. The % character means that anything from that position on is considered a match. You also can use [ ] to create a wild card for a specific character.
The following example retrieves employees whose last name begins with the letter S. To match the pattern, a last name must have an S as the first character and anything at all after it.
WHERE LastName LIKE "S%"
To retrieve employees with an S anywhere in their last names, you can use the following:
WHERE LastName LIKE "%S%"
You also can retrieve just employees whose last name ends with S, as follows:
WHERE LastName LIKE "%S"
The LIKE condition can be negated with the NOT operator. The following example retrieves only employees whose last name does not begin with S:
WHERE LastName NOT LIKE "S%"
Using the LIKE condition, you also can specify a wild card on a single character. If you want to find all employees named Smith but are not sure if the one you want spells his or her name Smyth, you can use the following:
WHERE LastName LIKE "Sm[iy]th"
This example retrieves only names that start with Sm, then have an i or y, and then a final th. With this example, as long as the first two characters are Sm and the last two are th, and as long as the middle character is i or y, the name is considered a match.
TIP: Using the powerful LIKE condition, you can retrieve data in many different ways. But everything comes with a price, and the price here is performance. Generally, LIKE conditions take far longer to process than other search conditions, especially if you use wild cards at the beginning of the pattern. As a rule, use LIKE and wild cards only when absolutely necessary.
You covered a lot of ground in this chapter. You learned about SQL and ODBC and how to create ODBC data sources. You also learned how to use Microsoft Query to create and test SQL statements. The most used SQL statement is the SELECT statement, which you use to retrieve data from a table. Using the SELECT statement, you can specify from where the data should be retrieved, exactly what data to retrieve, and how to sort the resulting output.
For more information about topics mentioned in this chapter, see the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}