Up Next

Chapter 1 *

Selecting Data from Oracle *

Selecting Rows *

Writing SELECT Statements *

Salary *

Exercises *

Performing Arithmetic Equations *

Exercises *

Handling NULL Values *

Exercises *

"Renaming" Columns with Column Aliases *

Exercises *

Putting Columns Together with Concatenation *

Exercises *

Editing SQL Queries Within SQL*Plus *

ORA-00923: FROM Keyword Not Found Where Expected *

ORA-00942: Table or View Does Not Exist *

Exercises *

Limiting Selected Output *

The ORDER BY Clause *

Exercises *

The WHERE Clause *

Exercises *

Using Single-row Functions *

Various Single-row Functions Explained *

Exercises *

Using Functions in SELECT Statements *

Exercises *

Date Functions *

Exercises *

Conversion Functions *

Exercises *

Chapter Summary *

Two-minute Drill *

 

Unit I

Preparing for OCP DBA Exam 1: SQL and PL/SQL

 

Chapter 1

Selecting Data from Oracle

In this chapter, you will understand and demonstrate knowledge in the following areas:

Selecting rows
Limiting selected output
Using single-row functions

The first exam in the Oracle Certified Professional series covers many basic areas of database usage and design. Every Oracle user, developer, and DBA should have complete mastery in these areas before moving on into other test areas such as database design, administration, backup and recovery, and tuning. This unit assumes little or no prior knowledge of Oracle in order to help the user go from never using Oracle to having enough expertise in the Oracle server product to maintain and enhance existing applications and develop small new ones. The five chapters in this unit will function as the basis for understanding the rest of the book. This chapter will cover several aspects of data retrieval from the Oracle database, including selecting rows, limiting the selection, single-row functions, joining data from two or more tables, grouping functions, subqueries, and specifying variables at execution time. The content of this chapter covers material comprising 17 percent of test content on OCP Exam 1.

Selecting Rows

In this section, you will cover the following areas related to selecting rows:

Writing select statements
Performing arithmetic equations
Handling NULL values
Renaming columns with column aliases
Putting columns together with concatenation
Editing SQL queries within SQL*Plus

Use of Oracle for many people begins with usage of an existing Oracle application in an organization. The first tool many people see for selecting data directly from the Oracle relational database management system is called SQL*Plus. When users first start SQL*Plus, in most cases they must enter their Oracle username and password in order to begin a session with the Oracle database. There are some exceptions to this rule that utilize the password authentication provided with the operating system. The next unit, covering OCP Exam 2, will explore the methods and implications of starting SQL*Plus sessions without supplying an Oracle username and password. The following examples show how you might begin a session.

$> sqlplus jason/athena

or

$> sqlplus /

A session is an interactive runtime environment in which the user enters a command to retrieve data and Oracle performs a series of activities to obtain the data that the user asked for. What does interactive mean? It means that Oracle and the user have an interactive "conversation" in which the user asks Oracle to provide certain pieces of information and Oracle provides it. Conversation implies language. In order for the user and Oracle to communicate, they must both speak the same language. The language users "speak" to Oracle in order to retrieve data is a special type of language called Structured Query Language, or SQL for short. SQL can be pronounced as three individual letters, or in the same way as "sequel." SQL is a "functional" language. A functional language is one that allows the users to specify the types of things they want to see happen in terms of the results they want. Contrast this approach to other languages you may have heard about or programmed in, such as C++ or COBOL. These other languages are often referred to as "procedural" programming languages because the code written in these languages implies an end result by explicitly defining the means, or the procedure, by which to get there. In contrast, SQL explicitly defines the end result, leaving it up to Oracle to determine the method by which to obtain the data. Data selection can be accomplished using the following code listing.

SELECT *
FROM emp
WHERE empid = 39334;

This SQL statement asks Oracle to provide all data from the EMP table where the value in a certain column called EMPID equals 39334. The following block of code from an imaginary procedural programming language similar to C illustrates how the same function may be handled by explicitly defining the means to the end.

Include <stdio.h>
Include <string.h>
Include <rdbms.h>

Int *empid;
Char *statement;

Type emp_rec is record (
Int empid;
Char[10] emp_name;
Int salary; )

Void main()
{
Access_table(emp);
Open(statement.memaddr);
Strcpy("SELECT * FROM EMP WHERE EMPID = 39334",statement.text);
parse(statement);
execute(statement);
for (I=1,I=statement.results,I+1) {
fetch(statement.result[I],emp_rec);
printf(emp_rec);
}
close(statement.memaddr);
}

Of course, that C-like block of code would not compile anywhere but in the imagination of the reader, but the point of the example is clear—other languages define a means toward an end, while SQL allows the user to define the end in and of itself.

Writing SELECT Statements

The most common type of SQL statement executed in most database environments is the query, or select statement. Select statements are designed to pull requested data from the database. Where is data stored in the database? Data is stored in special database objects called tables. A table in Oracle is similar in concept to Table 1-1. It has columns and rows, each of which is meant to be unique. For more information about tables, see the next chapter. SQL provides a readable interface used to pull data from the EMP table as designated in the preceding statement.

Empid Lastname Fname

Salary

39334 Smith Gina 75,000
49539 Qian Lee 90,000
60403 Harper Rod 45,000
02039 Walla Rajendra 60,000
49392 Spanky Stacy 100,000

Table 1: EMP

The user can issue a simple select statement that is designed to pull all data from the table shown in Table 1-1. When SQL*Plus is started, it produces several components of information, including the version of SQL*Plus being used, the date, the version of the Oracle database being accessed, the version of PL/SQL in use, and the server options available on the database. The following code block demonstrates SQL*Plus.

SQL*Plus: Release 3.2.3.0.0 - Production on Tue Feb 03 18:53:11 1998
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Connected to: Oracle7 Release 7.3.4.0.1
With the distributed and replication options
PL/SQL Release 2.3.0.0.0 – Production

SQL> SELECT * FROM HRAPP.EMP;

EMPID LASTNAME FIRSTNAME SALARY
----- -------- --------- ------
39334 SMITH    GINA      75000
49539 QIAN     LEE       90000
60403 HARPER   ROD       45000
02039 WALLA    RAJENDRA  60000
49392 SPANKY   STACY     100000

The line in bold in this excerpt from a SQL*Plus session illustrates the entry of a simple SQL statement. The query requests Oracle to give all data from all columns in the EMP table. Oracle replies with the contents of the EMP table as diagrammed in Table 1-1. Note that the user did not tell Oracle how to retrieve the data, the user simply expressed the data they wanted using SQL syntax and Oracle returned it. Chapter 17 shows how Oracle performs these tasks behind the scenes.. For now, make sure you understand how to specify a schema owner, the table name, and the column name in a select statement in SQL*Plus. The following code block demonstrates proper usage.

SELECT table_name.column_name, table_name.column_name …
FROM schema.table_name;

Tip: Always use a semicolon (;) to end SQL statements when entering them directly into SQL*Plus.

The main components of a select statement are outlined. The first component is the select clause. This part is required in order for Oracle to identify that it must now perform a select statement. The second component of a select statement is the list of columns from which the user would like to view data. In the statement issued in the example SQL*Plus session above, the column listing as described in the statement format was substituted with a special wildcard (*) character, which indicates to Oracle that the user wants to view data from every column in the database. The user could have executed the following query and obtained the following data instead. The last aspect of the select statement of importance is the from clause. This special clause tells Oracle what database table to pull the information from. Usually, the database user will need to specify the schema, or owner, to which the table belongs, in addition to naming the table from which the data should come.

SELECT empid, lastname, salary FROM HRAPP.EMP;

EMPID LASTNAME SALARY
----- -------- ------
39334 SMITH     75000
49539 QIAN      90000
60403 HARPER    45000
02039 WALLA     60000
49392 SPANKY    100000

Notice in the statement issued above that the table named in the from clause is HRAPP.EMP. This means that Oracle should pull data from the EMP table in the HRAPP schema. When a user is granted the ability to create database objects, the objects he or she creates belong to the user. Ownership creates a logical grouping of the database objects by owner, and the grouping is called a schema.

Tip: A schema is a logical grouping of database objects based on the user that owns the object.

Exercises

  1. What is a select statement? Name the two required components of a select statement.
  2. How should the user end a select statement in SQL*Plus?
  3. What is a schema?

Performing Arithmetic Equations

In addition to simple selection of data from a table, Oracle allows the user to perform different types of activities using the data. The most basic of these activities is arithmetic. All basic arithmetic operations are available in Oracle, including addition, subtraction, multiplication, and division. The operators used to denote arithmetic in Oracle SQL are the same as in daily use.

To better understand use of arithmetic equations in Oracle, the following example is offered. Assume, for example, that the user of the database is performing a simple annual review that involves giving each user a cost-of-living increase in the amount of 8 percent of their salary. The process would involve multiplying each person’s salary by 1.08. The user could execute the process manually with pencil or calculator, but look at how much easier it is for the user to execute a slightly more complicated query to determine the result with SQL:

SELECT empid, salary, salary*1.08 FROM HRAPP.EMP;

EMPID LASTNAME SALARY SALARY*1.08
----- -------- ------ -----------
39334 SMITH    75000  81000
49539 QIAN     90000  97200
60403 HARPER   45000  48600
02039 WALLA    60000  64800
49392 SPANKY   100000 108000

SQL allows the user to execute all types of arithmetic operations, including +, -, *, /. Oracle allows the user to execute special SQL statements designed to perform mathematical problems without selecting data as well. The feature of Oracle related to arithmetic functions of this type is a special table called DUAL. DUAL is an empty table that is used to fulfill the SQL select from construct.

SELECT 64+36 FROM DUAL;

64+36
-----
100

There is no data actually in DUAL; rather, it simply exists as a SQL construct to support the requirement of a table specification in the from clause. Additionally, the DUAL table contains only one column and one row.

Tip: DUAL is a special table consisting of one column and all NULL values. DUAL is used to satisfy the SQL syntax construct stating that all SQL statements must contain a from clause that names the table from which the data will be selected. When a user does not want to pull data from any table, but rather wants simply to use an arithmetic operation on a constant value, the user can include the values, operations, and the from DUAL clause.

Exercises

  1. How can the user perform arithmetic on selected columns in Oracle?
  2. What is the DUAL table? Why is it used?
  3. How does the user specify arithmetic operations on numbers not selected from any table?

Handling NULL Values

Sometimes, a query for some information will produce a nothing result. In database terms, nothing is called NULL. NULL is an expression that represents a nothing value. In set theory, the mathematical foundation for relational databases, NULL represents the value of an empty dataset, or a dataset containing no values. Unless specified otherwise, a column in a table is designed to accommodate the placement of nothing into the column. An example of retrieving a NULL is listed in the following code block. Notice that some of the employees have no spouse. Nothing in Oracle is represented with the NULL value. NULL is similar to nothing in that it represents no data present for this column in the row.

SELECT empid, lastname, firstname, spouse FROM HRAPP.EMP;

EMPID LASTNAME FIRSTNAME SPOUSE
----- -------- --------- ------
39334 SMITH    GINA      FRED
49539 QIAN     LEE
60403 HARPER   ROD       SUSAN
02039 WALLA    RAJENDRA  HARPREET
49392 SPANKY   STACY

However, there arise times when the user will not want to see nothing. Instead of retrieving an empty data field, there may be occasions where the user wants to see some default message. Oracle provides this functionality with a special function called nvl( ). This is the first function covered, so some extra attention will be paid to using it in Oracle. In the case above, assume that the user does not want to see blank spaces for spouse information. Instead, the user wants the output of the query to contain the word "unmarried" instead. The query below illustrates how the user can issue the query against Oracle to obtain the desired result. The nvl( ) function is used to modify the SPOUSE column such that if the value in the SPOUSE column is NULL, it will return the text string ‘unmarried’. Text strings in Oracle must be enclosed in single quotes.

SELECT empid, lastname, firstname, NVL(spouse,’unmarried’) FROM HRAPP.EMP;

EMPID LASTNAME FIRSTNAME NVL(spous
----- -------- --------- ---------
39334 SMITH    GINA      FRED
49539 QIAN     LEE       unmarried
60403 HARPER   ROD       SUSAN
02039 WALLA    RAJENDRA  HARPREET
49392 SPANKY   STACY     unmarried

Notice, first of all, that if the column specified in nvl( ) is not NULL, the value in the column is returned, while when the column is NULL, the special string is returned. The nvl( ) function can be used on columns of all datatypes. A discussion of different datatypes will appear later. For now, it is important to understand that the syntax for nvl( ) is as follows:

NVL(column_name, value_if_null)

Exercises

  1. What does NULL mean in the context of Oracle SQL?
  2. What is the nvl( ) function? How is it used?

"Renaming" Columns with Column Aliases

As the user may have noticed in some of the earlier examples, when Oracle returns data to the user, Oracle creates special headings for each column so that the user knows what the data is. The heading returned corresponds directly with the name of the column passed to Oracle as part of the select statement:

SELECT empid, lastname, firstname, NVL(spouse,’unmarried’) FROM HRAPP.EMP;

EMPID LASTNAME FIRSTNAME NVL(spous
----- -------- --------- ---------
39334 SMITH    GINA      FRED
49539 QIAN     LEE       unmarried
60403 HARPER   ROD       SUSAN
02039 WALLA    RAJENDRA  HARPREET
49392 SPANKY   STACY     unmarried

Each of the columns above in bold correspond to the column names indicated in the select statement, including the nvl( ) operation on SPOUSE. By default, Oracle reprints the column name exactly as it was included in the select statement. Unfortunately, although this method exactly describes the data selected in the query, it does not usually give a descriptive explanation of the column data. Compounding the problem is the fact that Oracle truncates the expression to fit a certain column length corresponding to the datatype of the column returned.

Oracle provides a solution to this situation with the use of column aliases in the select statement. Any column can be given another name by the user when the select statement is issued. This feature gives the user the ability to fit more descriptive names into the space allotted by the column datatype definition.

SELECT empid, lastname, firstname, NVL(spouse,’unmarried’) spouse FROM HRAPP.EMP;

EMPID LASTNAME FIRSTNAME SPOUSE
----- -------- --------- ---------
39334 SMITH    GINA      FRED
49539 QIAN     LEE       unmarried
60403 HARPER   ROD       SUSAN
02039 WALLA    RAJENDRA  HARPREET
49392 SPANKY   STACY     unmarried

As indicated in bold by the above code, the SPOUSE column is again named SPOUSE, even with the nvl( ) operation performed on it. The alias is specified after the column is named in the select statement according to the following method. In order to specify an alias, simply name the alias after identifying the column to be selected, with or without an operation performed on it, separated by white space.

Alternately, the user can issue the as keyword to denote the alias. The column with the operation in it is specified as usual, but instead of naming the column alias after white space following that column with operation, the as keyword can clearly identify the alias for others reading the query. Note the use of as to denote the alias in the following code block:

SELECT empid, lastname, firstname, NVL(spouse,’unmarried’)
AS spouse
FROM HRAPP.EMP;

EMPID LASTNAME FIRSTNAME SPOUSE
----- -------- --------- ---------
39334 SMITH    GINA      FRED
49539 QIAN     LEE       unmarried
60403 HARPER   ROD       SUSAN
02039 WALLA    RAJENDRA  HARPREET
49392 SPANKY   STACY     unmarried

To summarize, column aliases are useful for identifying the data in the output from SQL queries with meaningful headings. Aliases can be specified in two ways, either by naming the alias after the column specification separated by white space, or with the use of the as keyword to mark the alias more clearly for other readers of the query, as shown here:

SELECT column_with_or_without_operation alias, …;

or

SELECT column_with_or_without_operation AS alias, …;

Exercises

  1. What is a column alias? For what situations might column aliases be useful?
  2. What are two ways to define aliases for columns?

Putting Columns Together with Concatenation

Renaming a column in a select statement and using the nvl( ) operation are not the only things that can be done to change the output of a query. Entire columns can be put together to produce more interesting or readable output. The method used to merge the output of certain columns into something new is accomplished with a special operation called concatenation. The concatenation operator looks like two pipe characters put together, or ||. Consider the running example of output from EMP. In the following example, the user wants to change the name output to be in the format lastname, firstname.

SELECT empid, lastname||’, ’||firstname full_name, NVL(spouse,’unmarried’) spouse,
FROM HRAPP.EMP;

EMPID FULL_NAME        SPOUSE
----- ---------------- ---------
39334 SMITH, GINA       FRED
49539 QIAN, LEE         unmarried
60403 HARPER, ROD       SUSAN
02039 WALLA, RAJENDRA   HARPREET
49392 SPANKY, STACY     unmarried

Again, by using the concatenation operator in conjunction with a text string enclosed in single quotes, the output of two or more columns, or even the output of one column, can be put together to express new meaning. For good measure, the use of column aliases is recommended in order to make the name of the concatenated columns above more meaningful.

Exercises

  1. What is column concatenation?
  2. What special character sequence is used to concatenate columns?

Editing SQL Queries Within SQL*Plus

As the user gains more exposure to SQL*Plus, he or she will undoubtedly notice an annoying feature of the SQL*Plus command line—it can’t back up to previous lines. In other words, the user must type in the query very carefully, making sure that each line of the statement is correct before hitting the ENTER key and moving on to type the next line. So far, this limitation of the SQL command line hasn’t presented much difficulty. However, as the queries the user can write get more and more complicated, the user will grow frustrated. SQL*Plus does allow some correction of statement entry with the use of a special command called change, abbreviated as c. Consider the following example to illustrate the point:

SELECT empid, lastname||’, ’||firstname full_name,
NVL(sppuse,’unmarried’) spouse, FROM HRAPP.EMP;

NVL(sppuse,’unmarried’) spouse, FROM HRAPP.EMP;
*
ERROR at line 2:
ORA-00904: invalid column name

SQL> 2

2> NVL(sppuse,’unmarried’) spouse, FROM HRAPP.EMP;

SQL> c/sppuse/spouse

2> NVL(spouse,’unmarried’) spouse, FROM HRAPP.EMP;

SQL> /

EMPID FULL_NAME         SPOUSE
----- ----------------  ---------
39334 SMITH, GINA       FRED
49539 QIAN, LEE         unmarried
60403 HARPER, ROD       SUSAN
02039 WALLA, RAJENDRA   HARPREET
49392 SPANKY, STACY     unmarried

In this example, the user issues a select statement containing a typographical error, sppuse. Oracle notices the error and alerts the user to it with ORA-00924. To change it, the user first references the line containing the mistake, in this case with the number 2. At this point, Oracle indicates the current version of the SQL statement. Then the user issues the change command, abbreviated as c. The old text appears after the first slash, and the new text follows the second slash. Oracle makes the change and then displays the new version of the line. The user can then execute the SQL statement, using the slash (/) command. Other errors that may be produced by the user.

ORA-00923: FROM Keyword Not Found Where Expected

This error indicates that the from keyword was not included or was misspelled.

ORA-00942: Table or View Does Not Exist

This error indicates that the table or view typed in does not exist. Usually, the reason for ORA-00942 is a typo in the name of the table or view, or because the schema owner was not specified in front of the table name. This error is fixed either by correcting the typing problem or by adding the schema owner onto the front of the table name. An alternative solution exists for the latter cause in creating synonyms for tables that are accessible to other users. This solution will be discussed in a later section.

In any case, the method used to correct the typing problem is to first select the line number from the special buffer that SQL*Plus maintains the current query in. The name of the buffer is afiedt.buf on many operating systems. The line of the buffer is identified by entering the line number and pressing ENTER as indicated by the entry at the prompt as indicated. After that line is chosen, the change command is entered in the following syntax.

c/old_value/new_value

After making the change to the first appearance of old_value in the current line, Oracle redisplays the current line, with the change made. Note that the change will be made to the first appearance of old_value only. If the change must be made to a specific place in the line, more characters can be added to the old_value parameter as appropriate. Finally, the corrected text can be reexecuted by entering a slash (/) at the prompt as indicated.

It takes some acclimation before the user will use SQL*Plus line editor to change command as freely as they would use their own favorite text editor. Fortunately, Oracle makes provisions for the users to utilize their favorite text editor to edit the statement created in afiedt.buf. Instead of entering all the additional commands to identify the line to be edited, followed by the exact character sequence to change, Oracle allows the user to type in the edit command (abbreviated ed). This action causes Oracle to bring up the SQL statement in afiedt.buf into the operating system’s default text editor. On UNIX systems, that text editor is usually VI or EMACS, while Windows environments usually opt for the Notepad text editor. To change the text editor used, issue the define_editor=youreditor’ statement from the prompt.

Using a text editor rather than the line editor native to SQL*Plus offers many benefits. First and foremost is the benefit of using a text editor the user knows well, creating a familiarity with the application that is useful in adapting to SQL*Plus quickly. Second, it is helpful with large queries to have the entire block of code in front of the user and immediately accessible.

Tip: When running SQL statements from scripts, do NOT put a semicolon (;) at the end of the SQL statement. Instead, put a slash (/) character on the line following the script.

One final word of note for using external editors to create SQL statements that will then be executed with Oracle SQL*Plus. It is possible to use the text editor of choice to write the entire query first and then load it into Oracle SQL*Plus. Two commands are available for this functionality. The first is called get. The get command opens the text file specified and places it in afiedt.buf. Once loaded, the user can execute the command using the slash (/) command. Alternately, the user can simply load SQL statements from file into afiedt.buf and execute in one step using the @ command.

SQL*Plus: Release 3.2.3.0.0 - Production on Tue Feb 03 18:53:11 1998
Copyright (c) Oracle Corporation 1979, 1998. All rights reserved.
Connected to Oracle7 Release 7.3.4.0.0
With the distributed and replication options
PL/SQL Release 2.3.0.0.0 - Production

SQL> GET select_emp
SELECT * FROM emp
SQL> /

EMPID LASTNAME FIRSTNAME SALARY
----- -------- --------- ------
39334 SMITH    GINA      75000
49539 QIAN     LEE       90000
60403 HARPER   ROD       45000
02039 WALLA    RAJENDRA  60000
49392 SPANKY   STACY     100000

5 rows selected;

SQL> @select_emp

SELECT * FROM emp


/

EMPID LASTNAME FIRSTNAME SALARY
----- -------- --------- ------
39334 SMITH    GINA      75000
49539 QIAN     LEE       90000
60403 HARPER   ROD 4     45000
02039 WALLA    RAJENDRA  60000
49392 SPANKY   STACY     100000

5 rows selected;

In the first case illustrated by the example above, the get command is used to pull in the contents of the select_emp.sql file into the afiedt.buf buffer. Notice that the ".sql" extension was left off. Oracle SQL*Plus assumes that all scripts containing SQL statements will have the .sql extension, so the extension can be omitted. Notice also that after the file is brought in using get, it can then be executed using the slash (/) command. In the second case, illustrated by #2 above, the same file is read into afiedt.buf and executed in one step, eliminating the need for the slash (/) command by using the @ command. Again, the .sql extension is omitted. Remember that when using the get or @ commands, if a full pathname is not specified as the filename, then Oracle SQL*Plus assumes the file is in the local directory.

Exercises

  1. What two mechanisms are available to enter and modify SQL statements within SQL*Plus?
  2. What is the edit command in the SQL*Plus command line? How can SQL scripts be loaded from files into SQL*Plus? How are they run?
  3. What command is used to define a text editor for SQL*Plus to use?

Limiting Selected Output

In this section, you will cover the following areas related to limiting selected output:

The order by clause
The where clause

Obtaining all output from a table is a great feature, but in the reality of database applications, the user must be more selective in choosing output. The reason is because most database applications contain a lot of data. How much data can a database contain? Some applications contain tables with a million rows or more, and the most recent release of Oracle, Oracle8, will store up to 512 petabytes of data. Needless to say, manipulating vast amounts of data requires the user to be careful to ask for exactly what he or she wants.

The ORDER BY Clause

In our running example of data from the EMP table, a fundamental principle of relational data storage is illustrated. Data within a table need not have any order. Another quick look at the output from the EMP table will demonstrate:

SQL> /

EMPID LASTNAME FIRSTNAME SALARY
----- -------- --------- ------
39334 SMITH    GINA      75000
49539 QIAN     LEE       90000
60403 HARPER   ROD       45000
02039 WALLA    RAJENDRA  60000
49392 SPANKY   STACY     100000

Notice that the data returned is in no particular order on any column, either numeric or alphabetical. Oracle allows the user to place order on output from select statements by issuing a special clause along with the statement already presented. That special clause is called order by. This clause can be included in select statements at the end of the statement. The general syntax for the order by clause is to include both the clause and the column or column alias(es) on which Oracle will define order, optionally followed by a special clause defining the direction of the order. Possible directions are asc for ascending and desc for descending, as shown below:

SQL> SELECT * FROM emp ORDER BY empid DESC;

EMPID LASTNAME FIRSTNAME SALARY
----- -------- --------- ------
60403 HARPER   ROD       45000
49539 QIAN     LEE       90000
49392 SPANKY   STACY     100000
39334 SMITH    GINA      75000
02039 WALLA    RAJENDRA  60000

In addition to providing order on one column, Oracle can provide order on many columns. If the user desires, he or she can include multiple column specifications, as well as ascending or descending order in each of the columns specified. The order by clause can be useful in simple reporting. It can be applied to columns that are of NUMBER, text (VARCHAR2 and CHAR), and DATE datatypes. Finally, one can use numbers to indicate the column on which Oracle should order the output from a statement. The use of numbers depends on the positioning of each column. For example, if the user issues a statement similar to the one in the following code block, the order for the output will be as shown. The number 2 indicates that the second column should be used to define order in the output. But, since the second column is something different in each statement, the order of the output will be different as well.

SELECT empid, lastname FROM emp ORDER BY 2;

EMPID LASTNAME
----- --------
02039 WALLA
39334 SMITH
49392 SPANKY
49539 QIAN
60403 HARPER

SELECT empid, lastnname FROM emp ORDER BY 2;

LASTNAME EMPID
-------- -----
60403    HARPER
39334    SMITH
49392    SPANKY
49539    QIAN
02039    WALLA

Exercises

  1. How can a user put row data returned from a select statement in order? What are the various orders that can be used with this option?
  2. What are the two ways the column on which order should be defined can be identified?

The WHERE Clause

The where clause in Oracle select statements is where the really interesting things begin. This important clause in select statements allows the user to single out a few rows from hundreds, thousands, or even millions like it. The where clause operates on a basic principle of comparison:

SELECT * FROM emp WHERE empid = 43932;

EMPID LASTNAME FIRSTNAME SALARY
----- -------- --------- ------
49392 SPANKY   STACY     100000

Instead of pulling all rows from EMP, Oracle pulls just one row for display. To determine what row to display, the where clause performs a comparison operation as specified by the query—in this case, the comparison is an equality operation, where empid = 43932. However, equality is not the only means by which Oracle can obtain data. Some other examples of comparison are demonstrated in the following list:

x = y Comparison to see if x is equal to y.
x > y Comparison to see if x is greater than y.
x >= y Comparison to see if x is greater than or equal to y.
x < y Comparison to see if x is less than y.
x <= y Comparison to see if x is less than or equal to y.
x <> y
x != y
x ^= y
Comparison to see if x is not equal to y.
like A special comparison used in conjunction with the
character wildcard (%) character to find substrings in text variables.
soundex A special function used to introduce "fuzzy logic" into text string comparisons by allowing equality based on similarly spelled words.
between A range comparison operation that allows for operations on dates that are similar to the following numeric comparison: Y "is between" X and Z.
in A special comparison that allows the user to specify multiple equality statements by defining a set of values, any of which the value can be equal to. An example of its usage may be x IN (1,2,3,4,5).

These six operations are the cornerstone of comparison. According to Boolean logic (one of the cornerstones of computing in a database or any other type of environment), every comparison between two values boils down to one or more of these operations. A select statement need not have only one comparison in it to determine what data should be returned. Multiple comparisons can be placed together using the following list of operations. The operator is listed along with the result that is required to fulfill the criteria based on the presence of this operator.

x and y Both comparisons in x and y must be true.
x or y One comparison in x or y must be true.
not x The logical opposite of x.

Exercises

  1. What is a where clause? On what principle does this clause operate to determine which data is selected?
  2. What are some operations available to assist in the purpose of comparison? What are some operations that allow the user to specify more than one comparison in the where clause?

Using Single-row Functions

In this section, you will cover the following areas related to using single-row functions:

Various single-row functions explained
Using functions in select statements
Date functions

In addition to simple comparison, Oracle allows more complex comparison operations with the use of special functions. There are dozens of functions available in Oracle that can be used for many purposes. Some functions in Oracle are designed to alter the data returned by a query, such as the nvl( ) function already presented. The functions in this category are designed to work on columns of any datatype to return information in a different way. For example, the nvl( ) function can handle nulls appearing in any column, including dates, numbers, text strings, and others. One commonly used example of this type of function is decode( ). The decode( ) procedure works on the same principle as an if-then-else statement works in many common programming languages, including PL/SQL, which will be discussed in Chapter 6.

SELECT DECODE(column, val1, return1, val2, return2, … ,return_default)

The decode( ) function allows for powerful transformation of data from one value to another. Its scope encompasses the functionality provided by nvl( ), yet decode( ) goes so much farther than nvl( ) in its ability to return highly specialized data when given the right criteria. Furthermore, it allows for a default return value to be specified if the user so desires. Some examples of decode( ) in action will appear later in the chapter.

Various Single-row Functions Explained

From this point on, all functions described have limitations on the datatype they can perform their operations on. Related to the set of functions designed to convert data from one thing to another are several functions that manipulate text strings. These functions are similar in concept to nvl( ) and decode( ) in that they can perform a change on a piece of data, but the functions in this family can perform data change on only one type of data—text. As such, the functions in this family are often referred to as text, or character functions. In this family are several functions in Oracle, for which some of the highlighted functions that are most used are listed following this paragraph.

lpad(x,y[,z])
rpad(x,y[,z])
Returns the column "padded" on the left or right side of the data in the column passed as x to a width passed as y. The optional passed value z indicates the character that lpad or rpad will insert into the column.
lower(x)
upper(x)
initcap(x)
Returns the column value passed as x into all lowercase or uppercase, or changes the initial letter in the string to a capital letter.
length(x) Returns a number indicating the number of characters in the column value passed as x.
substr(x,y[,z]) Returns a substring of string x, starting at character number y to the end, which is optionally defined by the character appearing in position z of the string.

Others are designed to perform specialized mathematical functions such as those used in scientific applications like sine and logarithm, which should already be fairly well understood by those with a background in trigonometry. These operations are commonly referred to as math or number operations. The functions falling into this category are listed below. These functions are not all the ones available in Oracle, but rather are the most commonly used ones that will likely be used on OCP Exam 1.

abs(x) Obtains the absolute value for a number. For example, the absolute value of (-1) is 1, while the absolute value of 6 is 6.
ceil(x) Similar to executing round (see below) on an integer (i.e., round(x,0), except ceil always rounds up. For example, ceil(1.6) = 2. Note that rounding "up" on negative numbers produces a value closer to zero (e.g., ceil(-1.6) = -1, not –2).
floor(x) Similar to ceil (see above), except floor always rounds down. For example, floor(1.6) = 1. Note that rounding "down" on negative numbers produces a value further away from zero (e.g., floor(-1.6) = -2, not –1.
mod(x,y) The modulus of x, as defined by long division as the integer remainder left over when x is divided by y until no further whole number can be produced. An example is mod(10,3) = 1, or mod(10,2) = 0.
round(x,y) Round x to the decimal precision of y. If y is negative, round to the precision of y places to the left of the decimal point. For example, round(134.345,1) = 134.3, round(134.345,0) = 134, round(134.345,-1) = 130.
sign(x) Displays integer value corresponding to the sign of x, 1 if x is positive, -1 if x is negative.
sqrt(x) The square root of x.
trunc(x,y) Truncate value of x to decimal precision y. If y is negative, then truncate to y number of places to the left of the decimal point.
vsize(x) The storage size in bytes for value x.

The final category of number functions that will be discussed here is the set of list functions. These functions are actually used for many different datatypes, including text, numeric, and date. The list functions are listed below.

greatest(x,y,…) Returns the highest value from list of text strings, numbers, or dates (x,y…).
least(x,y,…) Returns the lowest value from list of text strings, numbers, or dates (x,y…).

Another class of data functions available in Oracle correspond to another commonly used datatype in the Oracle database—the DATE datatype. The functions that perform operations on dates are known as date functions. Before diving into the functions, a useful item in Oracle related to dates will be presented. There is a special keyword that can be specified to give Oracle users the current date. This keyword is called sysdate. In the same way that the user calculated simple arithmetic in an earlier part of the chapter using the DUAL table, so too can the user execute a select statement using sysdate to produce today’s date:

SELECT sysdate FROM DUAL;

SYSDATE
---------
15-MAR-98

With usage of sysdate established, the functions that can be used on DATE columns are listed in the following definitions:

add_months(x,y) Returns a date corresponding to date x plus y months.
last_day(x) Returns the date of the last day of the month that contains date x.
months_between(x,y) Returns a number of months between y and x as produced by y-x. Can return a decimal value.
new_time(x,y,z) Returns the current date and time for date x in time zone y as it would be in time zone z.

Why use functions at all? The functions available in Oracle are highly useful for executing well-defined operations on data in a table or constant values in an easy way. For example, if the user were working with a scientific organization to produce a report of data for that organization, the user may want to use some of the math functions available in Oracle. Rather than selecting data from a table and performing standard mathematical calculations using a scientific calculator, the user may instead execute the functions on that data and produce the report cleanly, in one step. The use of functions in Oracle often saves time and energy.

Exercises

  1. Identify some of the character, number, and date functions available in SQL. What are two functions that allow the user to transform column values regardless of the datatype?
  2. What are other types of other functions that perform operations on columns of specific datatypes?

Using Functions in SELECT Statements

The previous section introduced the many functions available in Oracle. The definitions in that section should suffice for reference; however, there is no substitute for actual usage. This section will show the functions listed in action. The first example below details use of the decode( ) function. Assume that the user is selecting data from the EMP table. The data in the SEX column of EMP is populated with M for male and F for female. Instead of displaying a letter, the user wants to write out the full word for each sex.

SELECT empid, lastname, firstname,
DECODE(sex,’M’,’MALE’,’F’,’FEMALE’) sex
FROM emp
ORDER BY empid DESC;


EMPID LASTNAME FIRSTNAME SEX
-----
-------- --------- ------
60403 HARPER   ROD        MALE
49539 QIAN     LEE        FEMALE
49392 SPANKY   STACY      FEMALE
39334 SMITH    GINA       FEMALE
02039 WALLA    RAJENDRA    MALE

Notice that the decode( ) command has five parameters, the first of which is the name of the column. This column must always be present. The second parameter corresponds to the value that could be found in the SEX column, followed by the value that decode( ) should return if SEX in this row is equal to ‘M’. The next set of parameters answers the question of what decode( ) should return if the value in the column is ‘F’. This matching of column values with appropriate return values can continue until the user has identified all cases he or she would like decode( ) to handle. The last parameter according to the definition of decode( ) is used for the default return value. No default value was specified in this example, as the default return value is optional.

The next section will present examples of text or character function examples. The first of these examples is for rpad( ) and lpad( ). These two functions can be used to place additional filler characters on the right or left side of data in a column out to a specified column width.

SELECT empid, lastname, firstname,
RPAD(DECODE(sex,’M’,’MALE’,’F’,’FEMALE’),10,’-’) sex
FROM emp
ORDER BY empid DESC;

EMPID  LASTNAME FIRSTNAME SEX
-----
-------- --------- ----------
60403  HARPER    ROD       MALE------
49539  QIAN      LEE       FEMALE----
49392  SPANKY    STACY     FEMALE----
39334  SMITH     GINA      FEMALE----
02039  WALLA     RAJENDRA   MALE------

An interesting property of Oracle SQL functions is displayed in this example. The output from one SQL function can be used as input for another. In this case, the rpad( ) operation will pad the decoded SEX column out to ten characters with dashes. If the lpad( ) operation had been used instead, the result would have been as follows:

SELECT empid, lastname, firstname,
LPAD(DECODE(sex,’M’,’MALE’,’F’,’FEMALE’),10,’-’) sex
FROM emp
ORDER BY empid DESC;

EMPID LASTNAME FIRSTNAME SEX
-----
-------- --------- ----------
60403 HARPER    ROD      ------MALE
49539 QIAN      LEE      ----FEMALE
49392 SPANKY    STACY    ----FEMALE
39334 SMITH     GINA     ----FEMALE
02039 WALLA     RAJENDRA ------MALE

Some of the simpler character functions are next. Two straightforward examples of SQL queries are sometimes referred to as "case translators," because they perform a simple translation of case based on the text string passed.

SELECT LOWER(title) TITLE_NOQUOTE,
UPPER(artist) ARTIST1, INITCAP(artist) ARTIST2
FROM SONGS;

TITLE_NOQUOTE        ARTIST1   ARTIST2
-------------------  --------- ---------
"happy birthday"     ANONYMOUS Anonymous
"diamonds and rust"  ANONYMOUS Anonymous
"amazing grace"      ANONYMOUS Anonymous

Another straightforward and surprisingly useful character function is the length( ) function, which returns the length of a text string.

SELECT title, LENGTH(title) LENGTH
FROM SONGS;

TITLE                 LENGTH
-------------------  ------
"HAPPY BIRTHDAY"        16
"DIAMONDS AND RUST"     19
"AMAZING GRACE"         15

Note one interesting thing happening in this query—spaces and special characters are all counted as part of the length! This is an important facet to remember when dealing with text strings in Oracle. Blank spaces count as part of the length of the column value. Another extraordinarily useful function related to character strings is the substr( ) function. This function is commonly used to extract data from a longer text string. Its syntax, though slightly more difficult to understand than some of the other commands in Oracle, is definitely worth mastering. substr( ) takes as its first parameter the full text string to be searched. The second parameter contains an integer that designates the character number at which the substring should begin. The third parameter is optional and specifies how many characters to the right of the start of the substring will be included in the substring. Optionally, the final parameter in the substr( ) call could have been left off, producing the following output:

SELECT title, SUBSTR(title,5,5) CHARS
FROM SONGS;

TITLE                 CHARS
------------------- -----
"HAPPY BIRTHDAY"      Y BIR
"DIAMONDS AND RUST"    ONDS
"AMAZING GRACE"       ING G

SELECT title, SUBSTR(title,5) CHARACTERS
FROM SONGS;

TITLE                CHARACTERS
------------------- ---------------
"HAPPY BIRTHDAY"    Y BIRTHDAY"
"DIAMONDS AND RUST" ONDS AND RUST"
"AMAZING GRACE"     ING GRACE"

The number or math functions are frequently used in scientific applications, and as such may not be as familiar to developers with less mathematical experience. It is beyond the scope of this chapter to discuss the meaning of the math functions, particularly the logarithmic functions. However, use of those functions will be demonstrated by the SQL statements displayed in this section. The first function detailed here is the abs( ) or absolute value function.

SELECT ABS(25), ABS(-12) FROM DUAL;

ABS(25) ABS(-12)
------- --------
25      12

The next single-value function that will be covered in this section is the ceil( ) function.

SELECT CEIL(123.323), CEIL(45), CEIL(-392), CEIL(-1.12) FROM DUAL;

CEIL(123.323) CEIL(45) CEIL(-392) CEIL(-1.12)
------------- -------- ---------- -----------
124           45        -392        -1

The next single-value function is the floor( ) function. The floor( ) is the opposite of ceil( ).

SELECT FLOOR(123.323), FLOOR(45), FLOOR(-392), FLOOR(-1.12)
FROM DUAL;

FLOOR(123.323) FLOOR(45) FLOOR(-392) FLOOR(-1.12)
-------------  --------  ----------  -----------
123            45         -392         -2

The next function covered in this section is related to long division. The function is called mod( ), and it returns the remainder amount for a number and its divisor.

SELECT MOD(12,3), MOD(55,4) FROM DUAL;

MOD(12,3) MOD(55,4)

--------- ---------
0              3

After that, the user should look at round( ). This important function allows the user to round a number off to a specified value of precision.

SELECT ROUND(123.323,2), ROUND(45,1), ROUND(-392,-1), ROUND(-1.12,0)
FROM DUAL;

ROUND(123.323,2) ROUND(45,1)  ROUND(-392,1)   ROUND(-1.12,0)
---------------- --------     -------------   -----------
123.32             45              -390               -1

The next function is called sign( ). It assists in identifying a number to be positive or negative. If the number passed is positive, sign( ) returns 1, if the number is negative, sign( ) returns –1.

SELECT SIGN(-1933), SIGN(55), SIGN(0) FROM DUAL;

SIGN(-1933) SIGN(55)    SIGN(0)
----------  ----------- -------
-1           1            0

The next example is the sqrt( ) function. It is used to derive the square root for a number.

SELECT SQRT(34), SQRT(9) FROM DUAL;

SQRT(34)   SQRT(9)
---------  ----------
5.8309519  3

The next single-value number function is called trunc( ). Similar to round( ), trunc( ) truncates a value passed into it according to the precision that is passed in as well.

SELECT TRUNC(123.232,2), TRUNC(-45,1), TRUNC(392,-1), TRUNC(5,0) FROM DUAL;

TRUNC(123.232,2) TRUNC(-45,1) TRUNC(392,-1) TRUNC(5,0)
---------------- ------------ ------------- ----------
123.23           -45           390             5

The final single-row operation that will be covered in this section is the vsize( ) function. This function is not strictly for numeric datatypes, either. The vsize( ) function gives the size in bytes of any value for a text, number, date, or ROWID, and other columns.

SELECT VSIZE(384838), VSIZE('ORANGE_TABBY'), VSIZE(sysdate) FROM DUAL;

VSIZE(384838) VSIZE('ORANGE_TABBY') VSIZE(SYSDATE)
------------- --------------------- --------------
4             12                      8

Exercises

  1. What is the purpose of the nvl( ) function? What datatypes does it accept? What is the purpose of a decode( ) statement? What datatypes does it accept?
  2. Name some character functions? Can two functions be combined? Why or why not?
  3. Name some single-value number functions. What types of applications are these functions typically used in?
  4. What function is used to determine the size in bytes of a given value or column?

Date Functions

There are several date functions in the Oracle database. The syntax of these functions has already been presented. This section will discuss each function is more detail and present examples of their usage. But first, the user should understand how dates are stored in Oracle. The Oracle database stores dates in an integer format, storing the date as the number of days from the beginning of the Julian calendar. This method allows for easy format changes and inherent millennium compliance.

The first function is the add_months( ) function. This function takes as input a date and a number of months to be added. Oracle then returns the new date, which is the old date plus the number of months.

SELECT ADD_MONTHS(‘15-MAR-98’,26)
FROM DUAL;

ADD_MONTHS(‘15
--------------
15-MAY-02

The next date function, last_day( ), helps to determine the date for the last date in the month for the date given.

SELECT LAST_DAY(‘15-MAR-99’) FROM DUAL;

LAST_DAY(‘15-M
--------------
31-MAR-99

The next date function determines the number of months between two different dates given. The name of the function is months_between( ). The syntax of this command is tricky, so it will be presented here. The syntax of this command is months_between(y,x), and the return value for this function is y-x.

SELECT MONTHS_BETWEEN(‘15-MAR-98’,‘26-JUN-97’) FROM DUAL;

MONTHS_BETWEEN
--------------
8.6451613

The next and last example of a date function is the new_time( ) function. This procedure accepts three parameters, the first being a date and time, the second being the time zone the first parameter belongs in, and the last parameter being the time zone the user would like to convert to. Each time zone is abbreviated in the following way: XST or XDT, where S or D stands for standard or daylight savings time, and where X stands for the first letter of the time zone (such as Atlantic, Bering, central, eastern, Hawaii, Mountain, Newfoundland, Pacific, or Yukon). There are two exceptions: Greenwich mean time is indicated by GMT, while Newfoundland standard time does not use daylight savings. An example of the usage of new_time( ) is as follows in this example. Another useful fact to know when using new_time( ) is that the Oracle date format shown is not the only one available. Dates in Oracle are stored as numbers from the beginning of the Julian calendar (December 31, 4713 B.C.E.), down to the second. So far, none of the queries used to demonstrate the date functions have required that much precision, but the following example will. In order to demonstrate the full capability of Oracle in the new_time( ) function, the NLS date format can be changed to display the full date and time for the query. The example below demonstrates both the use of nls_date_format( ) to change the date format and the new_time( ) function to convert a time stamp to a new time zone:

ALTER SESSION
SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’;

SELECT NEW_TIME(‘15-MAR-1998 14:35:00’,‘AST’,‘GMT’)
FROM DUAL;

NEW_TIME(‘15-MAR-199
--------------------
15-MAR-1998 18:35:00

Exercises

  1. What is nls_date_format? How is it set? How is it used?
  2. Which date functions described in this section return information in the DATE datatype? Which one returns information in a datatype other than DATE?
  3. How are dates stored in Oracle?

Conversion Functions

Still other functions are designed to convert columns of one datatype to another type. As these functions are simply designed to change the datatype of the column value, not actually modify the data itself, the functions are called conversion functions. There are several different conversion functions available in the Oracle database. The ones available appear in the following list:

to_char(x) Converts noncharacter value x to character
to_number(x) Converts nonnumeric value x to number
to_date(x[,y]) Converts nondate value x to date, using format specified by y
to_multi_byte(x) Converts single-byte character string x to multibyte characters according to national language standards
to_single_byte(x) Converts multibyte character string x to single-byte characters according to national language standards
chartorowid(x) Converts string of characters x into an Oracle ROWID
rowidtochar(x) Converts string of characters x into an Oracle ROWID
hextoraw(x) Converts hexadecimal (base-16) value x into raw (binary) format
rawtohex(x) Converts raw (binary) value x in to hexadecimal (base-16) format
convert(x[,y[,z]]) Executes a conversion of alphanumeric string x from the current character set optionally specified as z to the one specified by y
translate(x,y,z) Executes a simple value conversion for character or numeric string x into something else based on the conversion factors y and z

The following text illustrates the most commonly used procedures for converting data in action. These are the to_char( ), to_number( ), and to_date( ) functions. The first one demonstrated is the to_char( ) procedure. In the example of new_time( ), the date function described earlier, the alter session set nls_date_format( ) statement was used to demonstrate the full capabilities both of Oracle in storing date information and Oracle in converting dates and times from one time zone to another. That exercise could have been accomplished with the use of the to_char( ) conversion function as well, however. Using to_char( ) in this manner saves the user from converting nls_date_format, which, once executed, is in effect for the rest of the user’s session, or until the user executes another alter session set nls_date_format statement. Rather than using this method, the user may want to opt for a less permanent option offered by the to_char( ) function.

SELECT TO_CHAR(NEW_TIME(TO_DATE(‘15-MAR-1998 14:35:00’,
‘DD-MON-YYYY HH24:MI:SS’),‘AST’,‘GMT’)
FROM DUAL;

NEXT_DAY(‘15-MAR-9
------------------
15-MAR-98 18:35:00

Note that this example also uses the to_date( ) function, another conversion function in the list to be discussed. The to_date( ) function is very useful for converting numbers, and especially character strings, into properly formatted DATE fields.

SELECT TO_NUMBER(‘49583’) FROM DUAL;

TO_NUMBER(‘49583’)
------------------
49583

Although there does not appear to be much difference between the output of this query and the string that was passed, the main difference is the underlying datatype. Even so, Oracle is actually intelligent enough to convert a character string consisting of all numbers before performing an arithmetic operation using two values of two different datatypes.

SELECT ‘49583’ + 34 FROM DUAL;

‘49583’+34
----------
49617

Exercises

  1. Identify some conversion functions. Which conversion functions are commonly used?
  2. What is nls_date_format? How is it used?

Chapter Summary

This chapter provides an introduction to using Oracle by demonstrating basic techniques for use of select statements. The areas discussed in this chapter are selecting row data from tables using the select from statement, limiting the rows selected with the where clause of the select from statement, and using the single-row functions available in Oracle to manipulate selected data into other values, formats, or meanings. This chapter is the cornerstone for all other usage in Oracle, as well as for passing the OCP Exam 1. Material covered in this chapter comprises 17 percent of test content on OCP Exam 1.

The first area covered in this chapter is information about selecting data from Oracle. The most common manipulation of data in the Oracle database is to select it, and the means by which to select data from Oracle is the select statement. The select statement has two basic parts, the select clause and the from clause. The select clause identifies the column of the table that the user would like to view contents of. The from clause identifies the table in which the data selected is stored. In this chapter, data from only one table at a time was considered. In the next chapter, the concept of pulling or "joining" data from multiple tables will be considered.

Often, users will want to perform calculations involving the data selected from a table. Oracle allows for basic, intermediate, and complex manipulation of data selected from a database table through the use of standard arithmetic notation such as plus (+), minus(-), multiply (*), and divide (/). These operators can be used to perform math calculations on the data selected from a table or as math operators on numbers in calculator-like fashion. In order to perform calculations on numbers that are not selected from any table, the user must utilize the DUAL table. DUAL is simply an empty table with one column that fulfills the syntactic requirements of SQL statements like select, which need a table name in the from clause in order to work.

When manipulating data from a table, the user must remember to handle cases when column data for a particular row is nonexistent. Nonexistent column data in a table row is often referred to as being NULL. These NULL values can be viewed either as blank space, by default, or the user can account for the appearance of null data by using a special function that will substitute null fields with a data value. The name of this special function is nvl( ). The nvl( ) function takes two parameters: the first is the column or value to be investigated for being null, and the second is the default value nvl( ) will substitute if the column or value is null. The nvl( ) function operates on all sorts of datatypes, including CHAR, VARCHAR2, NUMBER, and DATE.

When performing special operations on columns in a select statement, Oracle often displays hard-to-read headings for the column name because Oracle draws the column name directly from the select clause of the select statement. The user can avoid this problem by giving a column alias for Oracle to use instead. For example, the following select may produce a cryptic column heading: select nvl(empid,’00000’) EMPID …, while a column alias would allow Oracle to provide a more meaningful heading: select nvl(empid,’00000’) EMPID …. Column aliases are specified as character strings following the function and/or column name the alias will substitute. Be sure to include white space between the function and/or column name and the alias.

Concluding the introduction to SQL select statements, the use of concatenation and entering the actual statements was discussed. Columns can be concatenated together using the double-pipe (||) delimiter. This operation is useful for placing information closer together, or to use special characters to separate the output, such as commas or others. The SQL statement itself is entered using the SQL*Plus tool. If a user makes an error while typing in the line of SQL, the user can use the backspace key to erase characters until he or she reaches the mistake; however, this approach only works if the user is still on the same line in the SQL entry buffer. If the user has already proceeded to another line, or if he or she has already tried to execute the command, then he or she can type in the number corresponding to the line to be corrected to select that line for editing. Then, the user can type in the change command, abbreviated c/old/new, where old is the existing version of the string containing the mistake, and new is the correction. If this all sounds complicated, the user can simply type edit, or ed from the prompt in SQL*Plus, and Oracle will immediately bring up the user’s favorite text editor. The text editor used here can be specified or changed with the define_editor="youreditor" command.

The number or order of selected rows from the database can be limited with various options. The first option discussed is order by. This is a clause that allows the user to specify two things—the first is a column on which to list the data in order, the second is whether Oracle should use ascending or descending order. Usage of the order by clause can make output from an Oracle select statement more readable, since there is no guarantee that the data in Oracle will be stored in any particular order.

The second means of limiting selected output is the where clause. Proper use of this clause is key to successful usage of Oracle and SQL. In the where clause, the user can specify one or more comparison criteria that must be met by the data in a table in order for Oracle to select the row. A comparison consists of two elements that are compared using a comparison operator, which may consist of a logic operator such as equality (=), inequality (<>,!=, or ^=), less than (<) or greater than (>), or a combination of less or greater than and equality. Alternately, the user can also utilize special comparison operators that enable for pattern matches using like %, range scans using between x and y, or fuzzy logic with the soundex(x) = soundex(y) statement. In addition, one or more comparison operations may be specified in the where clause, joined together with and or the or operator, or preceded by not.

Data selected in Oracle can be modified with the use of several functions available in Oracle. These functions may work on many different types of data, as is the case with nvl( ) other functions called decode( ), greatest( ), or least( ). Alternately, their use may be limited to a particular datatype. These functions may be divided into categories based on the types of data they can handle. Typically, the functions are categorized into text or character functions, math or number functions, and date functions.

Usage of Oracle built-in functions enables the user to perform many different operations. In general, the use of a function comprises specifying the name of the function and the passing of parameters to the function. For example, to change the characters in a text string requires identifying the function that performs this task, followed by passing the function a value. To perform the task in this example, the following function call could be made: upper(lowercase).

The chapter also detailed the usage of all the functions available in Oracle, and provided examples for most of them. For brevity sake, they will not reappear here; however, it should be noted that many of the functions can be used together and in conjunction with the multitype functions like decode( ). For example, the usage of decode(sqrt(x), 4, ‘HARVEY’,5,’JILL’,’BRAD’) is permitted. In essence, this functionality allows the user to incorporate the output from one function as input for another. An entire set of conversion functions are also available to change datatypes for values, or to create ciphers, or even to change the character sets used in order to move data onto different machines. Again, for the sake of brevity, the functions themselves are not listed here; however, it should be stated that the conversion functions can be used in conjunction with many of the other functions already named.

Two-minute Drill

Data is retrieved from Oracle using select statements.
Syntax for a select statement consists of select …from…;.
When entering a select statement from the prompt using SQL*Plus, a semicolon(;) must be used to end the statement.
Arithmetic operations can be used to perform math operations on data selected from a table, or on numbers using the DUAL table.
The DUAL table is an empty table used to fulfill the syntactic requirements of SQL select statements.
Values in columns for particular rows may be empty, or null.
If a column contains the NULL value, the user can use the nvl( ) function to return meaningful information instead of an empty field.
Aliases can be used in place of the actual column name or to replace the appearance of the function name in the header.
Output from two columns can be concatenated together using a double-pipe (||).
SQL commands can be entered directly into SQL*Plus on the command line.
If a mistake is made, the change (c/old/new) command is used.
Alternately, the edit (ed) command can be used to make changes in the user’s favorite text editor.
The user can specify a favorite text editor by issuing the define_editor command at the prompt.
The order by clause in a select statement is a useful clause to incorporate sort order into the output of the file.
Sort orders that can be used are ascending or descending. Order is determined by the column identified by the order by clause.
The where clause is used in SQL queries to limit the data returned by the query.
The where clauses contain comparison operations that determine whether a row will be returned by a query.
There are several logical comparison operations, including =, >, >=, <, <=, <>, !=, ^=.
In addition to the logical operations, there is a comparison operation for pattern matching called like. The % character is used to designate wildcards.
There is also a range operation called between.
There is also a fuzzy logic operation called soundex.
Finally, the where clause can contain one or more comparison operations linked together by use of and, or, and preceded by not.
Several SQL functions exist in Oracle.
SQL functions are broken down into character functions, number functions, and date functions.
A few functions are usable on many different types of data.
There are also several conversion functions available for transforming data from text to numeric datatypes and back, numbers to dates and back, text to ROWID and back, etc.