Manage Learn to apply best practices and optimize your operations.

Translating information requests into SQL SELECT statements

Learn how to translate information requests into suitable SQL SELECT statements in this excerpt from "SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, Second Edition." You'll learn techniques to create more complicated SQL SELECT statements and how to retrieve multiple columns in a single statement.

Translating Your Request into SQL
When you request information from the database, it's usually in the form of a question or a statement that implies a question. For example, you might formulate statements such as these:

"Which cities do our customers live in?"
"Show me a current list of our employees and their phone numbers."
"What kind of classes do we currently offer?"
"Give me the names of the folks on our staff and the dates they were hired."

After you know what you want to ask, you can translate your request into a more formal statement. You compose the translation using this form:

Select <item> from the <source>

Start by looking at your request and replacing words or phrases such as "list," "show me," "what," "which," and "who" with the word "Select." Next, identify any nouns in your request, and determine whether a given noun represents an item you want to see or the name of a table in which an item might be stored. If it's an item, use it as a replacement for <item> in the translation statement. If it's a table name, use it as a replacement for <source>. If you translate the first question listed earlier, your statement looks something like this:

Select city from the customers table

After you define your translation statement, you need to turn it into a full-fledged SELECT statement using the SQL syntax shown in Figure 4–4. The first step, however, is to clean up your translation statement. You do so by crossing out any word that is not a noun representing the name of a column or table or that is not a word specifically used in the SQL syntax. Here's how the translation statement looks during the process of cleaning it up:

Select city from the customers table

Remove the words you've crossed out, and you now have a complete SELECT statement.

SELECT City FROM Customers


Figure 4–4 The syntax of a simple SELECT statement

You can use the three-step technique we just presented on any request you send to your database. In fact, we use this technique throughout most of the book, and we encourage you to use it while you're beginning to learn how to build these statements. However, you'll eventually merge these steps into one seamless operation as you get more accustomed to writing SELECT statements.

You are reading part 2 from "How to use the SELECT statement in SQL," excerpted from SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (2nd Edition), by John L. Viescas and Michael J. Hernandez, copyright 2007, printed with permission from Addison-Wesley Professional.

Remember that you'll work mostly with columns and tables when you're beginning to learn how to use SQL. The syntax diagram in Figure 4–4 reflects this fact by using column_name in the SELECT clause and table_name in the FROM clause. In the next chapter, you'll learn how to use other terms in these clauses to create more complex SELECT statements.

You probably noticed that the request we used in the previous example is relatively straightforward. It was easy to both redefine it as a translation statement and identify the column names that were present in the statement. But what if a request is not as straightforward and easy to translate, and it's difficult to identify the columns you need for the SELECT clause? The easiest course of action is to refine your request and make it more specific. For example, you can refine a request such as "Show me the information on our clients" by recasting it more clearly as "List the name, city, and phone number for each of our clients." If refining the request doesn't solve the problem, you still have two other options. Your first alternative is to determine whether the table specified in the FROM clause of the SELECT statement contains any column names that can help to clarify the request and thus make it easier to define a translation statement. Your second alternative is to examine the request more closely and determine whether a word or phrase it contains implies any column names. Whether you can use either or both alternatives depends on the request itself. Just remember that you do have techniques available when you find it difficult to define a translation statement. Let's look at an example of each technique and how you can apply it in a typical scenario.

To illustrate the first technique, let's say you're trying to translate the following request.

"I need the names and addresses of all our employees."

This looks like a straightforward request on the surface. But if you review this request again, you'll find one minor problem: Although you can determine the table you need (Employees) for the translation statement, there's nothing within the request that helps you identify the specific columns you need for the SELECT clause. Although the words "names" and "addresses" appear in the request, they are terms that are general in nature. You can solve this problem by reviewing the table you identified in the request and determining whether it contains any columns you can substitute for these terms. If so, use the column names in the translation statement. (You can opt to use generic versions of the column names in the translation statement if it will help you visualize the statement more clearly. However, you will need to use the actual column names in the SQL syntax.) In this case, look for column names in the Employees table shown in Figure 4–5 that could be used in place of the words "names" and "addresses."

Employees
EmployeeID         PK
EmpFirstName
EmpLastName
EmpStreetAddress
EmpCity
EmpState
EmpZipCode
EmpPhoneNumber
EmpAreaCode

Figure 4–5 The structure of the Employees table

To fully satisfy the need for "names" and "addresses," you will indeed use six columns from this table. EmpFirstName and EmpLastName will both replace "names" in the request, and EmpStreetAddress, EmpCity, EmpState, and EmpZipCode will replace "addresses." Now, apply the entire translation process to the request, which we've repeated for your convenience. (We'll use generic forms of the column names for the translation statement and the actual column names in the SQL syntax.)

"I need the names and addresses of all our employees."
 

Translation Select first name, last name, street address, city, state, and ZIP
Code from the employees table
Clean Up Select first name, last name, street address, city, state, and ZIP Code from the employees table
SQL SELECT EmpFirstName, EmpLastName, EmpStreetAddress,
EmpCity, EmpState, EmpZipCode
FROM Employees
 
Note: This example clearly illustrates how to use multiple columns in a SELECT clause. We'll discuss this technique in more detail later in this section.

The next example illustrates the second technique, which involves searching for implied columns within the request. Let's assume you're trying to put the following request through the translation process.

"What kind of classes do we currently offer?"

At first glance, it might seem difficult to define a translation statement from this request. The request doesn't indicate any column names, and without even one item to select, you can't create a complete translation statement. What do you do now? Take a closer look at each word in the request and determine whether there is one that implies a column name within the Classes table. Before you read any further, take a moment to study the request again. Can you find such a word?

