Remember that our SQL statements can be translated back into normal requests. What have we asked SQL to do? "Show me the first and last names of all of the people". The SQL statement literally reads "Show me the first and last name of all of the data in the People table". Learning how to read a SQL statement and translate from an English language request to SQL statement will be very valuable later when you see items such as the following:

"Show me the top five salespeople for the southeast region in the last quarter. Let me see the results by individual state as well as a grand total.

The above statement will be a type of request that you will be able to translate by the end of this book. Throughout this chapter the request will be included with the query. This should help us in analyzing the SQL that we are adding and avoid some of the confusion about what we are trying to do with SQL.

Select only the data that is needed: queries will run faster!

Sometimes, rather than receiving the results we might expect from a query, we might encounter an error message. With a bit of close attention, and double-checking of syntax, this can usually be quickly resolved. Have a look at the following table to see how mistakes are expressed in error messages:

Common Errors

Problem	Sample Message
Incorrect field name (misspelling, does not exist, etc.)	Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'Name'.
Incorrect table name	Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'Employee'.

Defining Fields

There may be many times that we will want to rename the fields so they make more sense for the developers using our databases, or when doing calculations and string manipulation in our queries, as we shall see. Other times we may want to rename the field to make the output more readable; to provide a common field name independent of the underlying table structure.

Suppose we had a field name emp_Name_First that we want to call FirstName. This would be an example of renaming the fields within a query.

SQL has a keyword, AS, for just this purpose. Some databases (such as Oracle) do not require the use of AS and simply need a space between the field and the alias name. We can use AS to rename fields that may not be clear to the consumers or to allow us to name fields that would not have a name (for instance a field constructed to contain the results of a calculation is not given a name). Instead of referring to CustomerName and ContactName in our queries, suppose that we want to call them Customer and Contact instead.

The original query - without renaming the fields - would simply be:

SELECT CompanyName, Contact Name
FROM Customers

and would return the following results:

SELECT CompanyName AS Company,
ContactName AS Contact
FROM Customers

Note that the field names have now changed in the output window:

Aliasing - Referencing an item (field or table) with a different name.

When we use the AS operator, we instruct the query that the field from which data is to be retrieved should be given another name once the query is complete and the resultset is passed back from the query. The calling application can now use the alias rather than the name of the underlying field.

The AS operator is very useful when we output fields based on a conditional statement, or when calculating the value of a field. We'll begin looking at conditional statements in the next section, and we'll look closer at calculating fields later on in the chapter. In these cases, the returned fields have no name since the value is calculated rather than pulled directly from a named field, and you need to give the 'result' a new field name. This is completed with the AS operator as well.

Renaming fields using the AS operator can assist us in writing cleaner queries, and can help to clear up any confusion over fields that look similar or have unfriendly naming conventions. We can also alias fields that contain spaces by using brackets ([ ]) or single quotes (' ') to surround the name with the space. In Oracle we must use double quotes (" ") to contain spaces. We could alias FirstName as [First Name] or 'First Name', or "First Name" in Oracle, and our query would display the field name with a space included.

Using field and table names with spaces is possible in some databases, but it is not recommended since some database interfaces may have difficulty dealing with spaces in the names. As a general rule, use long names, but with mixed case as one word. For instance, FirstName is preferred over "First Name" as is FederalTaxRate over "Federal Tax Rate".