There are four basic types of queries:
Queries that read from the database
Queries that write to the database
Queries that update existing information in the database
Queries that delete information from the database
The following sections examine the important details of each type of query, and apply them to the GuestBook table we created in the database tutorial.
SELECT: Reading from the Database
You use a SQL SELECT queryoften simply called a SELECT query or just a SELECTto read information from one or more tables in your database. This is the general syntax of the simplest form of this query:
SELECT fieldname(s) FROM table(s)
For example, if you want to view all the information in the GuestBook table, the SELECT query would look like this:
SELECT email, comment, age, dateposted, hideEmail, name, gender FROM GuestBook
Ready to get started? Follow these steps:
Enter the above query into the query window as shown in Figure 9.
Important: You can enter the entire query on one line in the SQL query window or, for readability purposes, add extra spaces and line breaks.
Figure 9 Entering a SELECT query into the query window.
The database doesn't actually execute the query until you click the Run button, which is the button in the toolbar with the big red exclamation mark in the middle (see Figure 10).
Figure 10 The Run button starts the query process.
Click the Run button to execute the query. The result appears in the query window (see Figure 11).
Figure 11 Results of the SELECT query.
To get back to the query window, click the down arrow on the View button and select SQL View from the drop-down list (see Figure 12).
Figure 12 To get back to the query window, use the View button.
The records displayed are indeed the ones we manually entered in the last tutorial, so our query works! Let's go back to the query window and experiment with more sophisticated queries.
Displaying All Fields with a Wildcard
It can get tedious typing all the fieldnames for your SELECT query. SQL allows you to use an asterisk (*), also known as a wildcard, to indicate that you want all the fields. So, if you want to view all the records in the GuestBook table, here's the equivalent and now much shorter SELECT query (see Figure 13):
SELECT * FROM GuestBook
Figure 13 Using a wildcard to list all fields.
Figure 14 Query results for SELECT * FROM GuestBook.
It's also the same information you would get by "manually" opening the GuestBook table (clicking the GuestBook table icon in the starting window). So you're probably wondering what the big deal is with the SELECT query. The answer will become apparent in the next couple of examples, in which we use SELECT to view different parts of our table.
Viewing Partial Fields
Suppose you aren't interested in viewing all the information in a record; you simply want to look at one or two fields. In that case, a query that returns everything wastes both your time and your computer's memory. Thankfully, SQL allows you to restrict the fields that the database returns. To do so, in your SELECT query, simply list only the fields you want to see. For example, if you want to view just the names and comments for all the GuestBook entries, use this query (see Figure 15):
SELECT name, comment FROM GuestBook
Figure 15 Restricting the SELECT query to a couple of fields.
Figure 16 Results of the restricted SELECT query.
Restricting the fields that are returned (displayed) is just one useful action you can perform with the SELECT query. However, the real power of the SELECT query comes from the flexibility it provides in terms of accessing information according to different criteria.
Specifying Numeric Conditions
Imagine you have a database full of customer information and you want to know how many of your customers over the age of 30 bought your products. How would you do this? There are many situations in which you only want to look at database information that fits a certain set of conditions (called criteria). By specifying one or more conditions in your SELECT query, you can narrow the information the database returns to just the records that you're interested in. You specify these conditions in a WHERE clause at the end of the SELECT query:
SELECT fieldname(s) FROM table(s) WHERE condition(s)
For example, suppose you want to find everyone in your GuestBook table who is at least 30 years of age. Here's the SELECT query (see Figure 17):
SELECT * FROM GuestBook WHERE age>=30
Figure 17 Entering a simple numeric condition for the SELECT query.
When you run the query by clicking the Run button, the query window displays the results shown in Figure 18.
Figure 18 Results of a numeric conditional query.
The query returns only Fred's and Barney's records in this example, because they're the only people listed in the table whose ages are 30 or over (Wilma and Betty are in their 20s).
Besides specifying a single numeric condition, you can specify multiple conditions and "connect" them using and and or. For example, suppose you want to see the records of anyone who signed your guest book who was between 20 and 30 years of age. The query would look like this (see Figure 19):
SELECT * FROM GuestBook WHERE age>=20 and age<=30
Figure 19 Specifying two numeric conditions in the WHERE clause of the SELECT query.
Figure 20 shows the results. The query returns Betty's and Wilma's records, because they're both between the ages of 20 and 30.
Figure 20 Results of specifying two numeric conditions in the WHERE clause.
Specifying String Conditions
Suppose you want to find specific individuals in your database whose names match a certain criterion, such as anyone who signed your guest book and whose last name is Smith. Or suppose you want to find obscene comments in your guest book so that you can remove them. How would you find such records? Well, just as we specified numeric conditions in the SELECT query to restrict the records the database returned, we can specify textual or string conditions as well. A key difference is that you must enclose string values between single quotes ('). Forgetting to put single quotes around string values is the most common source of errors for beginning SQL writers.
As with numeric conditions, you attach a string condition to the end of your basic SELECT query via the WHERE clause. To see an example of adding a string condition to a SELECT query, let's write a query to see whether Fred Flintstone signed our guest book. Here's the query:
SELECT * FROM GuestBook WHERE name='Fred Flintstone'
Notice the single quotes around the string Fred Flintstone (see Figure 21).
Figure 21 Specifying a string condition in the WHERE clause of the SELECT query.
Figure 22 shows the results. Since there's only one Fred Flintstone in the GuestBook table, the database returns just one record.
Figure 22 Results of specifying a string condition in the WHERE clause.
Finding a specific name isn't usually very useful. But by employing the LIKE operator, you can find names, words, and strings that fit a given pattern. We examine this topic next.
Combining String Conditions with LIKE
Suppose you want to find everyone in the database with a specific last name, such as Flintstone. Would you try the following query?
SELECT * FROM GuestBook WHERE name='Flintstone'
It looks like it should work, doesn't it? But this query is actually looking for anyone in the database whose name consists of one word: Flintstone. Don't believe it? Try running the query.
You need to be able to specify that you don't care what's before the name Flintstone; you just want the name to end in Flintstone. This goal requires that you use the LIKE operator in place of the equals (=) operator. Here's our (working) query (see Figure 23):
SELECT * FROM GuestBook WHERE name LIKE 'Flintstone'
Figure 23 Using LIKE in the string condition.
Figure 24 shows the results. Both Fred and Wilmaand only Fred and Wilma, so far in our databasehave the last name Flintstone.
Figure 24 Results of using LIKE in the string condition.
Combining Field Restrictions with String Conditions
You can combine field restrictions with wildcards to perform any number of bizarre searches. For example, suppose you want to find the name, email address, and age of anyone in your guest book whose name contains the letter a. Here's the query; notice the two wildcardsan asterisk before the a and one after it (see Figure 25):
SELECT name,email,age FROM GuestBook WHERE name LIKE '*a*'
Figure 25 Combining wildcards and restricted fields.
Figure 26 shows the results. The database correctly returns Wilma's and Barney's records because each includes the letter a in the name.
Figure 26 Finding anyone whose name contains a.
This may seem like a useless query, but it can be modified easily to find entries in the table with obscene words. Simply substitute your favorite obscenity for the letter a, and specify the comment field in place of the name field.
Combining Field Restrictions with Numeric and String Conditions
You can combine string and numeric conditions by using and or or. Our final example in this section combines field restrictions with both numeric and string conditions. Suppose you want to find the name, e-mail address, and age of anyone in your guest book who is between the ages of 20 and 30 and whose name contains the letter a. Here's the query (see Figure 27):
SELECT name,email,age FROM GuestBook WHERE name LIKE '*a*' and age>=20 and age<=30
Figure 27 Combining field restrictions with string and numeric conditions.
Notice how the string condition (name like '*a*') and the numeric conditions (age>=20, age<=30) are combined using and. Figure 28 shows the results.
Figure 28 Results of combining field restrictions with string and numeric conditions.
Of the entries in the database, only Wilma has the letter a in her name and is 2030 years old; thus the query returned the correct results.
We've covered all the basic SQL SELECT queries you need to know in order to read information out of a table in your database. Now, SELECT queries can become quite complex, especially if you search over more than one table, or when you start embedding queries. However, you need to understand and become comfortable using these basic SELECT queries before you attempt the more complex ones. So practice experimenting with these SELECT queries and making up your own.
Next we look at using SQL to write information into a database table.
INSERT: Writing into the Database
As you've probably surmised, if there's a SQL command to read information out of a table, there's a SQL command to write information into a table. That command is INSERT. The syntax of the basic INSERT command is as follows:
INSERT INTO table(fieldname(s)) VALUES (values(s))
This command is best illustrated by an example using the GuestBook table. Suppose you want to insert a 30-year old male named Nick Flor, whose email address is email@example.com, and whose comment is I love guitars. The INSERT query would look like this (see Figure 29):
INSERT INTO GuestBook(name, email, age, gender, comment, hideEmail) [ccc]VALUES ('Nick Flor', 'firstname.lastname@example.org', 30, 'male', 'I love guitars', Yes)
Figure 29 INSERT query.
There are two important details you should note:
The order in which you specify the fields in the INSERT query doesn't have match the order of the fields in the GuestBook table. You can specify the fields in whatever order you want, but you must list the values in the same order as the fields. If your INSERT query specifies name followed by email address, the VALUES list must specify the actual name to insert, followed by the email address. Otherwise you'll get incorrect data in your tablea name where an email address ought to be and vice versaor, worst case, your query won't run at all.
You must enclose entries for text and memo field values in single quotes ('). The age field takes a numeric value, so it doesn't have to be enclosed in quotes. Similarly, the hideEmail field takes a Boolean value (Yes/No or True/False), which you don't have to surround with single quotes.
The dialog box shown in Figure 30 pops up, warning you that you're about to append information to your table. Click Yes to complete the insert process.
Figure 30 Append warning.
Unlike the SELECT query, the INSERT query doesn't return any results; Access just returns you to the query window (see Figure 31). Notice that the window's title bar changes from reading Select Query to Append Query.
Figure 31 The SQL query window after running the INSERT query.
To verify that the INSERT query worked, open the GuestBook table and look for the new entry. In case you forgot how, follow these steps:
Activate the opening window. It's usually behind the query window, and you can activate it by simply clicking the title bar or the edge of the window (see Figure 32).
Figure 32 Opening the GuestBook table to verify that the INSERT query worked.
Click the Tables button in the Objects list on the left side of the window.
Double-click the GuestBook table's icon to open the table (see Figure 33). Notice that the INSERT query did indeed insert the new data into the GuestBook table.
Figure 33 The GuestBook table contains the new entry.
Exercise: INSERT Query
Now you try using the SQL INSERT query to add information to the database. For this example, add a 29-year-old female named Nancy Flor to the GuestBook table, with the email address email@example.com (don't hide the address), and the comment I love pianos (see Figure 34):
Figure 34 Exercise: INSERT query.
If you wrote the INSERT query correctly, when you open the GuestBook table you should see the entry shown in Figure 35.
Figure 35 Contents of the GuestBook table after completing the exercise.
INSERT queries are not as difficult to write as SELECT queries. At worst, you have to do a lot of typing if your tables have many fields defined.
Do you have to specify all fields in an INSERT query? Usually not. We examine a partial INSERT procedure in the next section.
Exploiting Default Values: Inserting a Partial Record into the Database
When we created the GuestBook table in the last tutorial, we set it up so that all the fields except email and comment had default values. Thus, it's okay if in our INSERT query we leave out a field that has a default valuethe database automatically inserts the default value we defined. Did you notice that none of the INSERT queries above mentioned the dateposted field? Yet the GuestBook table included a value in that field for each of the entries we just added. (Don't believe me? Hop back to Figures 33 and 35 and take a look.)
To show how these default values are set, suppose we write an INSERT query that just inserts an email address and comment into the database (see Figure 36):
INSERT INTO GuestBook(email, comment) VALUES ('firstname.lastname@example.org', 'I love biscuits')
Figure 36 Partial INSERT query.
Now open the GuestBook table and look for the new entry (see Figure 37). The database automatically inserted default values for fields that we didn't define in the query. Specifically, the database inserted this info:
0 for the age field
The current time for the dateposted field
Yes for the hideEmail field
Declined to Specify for the name field
female for the gender field
Figure 37 Default values were supplied by the database.
DELETE and UPDATE Queries
In addition to the INSERT and SELECT queries for storing information in and retrieving information from a table, there are queries for removing and updating information. It's beyond the scope of this tutorial to cover these queries in detail, but we'll show you at least the general syntax of these commands in the following sections.
Anything you can SELECT you can DELETE. The DELETE query has almost the same syntax as the SELECT query, except that the word SELECT is replaced by DELETE and you don't specify any fieldsyou can only delete the entire record, not fields within a record. This is the general syntax for the DELETE query:
DELETE FROM table WHERE condition(s)
For example, suppose you want to delete the records of all guest book users who from 2030 whose names contain the letter a. Here's the query:
DELETE FROM GuestBook WHERE name LIKE '*a*' and age>=20 and age<=30
This query would delete Wilma Flintstone's record from our GuestBook table, because this record fits the conditions specified in the WHERE clause (refer to Figure 28). Compare the DELETE query above with the corresponding SELECT query that selected Wilma's record in Figure 28. We've boldfaced the differences below:
SELECT name,email,age FROM GuestBook WHERE name LIKE '*a*' and age>=20 and age<=30
If you're comfortable writing the WHERE conditions for SELECT queries, you should have no problems writing DELETE queries.
Be very careful with DELETE queries. There's no "undo" operation as in a word processor. Once you execute a DELETE query, the only way you can recover the deleted information is via a backup copy of the database.
There are many situations where you want to change a couple of fields in a record, usually because the information was entered incorrectly (such as a spelling error) or changed (such as a raise in salary). To change one or more fields in a record, you use the UPDATE query, which has this general syntax:
UPDATE table SET field1=value1, field2=value2, ..., fieldN=valueN WHERE condition
For example, if you want to set Fred Flintstone's age in the database to 40, you would use the following query:
UPDATE GuestBook SET age=40 WHERE name='Fred Flintstone'
The WHERE clause usually refers to a single record. If your WHERE clause specifies more than one record, all those records will have their field values changed. For example, the following UPDATE query refers to more than one record (refer to Figure 26):
UPDATE GuestBook set age=7,name='Dino' WHERE name LIKE '*a*'
Specifically, the records for both Wilma and Barney fit the conditions specified in the WHERE clause. Thus, the database will change both Wilma's and Barney's names to Dino and set both their ages to 7. The "tricky" part to writing an UPDATE query is getting the WHERE clause correct. However, as with the DELETE query, if you're comfortable writing WHERE clauses for your SELECT queries, you should have little problem generalizing your knowledge to UPDATE queries.