2.3. The Ubiquitous SELECT Statement

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

select * from dept;

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:

SELECT {* | [DISTINCT] column| expression [alias], ...}
  FROM tablename;

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.

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

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:

select empno, ename, sal from emp;

It produces results similar to the following:

EMPNO      ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450

7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

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

select empno "Employee Number", ename "Name", sal "Salary"
from emp;

Employee Number Name           Salary
--------------- ---------- ----------
           7369 SMITH             800
           7499 ALLEN            1600
           7521 WARD             1250
           7566 JONES            2975
           7654 MARTIN           1250
           7698 BLAKE            2850
           7782 CLARK            2450
           7788 SCOTT            3000
           7839 KING             5000
           7844 TURNER           1500
           7876 ADAMS            1100
           7900 JAMES             950
           7902 FORD             3000
           7934 MILLER           1300

14 rows selected.

2.3.3. 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:

select deptno from emp;

DEPTNO
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20
        30
        20
        10

14 rows selected.

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:

select distinct deptno from emp;

DEPTNO
----------
        10
        20
        30

3 rows selected.

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. In Chapter 5, "Using Multiple Tables," you'll learn how to execute queries on joined tables to get this kind of information.

2.3.4. 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:

select empno, ename, sal, sal*1.15 from emp;

EMPNO      ENAME             SAL   SAL*1.15
---------- ---------- ---------- ----------
      7369 SMITH             800        920
      7499 ALLEN            1600       1840
      7521 WARD             1250     1437.5
      7566 JONES            2975    3421.25
      7654 MARTIN           1250     1437.5
      7698 BLAKE            2850     3277.5
      7782 CLARK            2450     2817.5
      7788 SCOTT            3000       3450
      7839 KING             5000       5750
      7844 TURNER           1500       1725
      7876 ADAMS            1100       1265
      7900 JAMES             950     1092.5
      7902 FORD             3000       3450
      7934 MILLER           1300       1495

14 rows selected.

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. Some of these more advanced formatting techniques will be covered in Chapter 9, "Reporting Techniques."