Home > Store

Oracle SQL by Example, 3rd Edition

Register your product to gain access to bonus material or receive a coupon.

Oracle SQL by Example, 3rd Edition


  • Your Price: $55.99
  • List Price: $69.99
  • We're temporarily out of stock, but order now and we'll send it to you later.



The ideal tutorial and workbook for mastering SQL for Oracle systems.

° Class-tested material and a proven teaching method supported by hundreds of examples and exercises

° Helps readers build their skillset by mastering the industry-standard SQL

° From SQL basics to security to optimization -- the book covers all aspects of SQL for Oracle administrators


  • Copyright 2004
  • Edition: 3rd
  • Book
  • ISBN-10: 0-13-145131-6
  • ISBN-13: 978-0-13-145131-5

The book ... has enough depth for even a seasoned professional to pick up enough tips to pay back the price of the book many times over.—Dr. Paul Dorsey, President Dulcian, Inc., and President, New York Oracle Users Group.

Students find Oracle SQL by Example to be extremely helpful not only to their coursework but also in preparing them for future careers as Oracle DBAs and developers. The layout of the text is conducive to the learning process. It introduces a concept and then reinforces that concept with an exercise.—Betsy Jenaway, Coordinator, Malcomb Operations, and Senior Lecturer College of Management, Lawrence Technological University, Southfield, MI.

The world's #1 hands-on Oracle SQL workbook ... fully updated for Oracle 10g

Crafted for hands-on learning and tested in classrooms worldwide this book illuminates in-depth every Oracle SQL technique you'll need. From the simplest query fundamentals through the newest regular expression database enhancements, you will focus on the tasks that matter most. Hundreds of guided lab exercises will systematically strengthen your expertise in writing effective, high-performance SQL. Along the way, you will acquire an arsenal of useful Oracle SQL knowledge and techniques: an extraordinary library of solutions for your real-world challenges with Oracle SQL.

  • Creating and using tables, views, indexes, and sequences
  • Working in SQL*Plus and iSQL*Plus environments
  • Oracle functions in depth—especially number, character, date, timestamp, interval, time zone, aggregate, and analytical functions
  • All types of joins, including equijoins, outer joins, self joins and ANSI join syntax options
  • Every type of subquery, including correlated and scalar subqueries, set operators, hierarchical queries, flashback queries, data dictionary, object-relational features, regular expressions, and more
  • Common pitfalls to avoid, and solutions for common mistakes
  • Practical performance, security, and architectural solutions
  • Insights, tips, and tricks-of-the-trade from an experienced Oracle professional
  • For every database developer, administrator, designer or architect, regardless of experience!



Here you will find instructions for downloading the sample database used in the exercises and labs in Oracle SQL Interactive Workbook, 3rd Edition, as well as instructions for optionally installing additional example tables.

Sample Database

This self-extracting zip file contains the sample database files you will need to complete the exercises and labs. Take the following steps to download and extract the file:

  1. Download the self-extracting scripts.exe to the C:\Temp folder on your machine.
  2. Open Windows Explorer to C:\Temp and double click on scripts.exe.
  3. The WinZip Self-Extractor will open. Click the Unzip button to extract the files to C:\guest\schemasetup. The folders C:\guest and C:\guest\schemasetup will be created for you automatically if you don't already have them.
  4. The files will be unzipped in the C:\guest\schemasetup directory.
  5. The readme.pdf in the c:\guest\schemasetup directory will provide instructions on how to install (viewing requires the Adobe Acrobat Reader). Please read the instructions carefully. Alternatively, you can also read the text-based version of the readme file called readme.txt.
  6. Click the Close button to exit the WinZip Self-Extractor.
  7. Return to the main page to complete the install.

You may also find it convenient to print the STUDENT Database Schema Diagram (requires the Adobe Acrobat Reader).

Additional Example Tables

If you want to optionally install the additional tables used for some of the examples in the book you can run an additional script.

If you downloaded the sample database you will automatically have two files named sql_book_add_tables.sql and drop_extra_tables.sql in your c:\guest\schemasetup directory. You may install these additional tables after you have created the STUDENT account. Instructions on how to create the additional files are found in the readme.pdf file under the header "IV. Additional Example Tables Installation Instructions."

Please see also more information on installing example tables.

Tips and Techniques

To aid you in your work with the exercises and labs in the book, we offer resources on the following topics:

Test Your Thinking and Additional Practice Questions

Download the answers to the Test Your Thinking plus Additional Practice Q&A .

Sample Content

Online Sample Chapter

The Basics of SQL and Oracle Databases

Downloadable Sample Chapter

Download the Sample Chapter related to this title.

