SELECT Statements

The SELECTstatement is an important one. You probably use this statement more than any other SQL statement. As you might have guessed, the SELECTstatement is used to retrieve data from a table or group of tables. The syntax for a SELECTstatement is far too compli- cated to show here. Instead, here’s a demonstration, using the Music database, of some ways that you can use a SELECTstatement. 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 WHEREclause. To demonstrate this, execute the following INSERTstatements:

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_memberstable:

SELECT band_member_fname, band_member_lname FROM t_band_members WHERE band_id = 1

You can also order the rows returned using an ORDERclause. The ORDERclause 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 SELECTstatement will first be ordered by band_member_lnameand 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 SELECTstatement, the column names are generally included. Sometimes that’s not appropriate. Luckily, SQL allows you to get around this problem. You can use an ASclause to rename the columns returned from the SELECTstatement 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 SELECTstatement 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_bandsand 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 SELECTstatement. 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 SELECTstatement 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 WHEREclauses in the two previous SELECTstatements. The WHEREclause links the tables on the band_idin each table. So all the rows in each table that have the same band_idvalue are displayed. Try taking out the WHEREclause and executing the SQL statement.

R

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 SELECTstatements are the most commonly used SQL statements.

Q

Y

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 SELECTcommand? (See “SELECT Statements.”)

Session Checklist