In this case, the word "kind" might imply a column name in the Classes table. Why? Because a kind of class can also be thought of as a category of class. If there is a category column in the Classes table, then you have the column name you need to complete the translation statement and, by inference, the SELECT statement. Let's assume that there is a category column in the Classes table and take the request through the three-step process once again.

"What kind of classes do we currently offer?"

Translation Select category from the classes table
Clean Up Select category from the classes table
SQL SELECT Category
FROM Classes
 

As the example shows, this technique involves using synonyms as replacements for certain words or phrases within the request. If you identify a word or phrase that might imply a column name, try to replace it with a synonym. The synonym you choose might indeed identify a column that exists in the database. However, if the first synonym that comes to mind doesn't work, try another. Continue this process until you either find a synonym that does identify a column name or until you're satisfied that neither the original word nor any of its synonyms represent a column name.

Note: Unless we indicate otherwise, all column names and table names used in the SQL syntax portion of the examples are drawn from the sample databases in Appendix B, Schema for the Sample Databases. This convention applies to all examples for the remainder of the book.

Expanding the Field of Vision
You can retrieve multiple columns within a SELECT statement as easily as you can retrieve a single column. List the names of the columns you want to use in the SELECT clause, and separate each name in the list with a comma. In the syntax diagram shown in Figure 4–6, the option to use more than one column is indicated by a line that flows from right to left beneath column_name. The comma in the middle of the line denotes that you must insert a comma before the next column name you want to use in the SELECT clause.


Figure 4–6 The syntax for using multiple columns in a SELECT clause

The option to use multiple columns in the SELECT statement provides you with the means to answer questions such as these.

"Show me a current list of our employees and their phone numbers."

Translation Select the last name, first name, and phone number of all our employees from the employees table
Clean Up Select the last name, first name, and phone number of all our employees from the employees table
SQL SELECT EmpLastName, EmpFirstName, EmpPhoneNumber
FROM Employees
 

"What are the names and prices of the products we carry, and under what category is each item listed?"

Translation Select the name, price, and category of every product from the products table
Clean Up Select the name, price, and category of every product from the products table
SQL SELECT ProductName, RetailPrice, Category
FROM Products
 

You gain the advantage of seeing a wider spectrum of information when you work with several columns in a SELECT statement. Incidentally, the sequence of the columns in your SELECT clause is not important—you can list the columns in any order you want. This gives you the flexibility to view the same information in a variety of ways.

For example, let's say you're working with the table shown in Figure 4–7, and you're asked to pose the following request to the database.

"Show me a list of subjects, the category each belongs to, and the code we use in our catalog. But I'd like to see the name first, followed by the category and then the code."

Subjects
SubjectID          PK
CategoryID        FK
SubjectCode
SubjectName
SubjectDescription

Figure 4–7 The structure of the Subjects table

You can still transform this request into an appropriate SELECT statement, even though the person making the request wants to see the columns in a specific order. Just list the column names in the order specified when you define the translation statement. Here's how the process looks when you transform this request into a SELECT statement.

Translation Select the subject name, category ID, and subject code from the subjects table
Clean Up Select the subject name, category ID, and subject code from the subjects table
SQL SELECT SubjectName, CategoryID, SubjectCode
FROM Subjects
 

Using a Shortcut to Request All Columns
There is no limit to the number of columns you can specify in the SELECT clause—in fact, you can list all the columns from the source table. The following example shows the SELECT statement you use to specify all the columns from the Subjects table in Figure 4–7.

SQL SELECT SubjectID, CategoryID, SubjectCode,
SubjectName, SubjectDescription
FROM Subjects

When you specify all the columns from the source table, you'll have a lot of typing to do if the table contains a number of columns! Fortunately, the SQL Standard specifies the asterisk as a shortcut you can use to shorten the statement considerably. The syntax diagram in Figure 4–8 shows that you can use the asterisk as an alternative to a list of columns in the SELECT clause.


Figure 4–8 The syntax for the asterisk shortcut

Place the asterisk immediately after the SELECT clause when you want to specify all the columns from the source table in the FROM clause. For example, here's how the preceding SELECT statement looks when you use the shortcut.

SQL SELECT *
FROM Subjects

You'll certainly do less typing with this statement! However, one issue arises when you create SELECT statements in this manner: The asterisk represents all of the columns that currently exist in the source table, and adding or deleting columns affects what you see in the result set of the SELECT statement. (Oddly enough, the SQL Standard states that adding or deleting columns should not affect your result set.) This issue is important only if you must see the same columns in the result set consistently. Your database system will not warn you if columns have been deleted from the source table when you use the asterisk in the SELECT clause, but it will raise a warning when it can't find a column you explicitly specified. Although this does not pose a real problem for our purposes, it will be an important issue when you delve into the world of programming with SQL. Our rule of thumb is this: Use the asterisk only when you need to create a "quick and dirty" query to see all the information in a given table. Otherwise, specify all the columns you need for the query. In the end, the query will return exactly the information you need and will be more self-documenting.

The examples we've seen so far are based on simple requests that require columns from only one table. You'll learn how to work with more complex requests that require columns from several tables in Part III.


TABLE OF CONTENTS


Exchange 2007 Storage SystemsThis chapter excerpt from SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (2nd Edition) by John L. Viescas and Michael J. Hernandez, is printed with permission from Addison-Wesley Professional, Copyright 2007.

Click here for the chapter download or purchase the book here.

This was last published in July 2008

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close