Databases are typically composed of many tables, each of which is designed to handle a specific type of information and assigned a unique name by which you can reference its data. For example, the table above might be named PRODUCTS_TABLE; the database that it's part of might have other tables as well - one to store registered user information, one to hold sales records, and so on. As you'll learn, one of the most powerful features of a database is its ability to combine data from a collection of interrelated tables into a single set of information.

One of the most fundamental SQL commands is SELECT, which retrieves a set of information from a table. The syntax of the basic SELECT command is as follows:

SELECT column(s) FROM table(s)

(Note that although more than one table can be named, in practice you probably wouldn't want to do this unless you also included a WHERE clause, as explained below in "Table Joins.") Here's a simple SQL query that asks the database to return all of the data stored in the products table we defined earlier:

SELECT * FROM PRODUCTS_TABLE

The "*" character means to include results from every column in the table. But we don't have to retrieve items from every column; we can also specify a subset of columns to return and say how the rows should be ordered. For example, consider the following query:

SELECT DESCRIPTION, PRICE FROM PRODUCTS_TABLE ORDER BY PRICE

This query specifies that we want the results to include only the DESCRIPTION and PRICE columns of the products table, in that order. It further specifies that we would like the rows to be ordered on the contents of the PRICE column. This query would return a results table like that shown in Figure 2.

We can further limit our search results by applying one of a number of possible conditional tests to each row in the results table. A conditional test is specified by appending a WHERE clause to the end of the SELECT command:

SELECT column(s) FROM table(s) WHERE condition

We can, for instance, select a single record from the table by constructing a WHERE clause that looks for a particular value in a particular field. In our case, because each value in the ITEM_ID column in our products table is unique, we can retrieve a particular record simply by referencing this value. For example:

SELECT * FROM PRODUCTS_TABLE WHERE ITEM_ID = 200

We can also perform comparison operations against column values. For example, we can construct a query that returns only the records for items priced higher than $10, as indicated by the value in each record's PRICE column.

SELECT * FROM PRODUCTS_TABLE WHERE PRICE > 10

We can perform a variety of other conditional tests as well, including equality and inequality, greater than or less than, and various string-matching tests. One useful test that we can perform on string values employs the LIKE clause plus the wildcard character (%) to do a wildcard search. For instance, if we wanted to find only records whose DESCRIPTION column value ends in " grid bug", we could use this query:

SELECT * FROM PRODUCTS_TABLE WHERE DESCRIPTION LIKE '% grid bug'

Many other tests, filters, and comparison operators can be included in SQL queries. One particularly interesting filter available to many systems, called SOUNDEX, allows us to search for something that sounds like another word when pronounced phonetically. This means that we can construct "fuzzy" searches where the exact search term isn't known - for example, we could search on "Jennifer" and receive matches on "Jennifer", "Genifer", and "Jenifer".

The terms of a WHERE clause aren't limited to a single test. We can combine a series of tests with Boolean AND/OR operators to further refine our search. Consider, for instance, the following query, which could be used to determine which grid bug varieties are running low on inventory:

SELECT DESCRIPTION, QTY_AVAILABLE FROM PRODUCTS_TABLE WHERE DESCRIPTION LIKE '% grid bug' 
   AND QTY_AVAILABLE < 30 ORDER BY QTY_AVAILABLE

Here we're getting only two columns from the products table and then limiting the rows returned to those that contain " grid bug". We're then further restricting the results to those records whose QTY_AVAILABLE column indicates that there are fewer than 30 units left in inventory. These restrictions are cumulative, as specified by the AND operator in the query. Finally, we're ordering the rows based on the values in the QTY_AVAILABLE column. This search would return the results table shown in Figure 6.

It's important to note that we can perform conditional tests and sorting on the values of any column in the table, even if we don't plan to include that column in our results. For example, in the above search we could have ordered the values by ITEM_ID, even though we chose not to return that column in the results.

As mentioned earlier in this article, one of the key benefits of a relational database is its ability to establish relationships among the various tables being used to store information. Thus, we can store our data in separate but interrelated tables instead of trying to cram everything into one big table. For example, we could keep a record of all purchase orders we received in a table called PO_TABLE, related by key values to our products table.

To establish relationships among tables, we need to identify the key values in each table. Key values are values in a column that can be used as a reference to a particular record. For instance, the key to records in our products table is the ITEM_ID column, which contains a unique part number for each product. In place of product descriptions, which are unwieldy and may change over time, the item ID gives us a stable, easy-to-manage way to refer to any item in our catalog. Once we have an item ID - anywhere, even inside another table - we can use it to pull out all of the other details about that product, such as its description or price.

Let's now look at how two tables in a database can be related by key values. The table shown in Figure 7, called PO_TABLE, stores data about pending purchase orders that need to be filled. Each record specifies the purchase order number, the item number, and the quantity ordered. The value in the ITEM column in this table corresponds to a value in the ITEM_ID column in our products table, thus establishing a relationship between the two tables. The ITEM column in this case is known as a foreign key, because it holds a value that corresponds to a key value in another table.

To merge information from two different tables in our table of results, we must name both tables in our SQL query. When a query combines data from more than one table, it's known as a join. A join names columns from several tables and typically uses a WHERE clause to establish some relationship among the columns. If we don't specify a WHERE clause and just specify several tables and columns in our query, the database engine will return every possible combination, which is a big mess and probably not what we were intending! Consider the following query, which simply fetches an appropriate description from the products table for each item referenced in the purchase orders table:

SELECT PO_NUMBER, DESCRIPTION, QTY_PURCHASED FROM PO_TABLE, PRODUCTS_TABLE 
   WHERE ITEM = ITEM_ID

The logic behind the query goes something like this: We're requesting information that must be obtained from both the purchase orders table and the products table. We want the PO_NUMBER and QTY_PURCHASED columns from the purchase orders table and the DESCRIPTION column from the products table. However, what we want to end up with is the description associated with each item listed in our purchase order table's ITEM field. We use a WHERE clause to select only that one particular intersection between the two tables. The results table that we get back from such a query looks like the one in Figure 8.

If your query includes tables that have columns with the exact same names, the database won't be able to determine which table's column you really want. To let it know, you must prefix the ambiguous column name with the name of its table (followed by a period). For example, if both our tables had a DATE column, we would refer to them as PRODUCTS_TABLE.DATE and PO_TABLE.DATE.

SELECT PO_NUMBER FROM PO_TABLE, PRODUCTS_TABLE WHERE PO_TABLE.ITEM = PRODUCTS_TABLE.ITEM_ID 
   AND QTY_AVAILABLE < QTY_PURCHASED

SQL can do more than simply retrieve information already stored in the database, of course. We can also use SQL commands to create new records or update existing ones. The syntax is a little different from the SELECT queries we've been studying but still pretty simple:

INSERT INTO table (column(s)) VALUES (value(s))
DELETE FROM table WHERE condition
UPDATE table SET column=value, column=value, ...

INSERT, as you might expect, enables us to insert data into a table by adding rows. The INSERT command requires a list of columns to insert and a corresponding list of values. The first value listed is assigned to the first column listed, the second to the second, and so on. We can leave out the columns list, in which case the values are mapped to the table's columns in the order they were specified when we defined the table's schema (the "natural order" of the columns).

INSERT INTO PRODUCTS_TABLE (ITEM_ID, DESCRIPTION) VALUES (600, 'Tan grid bug')

Figure 9 shows our table with this new row added. We didn't specify values for the PRICE and QTY_AVAILABLE columns in our INSERT command, so these columns are still undefined.

We can add the missing values to our new row with an UPDATE command, targeted at the record for the tan grid bug, which has an ITEM_ID value of 600:

UPDATE PRODUCTS_TABLE SET PRICE=19.95, QTY_AVAILABLE=100 WHERE ITEM_ID = 600

The WHERE clause in the UPDATE command restricts the update to affect only the single row we want. Figure 10 shows what our table would look like after this query was executed.

It's also possible to set values of a column in multiple rows at the same time. If we had a terrible fire at the warehouse that destroyed our entire inventory, we could issue this command, which would set the QTY_AVAILABLE column for all our products to a value of 0:

UPDATE PRODUCTS_TABLE SET QTY_AVAILABLE=0;

The DELETE command is used to remove rows from a table that match a specified condition. Its syntax is similar to that of the SELECT command, and it works pretty much the same way. For example, if we were to discontinue our line of red grid bugs, we would issue the following command to remove its entry from the products table:

DELETE FROM PRODUCTS_TABLE WHERE ID = 300

After this command was executed, all rows fitting the criteria specified in the WHERE clause would be removed from the table.