Home arrow Oracle arrow Coding the SELECT Statement

Coding the SELECT Statement

In this third part of a nine-part article series on working with the SELECT statement, you'll learn how to code the SELECT clause, how to name the column in a results set, and more. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

By: Murach Publishing
Rating: starstarstarstarstar / 0
July 21, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

SELECT [ALL|DISTINCTcolumn_specification [[AS] result_column] [, column_specification [[AS] result_column]] ...

Five ways to code column specifications

Source

Option

Syntax

Base table value

All columns

*

 

Column name

column_name

Calculated value

Result of a concatenation

String expression (see figure 3-5)

 

Result of a calculation

Arithmetic expression (see figure 3-6)

 

Result of a scalar function

Scalar function (see figure 3-7)

Column specifications that use base table values

The * is used to retrieve all columns

SELECT *

Column names are used to retrieve specific columns

SELECT vendor_namevendor_cityvendor_state

Column specifications that use calculated values

An arithmetic expression is used to calculate balance_due

SELECT invoice_numberinvoice_total payment_total credit_total AS balance_due

A string expression is used to derive full_name

SELECT first_name || ' ' || last_name AS full_name

Description

  1. Use SELECT * only when you need to retrieve all columns from a table. Otherwise, list the names of the columns you need.
  2. An expression is a combination of column names and operators that evaluate to a single value. In the SELECT clause, you can code arithmetic expressions, string expressions, and expressions that include one or more functions.
  3. After each column specification, you can code an AS clause to specify the name for the column in the result set. See figure 3-4 for details.

Note

  • The other elements shown in the syntax summary above let you control the number of rows that are returned by a query. You can use the DISTINCT keyword to eliminate duplicate rows. See figure 3-9 for details.

--------------------------------------------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 uses the AS keyword
-- DATE is a reserved keyword.
-- As 
a resultit must be enclosed in quotations.
SELECT invoice_number AS "Invoice Number"invoice_date AS "Date",
invoice_total AS total
FROM invoices

A SELECT statement that omits the AS keyword

SELECT invoice_number Invoice Number;, invoice_date "Date,
invoice_total total
FROM invoices

The result set for both SELECT statements

A SELECT statement that doesnít provide a name for a calculated column

SELECT invoice_numberinvoice_dateinvoice_total,
invoice_total payment_total credit_total
FROM invoices

Description

  1. By default, a column in the result set is given the same name as the column in the base table. If thatís not what you want, you can specify a column alias for the column.
  2. One way to name a column is to use the AS keyword as shown in the first example above. Although the AS keyword is optional, it enhances readability.
  3. If an alias includes spaces or an Oracle reserved keyword, you must enclose it in double quotes.
  4. When you enclose an alias in quotes, the result set uses the capitalization specified by the alias. Otherwise, the result set capitalizes all letters in the column name.

--------------------------------------------Figure 3-4 How to name the columns in a result set

Please check back for the next part of this series.


 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: