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


  • Sorry, this book is no longer in print.
Not for Sale



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

Oracle SQL by Example, Third Edition is a convenient, quick way to master Oracle SQL. This unique integrated book-and-Web learning solution teaches all the SQL skills you will need to succeed in an Oracle environment. Through hands-on, real-world labs, exercises, projects, and a superb Web-based training site, the book teaches administrators and programemrs alike the core skills required for their jobs. Coverage includes every key Oracle SQL concept, including: entity-relationship diagrams, the SQL*Plus environment, SELECT and other statements, Boolean operations, joins, aggregate and not-aggregate functions. Each chapter contains review exercises that help the reader master the important skills before they move on.



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