OCP Introduction to Oracle9i: SQL Exam Guide / Edition 1

Chapter 2: Limiting, Sorting, and Manipulating Return Data

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

• Restricting and sorting row data
• Using single-row functions

This chapter will build on the concepts you learned in Chapter 1 with respect to selecting data from Oracle. In this chapter, we'll discuss how to limit the data selected from the Oracle database and how to tell Oracle to return the data in a specific order. We'll also discuss how to use a category of functions called single-row functions to manipulate the results of your queries in a variety of ways. This chapter covers important material comprising 16 percent of the test content of OCP Exam 1.

Restricting and Sorting Row Data

This section will cover the following areas related to restricting and sorting row data:

• Sorting return data with the order by clause
• Limiting return data with 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 Oracle9i will store over 512 petabytes (512 1,024 5 bytes) of data. Of course, this is only a theoretical limit. The real amount of data you can store with Oracle depends on how much disk space you give Oracle to use, but, 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. This section tells you how.

Sorting Return Data with the order by Clause

Notice that Oracle does not return data requested in a particular order on any particular column, either numeric or alphabetical. According to the fundamentals of relational database theory, a table is by definition an unordered set of row data. That's fine for the ivory tower, but it's not always useful in real-world situations. Oracle enables you to order the output from select statements using the order by clause. This clause can impose a sort order on one or more columns in ascending or descending order in each of the columns specified. If more than one expression is specified for a sort order, then Oracle sorts in the order of data appearing in the first expression. If the first expression has duplicates, Oracle sorts in the order of data appearing in the second expression, and so on. The order by clause usually appears last in the Structured Query Language (SQL) statement, and the general syntax for the order by clause is to include both the clause and the column(s) or column alias(es) by which Oracle will order the results, each optionally followed by a special clause defining the direction of the order (asc for ascending and desc for descending). The default value is asc, and here is an example:

SQL> select empno, ename, sal
2 from emp
3 order by ename asc;
EMPNO ENAME SAL
--------- ---------- ---------
7876 ADAMS 1100
7499 ALLEN 1600
7698 BLAKE 2850
7782 CLARK 2450
7902 FORD 3000
7900 JAMES 950
7566 JONES 2975
7839 KING 5000
7654 MARTIN 1250
7934 MILLER 1300
7788 SCOTT 3000
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

An example of sorting output in descending order using the desc keyword is shown here:

SQL> select * from emp
2 order by ename desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ --------- --------- ----- --------- ----- ----- -------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

TIP
When NULL data appears in a column that Oracle is attempting to sort in ascending order, Oracle lists the NULL records at the end of the list. When sorting in descending order, Oracle places the NULL data at the top of the list.

The order by clause can be useful in simple reporting. It can be applied to columns that are NUMBER, text (VARCHAR2 and CHAR), and DATE datatypes. You can even use numbers to positionally indicate the column where Oracle should order the output from a statement. 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 specified in the statement should be used to define the order in the output):

SQL> select empno, ename from emp
2 order by 2 desc;
EMPNO ENAME
------ --------
7521 WARD
7844 TURNER
7369 SMITH
7788 SCOTT
7934 MILLER
7654 MARTIN
7839 KING
7566 JONES
7900 JAMES
7902 FORD
7782 CLARK
7698 BLAKE
7499 ALLEN
7876 ADAMS
SQL> select ename, empno from emp
2 order by 2 desc;
ENAME EMPNO
--------- ------
MILLER 7934
FORD 7902
JAMES 7900
ADAMS 7876
TURNER 7844
KING 7839
SCOTT 7788
CLARK 7782
BLAKE 7698
MARTIN 7654
JONES 7566
WARD 7521
ALLEN 7499
SMITH 7369

TIP
You can also sort by column alias.

Here's an even more complex example:

SQL> select ename, deptno, sal
2 from emp
3 order by 2 asc, 3 desc;
ENAME DEPTNO SAL
---------- --------- ---------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
SCOTT 20 3000
FORD 20 3000
JONES 20 2975
ADAMS 20 1100
SMITH 20 800
BLAKE 30 2850
ALLEN 30 1600
TURNER 30 1500
WARD 30 1250
MARTIN 30 1250
JAMES 30 950

For Review

1. Know how to put row data returned from a select statement in order and know the various sort orders (ascending and descending) that can be used with this option. Know also that Oracle can sort based on multiple columns.

2. Be sure you understand both the positional and named ways to specify the column on which the sort order should be defined.

Exercises

1. Which of the choices below identifies the order by clause that produces the following output?

EMPNO ENAME MGR
--------- ---------- ---------
7369 SMITH 7902
7566 JONES 7839
7782 CLARK 7839
7698 BLAKE 7839
7876 ADAMS 7788
7934 MILLER 7782
7499 ALLEN 7698
7654 MARTIN 7698
7521 WARD 7698
7900 JAMES 7698
7844 TURNER 7698
7788 SCOTT 7566
7902 FORD 7566

A. order by empno asc
B. order by ename desc
C. order by hiredate asc
D. order by mgr desc

2. You are sorting data in a table in your select statement in descending order. The column you are sorting on contains NULL records. Where will the NULL records appear?

A. At the beginning of the list
B. At the end of the list
C. In the middle of the list
D. At the same location they are listed in the unordered table

3. Identify the default sort order used by Oracle when no sort order is specified:
______________________

4. The results from an SQL query are shown here:

DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
20 RESEARCH DALLAS
30 SALES CHICAGO

Which of the following SQL statements could not have produced this output?

A. select deptno, dname, loc from dept order by 2 asc, 1 asc, 3 desc;
B. select deptno, dname, loc from dept order by 3 asc;
C. select deptno, dname, loc from dept order by 2 asc;
D. select deptno, dname, loc from dept order by 2 asc, 3 desc, 1 desc;...