How to code the SELECT clause
Figure 3-3 presents an expanded syntax for the SELECT clause. The keywords shown in the first line allow you to restrict the rows that are returned by a query. Youíll learn how to code them in a moment. But first, youíll learn various techniques for identifying which columns are to be included in a result set.
How to code column specifications
Figure 3-3 summarizes the techniques you can use to code column specifications. You saw how to use some of these techniques in the previous figure. For example, you can code an asterisk in the SELECT clause to retrieve all of the columns in the base table, and you can code a list of column names separated by commas. Note that when you code an asterisk, the columns are returned in the order that they occur in the base table.
You can also code a column specification as an expression. For example, you can use an arithmetic expression to perform a calculation on two or more columns in the base table, and you can use a string expression to combine two or more string values. An expression can also include one or more functions. Youíll learn more about each of these techniques in the topics that follow.
The expanded syntax of the SELECT clause
Five ways to code column specifications
Column specifications that use base table values
The * is used to retrieve all columns
Column names are used to retrieve specific columns
Column specifications that use calculated values
An arithmetic expression is used to calculate balance_due
A string expression is used to derive full_name
--------------------------------------------Figure 3-3 How to code column specifications
By default, a column in a result set is given the same name as the column in the base table. You can specify a different name, however, if you need to. You can also name a column that contains a calculated value. When you do that, the new column name is called a column alias. Figure 3-4 presents two techniques for creating column aliases.
The first technique is to code the column specification followed by the AS keyword and the column alias. This is the coding technique specified by the American National Standards Institute (ANSI, pronounced Ďann-seeí), and itís illustrated by the first example in this figure.
The second technique is to code the column specification followed by a space and the column alias. This coding technique is illustrated by the second example. Whenever possible, though, you should use the first technique since the AS keyword makes it easier to identify the alias for the column, which makes your SQL statement easier to read and maintain.
When you code an alias, you must enclose the alias in double quotes if the alias contains a space or is a keyword thatís reserved by Oracle. In this figure, the first two examples specify an alias for the invoice_number column that uses two words with a space between them.
In addition, these two examples specify an alias for the invoice_date column that uses a keyword thatís reserved by Oracle: the DATE keyword. If you donít enclose this keyword in double quotes, you will get an error when you attempt to execute either of these SQL statements. When you enter a statement into SQL Developer, it boldfaces keywords that are reserved by Oracle. This makes it easy to identify Oracle keywords when youíre writing SQL statements.
When you enclose an alias in double quotes, the result set uses the capitalization specified by the alias. Otherwise, the result set capitalizes all letters in the column name. In this figure, for instance, the first two columns in the first result set use the capitalization specified by the aliases. However, since no alias is specified for the third column, all letters in the name of this column are capitalized.
When you code a column that contains a calculated value, itís a good practice to specify an alias for the calculated column. If you donít, Oracle will assign the entire calculation as the name, which can be unwieldy, as shown in the third example. As a result, you usually assign a name to any column thatís calculated from other columns in the base table.
Two SELECT statements that name the columns in the result set
A SELECT statement that omits the AS keyword
The result set for both SELECT statements
A SELECT statement that doesnít provide a name for a calculated column
--------------------------------------------Figure 3-4 How to name the columns in a result set
Please check back for the next part of this series.
blog comments powered by Disqus