Table of Contents

(NOTE: Each chapter concludes with Test Your Thinking.)



About the Author.


1. SQL and Data.

Data, Databases, and the Definition of sql. Table Relationships. The Student Schema Diagram.

2. SQL: The Basics.

The SQL*Plus Environment. The Anatomy of a SELECT Statement. Editing a SQL Statement. The WHERE Clause: Comparison and Logical Operators. The ORDER BY Clause.

3. Character, Number, and Miscellaneous Functions.

Character Functions. Number Functions. Miscellaneous Single-Row Functions.

4. Date and Conversion Functions.

Applying Oracle's Date Format Models. Performing Date and Time Math. Understanding Timestamp and Time Zone Datatypes. Performing Calculations with the Interval Datatypes. Converting from One Datatype to Another.

5. Aggregate Functions, Group By, and Having.

Aggregate Functions. The GROUP BY and HAVING Clauses.

6. Equijoins.

The Two-Table Join. Joining Three or More Tables.

7. Subqueries.

Simple Subqueries. Correlated Subqueries. Inline Views and Scalar Subquery Expressions . ANY, SOME, and ALL Operators in Subqueries.

8. Set Operators.

The Power of UNION and UNION ALL. The MINUS and INTERSECT Set Operators.

9. Complex Joins.

Outer Joins. Self-Joins.

10. Insert, Update, and Delete.

Creating Data and Transaction Control. Updating and Deleting Data.

11. Create, Alter, and Drop Tables.

Creating and Dropping Tables. Altering Tables and Manipulating Constraints.

12. Views, Indexes, and Sequences.

Creating and Modifying Views. Indexes. Sequences.

13. The Data Dictionary and Advanced SQL*Plus Commands.

The Oracle Data Dictionary Views. Advanced SQL*Plus Commands.

14. Security.

Users, Privileges, Roles, and Synonyms.

15. Regular Expressions and Hierarchical Queries.

Regular Expressions. Hierarchical Queries.

16. Exploring Data Warehousing Features.

-Advanced SQL Concepts, Analytical Functions, and the with Clause. ROLLUP and CUBE Operators.

17. SQL Optimization.

-The Oracle Optimizer and Writing Effective SQL Statements.

Appendix A: Answers to Self-Review Questions.

Appendix B: SQL Formatting Guide.

Appendix C: SQL*Plus Command Reference.

Appendix D: Student Database Schema.

Appendix E: Table and Column Descriptions.

Appendix F: Additional Example Tables.

Appendix G: Navigating through the Oracle Documentation.

Appendix H: Resources.

Appendix I: Oracle Datatypes.




Page 22, 1st sentence under heading Identifying and Nonidentifying Relationships. It should read "In an identifying relationship, the PRIMARY key of the entity is propagated to the child entity as part of the primary key."

Page 150, question 7. Answer choices a and b seem identical because answer b is missing spaces. Answer choice b should read "Mary Jones". Note the extra spaces between the two words.

Page 240, Table 4.12, The INTERVAL DAY TO SECOND example reads "INTERVAL '200' DAY (3) (300 days." It should read "INTERVAL '200' DAY (3) (200 days)."

Page 269, last SQL statement on the page. The SELECT statement's first line should read, '… IS NULL…' instead of 'IS NOT NULL'. The result will be 1256.13793 instead of 1301.81034.

Page 312, line 14. The foreign key references the GRADE_TYPE_WEIGHT table not of the GRADE_TYPE table.

Page 362, line 9 (answer to question c). "…course number exists… " should read "…course number does not exist…"

Page 433, line 1. Replace the SECTION table with the ENROLLMENT table instead.

Page 522, line 7. The word "you" is missing between "if" and "do".

Page 563, line 2. "study_months" should be "study_duration".

Page 637, Question 13.2.1 a , last line on page and Page 642 Answers to 13.2.1. a. Replace 'ORDER BY POSITION' with 'ORDER BY 1, 2'.

Page 676, Heading User-Defined Roles, last sentence in first paragraph. Remove the word "to" in the sentence "…group of programmers need to privileges…".

Page 685, line 15, 16. This should be VARCHAR2 instead of VARCHAR in both places.

Page 727, last line in SQL statement on bottom of page. Single quotes around the literal '132' should be removed. (Statement will still execute with quotes, and Oracle will implicitly convert to NUMBER datatype.)

Page 744, grey chapter tab. This should read Lab 16.1 instead of 15.1.

Page 847, the last execution plan on the page. It shows UNION-ALL instead of UNION.

Submit Errata

More Information

Unlimited one-month access with your purchase
Free Safari Membership