The good news about this statement is that the WHERE clause works the same here as it does with the DELETE statement. It simply more clearly identifies the rows that need to be updated.

Here’s a sample UPDATE statement:

UPDATE t_bands SET band_title = ‘Hootie and The Blowfish’ WHERE band_id = 1

This statement says, “change the value in the band_title field to “Hootie and The Blowfish” in all rows where the value in the band_id field is 1.” It’s that simple.

SELECT Statements

The SELECT statement is an important one. You probably use this statement more than any other SQL statement. As you might have guessed, the SELECT statement is used to retrieve data from a table or group of tables. The syntax for a SELECT statement is far too complicated to show here. Instead, here’s a demonstration, using the Music database, of some ways that you can use a SELECT statement. Now get started.

If you want to return all rows from a single table, let’s say t_bands, you use the following command:

SELECT * FROM t_bands

In this statement, the * returns all columns. So what if you only want to return just a few rows based on certain criteria? Well, you would use a WHERE clause. To demonstrate this, execute the following INSERT statements:

INSERT INTO t_band_members (band_member_fname, band_member_lname, band_id) VALUES

(‘Darius’,’Rucker’,1)

INSERT INTO t_band_members (band_member_fname, band_member_lname, band_id) VALUES

(‘Mark’,’Bryan’,1)

INSERT INTO t_band_members (band_member_fname, band_member_lname, band_id) VALUES

(‘Dean’,’Felber’,1)

INSERT INTO t_band_members (band_member_fname, band_member_lname, band_id) VALUES

(‘Jim’,’Sonefeld’,1)

Now you can execute a command to return all the members of Hootie & The Blowfish as follows:

SELECT * FROM t_band_members WHERE band_id = 1

Generally, using * is not good practice because it returns all the columns in a table, which is not generally the desired result. For performance reasons it is a good idea to only request the columns you need. So what if you don’t want to return all the columns in a row? In that

case, you would simply explicitly define which columns to return. The following statement returns only two columns from the t_band_members table:

SELECT band_member_fname, band_member_lname FROM t_band_members WHERE band_id = 1

You can also order the rows returned using an ORDER clause. The ORDER clause allows you to specify the columns you want to use to order the rows that are returned by a SELECT statement.

SELECT band_member_fname, band_member_lname FROM t_band_members WHERE band_id = 1

ORDER BY band_member_lname, band_member_fname

In the previous statement, the results of the SELECT statement will first be ordered by band_member_lname and then by band_member_fname. So if you had two band members with the same last name, they would then be ordered by first name. Although, based on the data we have inserted thus far in the session, sorting by last and first name will yield the same results as sorting only by last name since all band members have different last names.

When you execute a SELECT statement, the column names are generally included. Sometimes that’s not appropriate. Luckily, SQL allows you to get around this problem. You can use an AS clause to rename the columns returned from the SELECT statement as shown in the following example:

SELECT band_member_fname AS “Last Name”, band_member_lname AS “First Name” FROM

t_band_members WHERE band_id = 1 ORDER BY band_member_lname, band_member_fname

Notice that the derived column names are enclosed in quotes. This is because the derived names contain spaces. If the derived names do not contain spaces — for example, “LName” — you do not need to use quotes.

OK, we’re getting close to the end. The last type of SELECT statement involves returning data from more than one table. There are many ways to do this. Here’s a simple example:

SELECT band_member_fname AS “Last Name”, band_member_lname AS “First Name”,

band_title AS “Band Title” FROM t_band_members, t_bands WHERE t_band_members.band_id

= t_bands.band_id ORDER BY band_title, _member_lname, band_member_fname

This statement returns data from two tables, t_bands and t_band_members. The FROM clause lists the tables from which you want to return data. With the statement, you are returning three columns from the two tables. The columns you want to return are listed after the SELECT statement. If, by chance, you have two columns with the same name in tables from which you are selecting, you need to preface the column names with the table name. For example, the following code is a rewrite of the previous SELECT statement that explicitly declares from which table you are selecting the columns:

SELECT t_band_members.band_member_fname AS “Last Name”, t_band_members.band_member_lname AS “First Name”, t_bands.band_title AS “Band Title”

FROM t_band_members, t_bands WHERE t_band_members.band_id = t_bands.band_id ORDER BY

t_bands.band_title, t_band_members.band_member_lname, t_band_members.band_member_fname

So, how are the tables joined when selecting data from two or more tables? Look at the WHERE clauses in the two previous SELECT statements. The WHERE clause links the tables on the band_id in each table. So all the rows in each table that have the same band_id value are displayed. Try taking out the WHERE clause and executing the SQL statement.

REVIEW

SQL is the language used to retrieve and manipulate data in a database. SQL is effectively a language composed of statements and clauses used in concert to create, retrieve, update, and delete data. From our experience, the INSERT, DELETE, UPDATE, and SELECT statements are the most commonly used SQL statements.

QUIZ YOURSELF

1.What is SQL? (See session introduction.)

2.What SQL statement is used to retrieve data from a database table? (See “SELECT Statements.”)

3.How do you return data from more than one table with a SELECT command? (See “SELECT Statements.”)