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

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 from the

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 in the translation statement. If it’s a table name, use it as a replacement for . 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 fullfledged 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

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.

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.”

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

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.

I come accross this informative topic in SQL QUERIES FOR MERE MORTALS II Edition by John L. Viescas  and Michael J. Hernandez.

Created with Artisteer