The Ubiquitous SELECT Statement - Oracle DBA

In the examples of tools for running SQL, you've seen the following simple SELECT statement:

In its most basic form, the SELECT statement has a list of columns to select from a table, using the SELECT ... FROM syntax. The * means "all columns." To successfully retrieve rows from a table, the user running the query must either own the table or have the permissions granted to the user by the owner or a DBA. The most basic SELECT syntax can be described as follows:

This type of statement representation is typical of what you'll see in Oracle documentation, and it can be very complex. Here is a summary of what the elements in the syntax representation mean:


Element

Meaning

|

Pick one or the other

{}

One within this list is required

Element

Meaning

[]

Item is optional

May repeat

Uppercase

Keyword or command

italics

Variable

We will explore many more advanced features of the SELECT statement throughout this book. However, to begin with, let's look at some examples of the column, alias, DISTINCT, and expression parts of a SELECT statement.

Column Specification

As you've seen, you can use the * character to view all columns in a table. But if the table contains too many columns to view at once, or your query needs only a small number of the total columns, you can pick the columns you need. For example, suppose that you want to view some information in the EMP table. How could you find out which columns are in this table without doing a SELECT * statement? You could use the DESCRIBE command in iSQL*Plus, as shown below.

Column Specification

Now that you know which columns exist in the EMP table, you realize that you really need to see only the employee number, name, and salary. Therefore, your SELECT statement should be something like this:

It produces results similar to the following:

expressions

Column Renaming

In one of our earlier SQL*Plus examples, we wanted the column headers to be more readable, and we used some of the built-in features of SQL*Plus to do this. However, if your requirements for readability are fairly simple, you can use SQL's built-in capability of column renaming, noted by the [alias] element of the SELECT syntax. Here is an example of providing aliases for the EMPNO, ENAME, and SAL columns in the EMP table. The alias is the renamed column seen in the results of the query.

Alias An alternate name for a column, specified right after the column name in a SELECT statement, seen in the results of the query.

Column Renaming

Duplicate Removal

The DISTINCT keyword removes all duplicate rows from the results of a query. For example, what if you wanted to see the department numbers for the employees in the EMP table? Your query might be something like this:

Duplicate Removal

But what you probably want is one row for each of the departments found in the EMP table. In this case, use the DISTINCT keyword:

Duplicate Removal

That's much easier to read. You now know that all of the employees belong to one of three departments. However, there may be many other departments, which would be listed in the department (DEPT) table. Some departments may not have any employees right now.

Expressions

To finish off our analysis of the SELECT syntax, let's look at the expression part of the SELECT statement. Let's say we would like to see how salaries would look if everyone got a 15 percent pay increase. All of the information we need to see is still in one table, the EMP table, but we need to perform some kind of calculation on one of the existing fields. To calculate a 15 percent pay increase, we need to not only see the existing salary but also multiply the SAL column by 1.15:

expressions

To make the proposed salary column more readable, we could use either a column alias or iSQL*Plus column-formatting commands. We might also want to show a total for the SAL and SAL*1.15 columns or show each salary increase to exactly two decimal places.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics