(pn)I

Unit I

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

Chapter 1

Selecting Data from Oracle

Selecting Rows *

Writing SELECT Statements *

Exercises *

Performing Arithmetic Equations *

Performing Arithmetic on Numeric Expressions *

Exercises *

Handling NULL Values *

Exercises *

Changing Column Headings with Column Aliases *

Exercises *

Putting Columns Together with Concatenation *

Exercises *

Editing SQL Queries Within SQL*Plus *

Exercises *

Limiting and Refining 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 *

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

Selecting rows
Limiting and refining selected output
Using single-row functions

The first exam in the OCP series covers 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 you 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, and single-row functions. 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
Creating column headings with column aliases
Putting columns together with concatenation
Editing SQL queries within SQL*Plus

Use of Oracle for many developers, designers, DBAs, and power users 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 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 following examples show how you might begin a session with Oracle from a command line operating system such as UNIX. From Windows, you may instead click on Start | Programs | Oracle for Windows | SQL*Plus or double-click on the SQL*Plus icon on your desktop if one appears there.

$>> sqlplus jason/athena

$>> sqlplus /

A session is an interactive runtime environment where you enter commands to retrieve data and Oracle performs a series of activities to obtain the data you asked for. Think of it as a conversation, which in turn implies language. You communicate with Oracle using 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 you to specify the types of things you want to see happen in terms of the results you want. Contrast this approach to other languages you may have heard about or programmed in, such as C++ or COBOL, which 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 you imagination, but the point of the example is clear—other languages define a process, while SQL defines the result.

Writing SELECT Statements

The most common type of SQL statement executed in most database environments is the query, or select statement. Select statements pull requested data from tables in a database. 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 Chapter 3. You can issue a simple select statement that is designed to pull all data from the table shown in Table 1-1. The following code block demonstrates SQL*Plus in action. You will see a select statement used to obtain data from a table called EMP, owned by a user called HRAPP. Sometimes Oracle developers and DBAs refer to database objects like tables being part of something called a "schema." A schema is a logical grouping of database objects like tables according to the owner. Thus, the EMP table you will see is part of the HRAPP schema.

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

SQL*Plus: Release 8.1.0.0.0 - Production on Tue Feb 03 18:53:11 199
Copyright (c) Oracle Corporation 1979, 1998. All rights reserved.
Connected to: Oracle8 Release 8.1.0.0.0
With the distributed and replication options
PL/SQL Release 8.1.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 first part containing the copyright information in this code block is a "welcome" message from SQL*Plus. If you wanted, you could suppress this information in your call to SQL*Plus from the operating system command line (such as UNIX) by entering "sqlplus –s" and pressing enter, where the –s extension indicates SQL*Plus should run in silent mode. The line in bold in this excerpt 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 you did not tell Oracle how to retrieve the data, you simply expressed the data you wanted using SQL syntax and Oracle returned it. 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 the select clause and the from clause. A select clause contains the list of columns or expressions containing data you want to see. The first statement used a wildcard (*) character, which indicates to Oracle that you want to view data from every column in the table. The from clause tells Oracle what database table to pull the information from. Often, 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, as we did in this next example with a schema.tablename notation as well:

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

For review, the statement issued in the code block gets its information from the table called 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 you. 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

What is a select statement? Name the two required components of a select statement.

How should you end a select statement in SQL*Plus?

What is a schema?

Performing Arithmetic Equations

In addition to simple selection of data from a table, Oracle allows you to perform different types of activities using the data. All basic arithmetic operations are available in Oracle. The operators used to denote arithmetic in Oracle SQL are the same as in daily use.

Assume, for example, that you are 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. Oracle makes this sort of thing easy with the use of arithmetic expressions, as shown below:

SELECT empid, lastname, 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

Performing Arithmetic on Numeric Expressions

Select statements in Oracle require you specify columns or expressions following the select keyword and a table name after the from keyword. However, you may not always want to perform arithmetic calculations on data from a table. For example, say you simply want to add two fixed values together. Every select statement must have a from clause, but since you are specifying fixed values, you don’t want Oracle to pull data from a real table. So why not pull data from a fake one? A special table called DUAL can be used in this query to fulfill the from clause requirement. Execute a select * from DUAL and see for yourself there is no data stored here. Now issue the following statement, and see results from the DUAL table:

SELECT 64+36
FROM DUAL;

64+36
-----
100

There is no meaningful data actually in DUAL; it simply exists as a SQL construct to support the requirement of a table specification in the from clause. The DUAL table contains only one column called DUMMY and one row with a value, "X."

TIP: 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, she can include the values, operations, and the from DUAL clause.

Exercises

How can you perform arithmetic on selected columns in Oracle?

What is the DUAL table? Why is it used?

How do you 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. 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 NULL is listed in the SPOUSE column of the following code block:

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 you will not want to see nothing. You may want to substitute a value in place of nothing. Oracle provides this functionality with a special function called nvl( ). Assume that you do not want to see blank spaces for spouse information. Instead, you want the output of the query to contain the word "unmarried." The query in the next code block illustrates how you can issue the query against Oracle to obtain the desired result.

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

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, but remember that the "value if NULL" argument must be the same datatype as the column specified. Basic syntax for nvl( ) is as follows:

NVL(column_name, value_if_null)

Exercises

What does NULL mean in the context of Oracle SQL?

What is the nvl( ) function? How is it used?

Changing Column Headings with Column Aliases

When Oracle returns data to you, Oracle creates headings for each column so that you know what the data is. Oracle bases the headings it creates on the name of the column passed to Oracle in 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

By default, Oracle reprints the column name exactly as it was included in the select statement, including functions if there are any. Unfortunately, this method usually leaves you with a bad description of the column data, compounded by the fact that Oracle truncates the expression to fit a certain column length corresponding to the datatype of the column returned. Fortunately, 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 you when the select statement is issued. This feature gives you 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, the SPOUSE column is again named SPOUSE, even with the nvl( ) operation performed on it. 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. Alternatively, you can issue the as keyword to denote the alias. The SPOUSE column with the nvl( ) operation is an example of using the as keyword 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

Column aliases are useful for adding meaningful headings to output from SQL queries. As shown, 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. Here’s another example:

SELECT column_with_or_without_operation alias, …;

SELECT column_with_or_without_operation AS alias, …;

Exercises

What is a column alias? For what situations might column aliases be useful?

What are two ways to define aliases for columns?

Putting Columns Together with Concatenation

Changing a column heading 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 glued together to produce more interesting or readable output. The method used to merge the output of two columns into one is called concatenation. The concatenation operator is two pipe characters put together, or ||. You can also use the concat( ) operation, passing it the two column names. In the following example, you change the name output to 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

