OCP Introduction to Oracle9i: SQL Exam Guide / Edition 1

Hardcover (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $1.99
Usually ships in 1-2 business days
(Save 96%)
Other sellers (Hardcover)
  • All (12) from $1.99   
  • New (2) from $28.64   
  • Used (10) from $1.99   


Prepare to pass the OCP Introduction to Oracle9i SQL exam--a required exam for OCA/OCP DBA certification--using this Oracle Press study guide. You'll get complete coverage of all exam topics followed by practice questions and chapter summaries. The CD-ROM contains hundreds of practice exam questions in an adaptive format.
Read More Show Less

Product Details

  • ISBN-13: 9780072195378
  • Publisher: McGraw-Hill Professional Publishing
  • Publication date: 10/29/2001
  • Series: Oracle Press Series
  • Edition number: 1
  • Pages: 512
  • Product dimensions: 7.30 (w) x 9.30 (h) x 1.47 (d)

Meet the Author

Jason Couchman (New York, NY) is an OCP-certified Oracle DBA and author of Oracle Certified Professional DBA Certification Exam Guide, Oracle Certified Professional Application Developer Exam Guide, Oracle Certified Professional DBO Certifica-tion Exam Guide, and Oracle8 Certified Professional DBA Practice Exams. He has led Oracle database projects with Fortune 500 corporations and presented topics at the North Carolina Oracle User Group. His articles on Internet and intranet development have been published internationally.
Read More Show Less

Read an Excerpt

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;
--------- ---------- ---------
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;
------ --------- --------- ----- --------- ----- ----- -------
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
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;
------ --------
7521 WARD
7369 SMITH
7788 SCOTT
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;
--------- ------
FORD 7902
JAMES 7900
ADAMS 7876
KING 7839
SCOTT 7788
CLARK 7782
BLAKE 7698
JONES 7566
WARD 7521
ALLEN 7499
SMITH 7369
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;
---------- --------- ---------
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.

1. Which of the choices below identifies the order by clause that produces the following output?
--------- ---------- ---------
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:

--------- -------------- -------------

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

Read More Show Less

Table of Contents

Pt. I Preparing for OCP DBA Exam I: Introduction to SQL
1 Overview of Oracle Databases 5
2 Limiting, Sorting, and Manipulating Return Data 55
3 Advanced Data Selection in Oracle 97
4 Subqueries 143
5 Creating Oracle Database Objects 201
6 Manipulating Oracle Data 259
7 Creating Other Database Objects in Oracle 291
8 User Access Control in Oracle 347
Pt. II OCP Oracle9i DBA Practice Exams
9 OCP Exam I: Introduction to SQL 381
Index 465
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)