Previous | Table of Contents | Next

Page 41

Listing 3.2L_03_02.TXT—Editing Statements in the Buffer

SQL> l
1* select * from employee
SQL> c/employee/department
1* select * from department
SQL> l
1* select * from department

Another method of entering and editing a script is to use an external editor such as Windows Notepad. Just enter edit filename at the SQL>. SQL*PLUS invokes the default editor, Windows Notepad. You can change the default editor by clicking Edit, Editor, Define. When the file name does not exist, an Alert box appears and asks whether you want to create a new file. After the script is finalized in the editor, save the file. Press Alt+Tab to invoke SQL*PLUS, and enter start filename or the @filename command. Either command loads the file into the buffer and executes it. Figure 3.4 shows an example of this procedure.

FIG. 3.4
Using the external
editor.


Oracle automatically places a default file extension of .SQL on any file saved from the SQL editor unless you specify a different extension. In addition, when loading or running a file from the editor, SQL*PLUS assumes this same default extension unless you specify a different extension in the command.

Understanding the Select Command Format

SQL*PLUS commands are based on the SQL language. SQL stands for structured query language. The most basic command is Select. This command is used to retrieve and view data from the database. The select statement has six clauses: select, from, where, group by, having, and order by. Figure 3.5 shows the format of the statement.

Page 42

FIG. 3.5
The select statement's
clauses and syntax.


The Select Clause

The first clause in the command statement is the select. This section is used to list the columns, literals, calculations, and functions (such as avg, sum, min, max) to be performed on columns. Each column is separated by a comma (,). Numeric columns may be added, subtracted, multiplied, or divided by placing an arithmetic operator between the column names. Oracle knows that the columns are part of one virtual column because a comma does not separate them. You can combine character columns by using the concatenation operator (||). Literals are numeric or character values placed in the clause. They appear in each row retrieved from the table.

The From Clause

The from clause follows immediately behind the select clause. This section is used to list the Oracle tables that contain the columns listed in the select clause. Multiple tables may be listed in this section. A comma must separate each tablename.

The Where Clause

The where clause is used to list conditions that limit the number of records retrieved by the statement. Without any conditions, the select statement retrieves and displays all the records in the listed tables. A condition consists of two table columns or one column and a value separated by an evaluation operator. Several of the evaluation operators are (=) equal to, (!=) not equal to, or (>) greater than. There are no limits to the number of conditions that may be listed. For the record to be selected from the database, each condition in the where clause must be evaluated to true.

The Group By Clause

The group by clause is used to indicate the set of records on which to perform the functions. Several of the functions that can be listed in the select clause are avg, sum, min, and max. A function can be performed on the entire set of retrieved records table or on subsets. When the computation is performed on subsets of the records, you must include the group by clause in the select statement. This is done so that SQL*PLUS knows the subsets on which to perform the functions.

Page 43

The Having Clause

The having clause is similar to the where clause. It is used to limit the number of records retrieved from the database. The difference is that sets of records are evaluated in this section. You can use the functions sum, avg, min, and max as conditional operators in the having section only. The where clause does not allow functions to be used.

The Order By Clause

Records are not stored in any particular order in the database. They are retrieved and displayed in the same order in which they are stored unless the order by clause is included in the select statement. The order by clause contains the name(s) or number(s) of the column(s) that determine the order. The number is the numeric place of the column in the select clause. You can list multiple columns in this section.

Simple Select

A simple select statement contains only the required portions of the statement. It has one or more columns defined in the select clause, one tablename in the from clause, and a semicolon at the end of the statement. The statement retrieves all the rows from the table and displays the columns listed in the select clause. Listing 3.3 shows an example of this type of statement:

Listing 3.3L_03_03.TXT—Selecting the Last Name and First Name of the Employees

SQL> select last_name, first_name from employee;
LAST_NAME       FIRST_NAME
--------------- ----------------
COOLIDGE        CALVIN
JOHNSON         LYNDON
REAGAN          RONALD
BUSH            GEORGE
JOHNSON         ANDREW
.
19 rows selected.
NOTE
Most of the listings contained in this book do not have all of the retrieved records displayed. I displayed only a sample of the records in order to save space. When records were removed from the listing, a period was placed in the listing. In the preceding listing, five records are displayed and 19 were selected. The period denotes 14 were removed.

This statement lists all the last_name and first_name records from the employee table. In some cases you may want to list all the columns in each record. You can do this by listing each column name in the select clause or by using the ALL symbol. This symbol is an asterisk (*). When the asterisk is listed in the select clause, all table columns appear (see Listing 3.1).

Previous | Table of Contents | Next