SELECT empid, concat(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

By using the concatenation operator in conjunction with a text string enclosed in single quotes, the output of two or more columns became one column to express new meaning. For good measure, the use of column aliases is recommended in order to make the name of the concatenated columns more meaningful.

Exercises

What is column concatenation?

What special character sequence is used to concatenate columns?

Editing SQL Queries Within SQL*Plus

The SQL*Plus work environment is very much a fair-weather coworker. It works well when you don’t make mistakes, but it is unforgiving to the fat-fingered once you have pressed enter to move to the next input line. So far, this limitation of the SQL command line hasn’t presented much difficulty. However, as the queries you can write get more and more complicated, you 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
*
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, you issued a select statement containing a typographical error, sppuse. Oracle notices the error and alerts you to it with ORA-00904. To change it, you first reference the line number containing the mistake, in this case with the number 2. Oracle indicates the current version of the SQL statement. Then you issue 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. You can then execute the SQL statement, using the slash (/) command. Other errors that may be produced include:

ORA-00923: FROM Keyword Not Found Where Expected

This error indicates that the from keyword was not included or was misspelled. Here is another:

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

In any case, the method used to correct the typing problem is to first type the line number containing the error and then use the change command using 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.

Oracle makes provisions for you to utilize their favorite text editor to edit the statement created in afiedt.buf, the file into which SQL*Plus stores the most recently executed SQL statement. You simply type edit (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 use Notepad. To change the text editor used, issue the define _editor=‘youreditor’ statement from the SQL*Plus 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 you know 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 you 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. Do this if you encounter problems where Oracle says it encountered an invalid character (the semicolon) in your script.

It is possible to write your entire query in a text editor first and then load it into SQL*Plus. If you do this, be sure you save the script with a .sql extension so that SQL*Plus can read it easily. Later, when you load the file into SQL*Plus, three commands are available for this use. The first is get. The get command opens the text file specified and places it in afiedt.buf. Once loaded, you can execute the command using the slash (/) command. Or, you can simply load SQL statements from the file into afiedt.buf and execute in one step using the @ command.

SQL*Plus: Release 8.1.0.0.0 - Production on Tue Feb 03 18:53:11 1999
Copyright (c) Oracle Corporation 1979, 1998. All rights reserved.
Connected to Oracle8 Release 8.1.0.0.0
With the distributed and replication options
PL/SQL Release 8.1.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 45000
02039 WALLA RAJENDRA 60000
49392 SPANKY STACY 100000

5 rows selected;

Notice that the .sql extension was left off the end of the filename in the line with the get command. SQL*Plus assumes that all scripts containing SQL statements will have the .sql extension, so it 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 the example, 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. 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

What two mechanisms are available to enter and modify SQL statements within SQL*Plus?

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?

What command is used to define a text editor for SQL*Plus to use?

Limiting and Refining Selected Output

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

The order by clause
The where clause

Obtaining all output from a table is great, but usually you must be more selective in choosing output. 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 Oracle8 will store up to 512 petabytes of data. Needless to say, manipulating vast amounts of data like that requires you to be careful. Always ask for exactly what you want, and no more.

The ORDER BY Clause

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. That’s fine for the database, but people like order! Oracle allows you to place order on output from select statements using the order by clause, 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. The default value is asc, and the output for desc as shown here:

SQL> SELECT *
2> FROM emp
3> 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

Order by can impose sort order on one or many columns in 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. You can even 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 you issue 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 DESC;

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

SELECT lastname, empid FROM emp ORDER BY 2 DESC;

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

Exercises

How can a user put row data returned from a select statement in order? What are the various sort orders that can be used with this option?

What are the two ways to specify the column on which sort order should be defined?

The WHERE Clause

The where clause in Oracle select statements is where the really interesting things begin. This important clause in select statements allows you 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 = 49392;

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

Assuming the EMPID column contains all unique values, 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 = 49392. 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 wildcards (% or _) 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, numbers, and characters that are similar to the following numeric comparison: Y "is between" X and Z.
in	A special comparison that allows you 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).

Every comparison between two values in Oracle boils down to one or more of these operations. 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 yx or y	One comparison in x or y must be true.
not x	The logical opposite of x.

Exercises

What is a where clause? On what principle does this clause operate to determine which data is selected?

What are some operations available to assist in the purpose of comparison? What are some operations that allow you 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

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. One commonly used example of this type of function is decode( ). The decode( ) function works on the same principle as an if-then-else statement works in many common programming languages, including PL/SQL.

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

The decode( ) function allows for powerful transformation of data from one value to another. 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. Several functions exist in Oracle 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. Some are as follows.

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. These operations are commonly referred to as math or number operations. The functions falling into this category are listed next. 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 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, 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 discussed here is the set of list functions. These functions are actually used for many different datatypes, including text, numeric, and date.

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 the DATE datatype. The functions that perform operations on dates are known as date functions. 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 you calculated simple arithmetic in an earlier part of the chapter using the DUAL table, so too can you execute a select statement using sysdate to produce today’s date:

SELECT sysdate FROM DUAL;

SYSDATE
---------
15-MAY-99

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.

The functions available in Oracle are highly useful for executing well-defined operations on data in a table or constant values and often save time and energy. Make sure you understand these functions for OCP.

Exercises

Identify some of the character, number, and date functions available in SQL. What are two functions that allow you to transform column values regardless of the datatype?

What are other types of 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 details use of the decode( ) function. Assume that you select 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 following code block lets you 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

The decode( ) command has five variables, the first of which is the name of the column. This column must always be present. The second variable 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 variables 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 you have identified all cases you would like decode( ) to handle. The last variable according to the definition of decode( ) is used for the default return value, which is optional.

Now look at examples of text or character function examples. The first of these examples is for rpad( ) and lpad( ). As shown in the following code, 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------

The output from one SQL function can be used as input for another, as demonstrated here. 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 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 double quotes are all counted as part of the length! 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. The substr( ) function takes as its first variable the full text string to be searched. The second variable 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. Observe the following output to understand the effects of omitting the third parameter:

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. The first function detailed here is the abs( ) or absolute value function that calculates how far away from zero the parameter passed lies on the number line:

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, which automatically rounds the number passed as its parameter up to the next higher integer:

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( ), rounding the value passed down to the next highest integer:

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 or modulus 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, you should look at round( ). This important function allows you to round a number off to a specified 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, and if the number is negative, sign( ) returns –1. If the number is zero, sign( ) returns zero:

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 text, number, date, 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

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?

Name some character functions? Can two functions be combined? Why or why not?

Name some single-value number functions. What types of applications are these functions typically used in?

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 in more detail and present examples of their usage. The Oracle database stores dates as integers, representing the number of days since 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-99',26)
FROM DUAL;

ADD_MONTHS('15
--------------
15-MAY-01

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-00') FROM DUAL;

LAST_DAY('15-M
--------------
31-MAR-00

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-99','26-JUN-98') FROM DUAL;

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

The last example of a date function is new_time( ). It 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 you 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. 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 following example demonstrates both the use of nls_date_format to change the date format and the new_time( ) function to convert a timestamp to a new time zone:

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

SELECT NEW_TIME('15-MAR-1999 14:35:00','AST','GMT')
FROM DUAL;

NEW_TIME('15-MAR-199
--------------------
15-MAR-1999 18:35:00

Exercises

What is nls_date_format? How is it set? How is it used?

Which date functions described in this section return information in the DATE datatype? Which one returns information in a datatype other than DATE?

How are dates stored in Oracle?

Conversion Functions

Still other functions are designed to convert columns of one datatype to another type. These functions do not actually modify the data itself; they just return the converted value. Several different conversion functions are available in the Oracle database, as listed below:

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 into 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( ) function. 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 you from converting nls_date_format, which, once executed, is in effect for the rest of your session, or until you execute another alter session set nls_date_format statement. Rather than using this method, you may want to opt for a less permanent option offered by the to_char( ) function, as shown below:

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

NEXT_DAY('15-MAR-9
------------------
15-MAR-99 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. The next function to consider is to_number( ), which converts text or date information into a number:

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, as shown in the following listing:

SELECT '49583' + 34 FROM DUAL;

'49583'+34
----------
49617

Exercises

Identify some conversion functions. Which conversion functions are commonly used?

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 you 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 is 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. 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, you must utilize the DUAL table. DUAL is simply a 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, you 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 you 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. You 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(LASTNAME,'DOE') …, while a column alias would allow Oracle to provide a more meaningful heading: select nvl(LASTNAME,'DOE') LASTNAME …. 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 two pipe (||) characters. This operation is useful for making two columns into one, or to use special characters, such as commas or others, to separate the output. The SQL statement itself is entered using the SQL*Plus tool. If a user makes an error while typing in the line of SQL, you can use the backspace key to erase characters until you reach the mistake; however, this approach only works if you are still on the same line in the SQL entry buffer. If you have already proceeded to another line, or if you tried to execute the command, then you can type in the number corresponding to the line to be corrected to select that line for editing. Then, you 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, you can simply type edit, or ed, from the prompt in SQL*Plus, and Oracle will immediately bring up your 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 or refined with various options. The option for refining data discussed is order by. This is a clause that allows you to specify two things—the first is a column on which to list the data in order, and 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 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, you 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. Alternatively, you can also utilize special comparison operators that enable 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( ) functions called decode( ), greatest( ), or least( ). Alternatively, 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 you to perform many different operations. In general, the use of a function comprises specifying the name of the function and the passing of variables 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 you to incorporate the output from one function as input for another. An entire set of conversion functions is 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 a table with one column and one row 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, you 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.

Alternatively, the edit (ed) command can be used to make changes in your favorite text editor.

You 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, abbreviated as asc and desc. 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.