Home > Store

Oracle SQL Interactive Workbook, 2nd Edition

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

Oracle SQL Interactive Workbook, 2nd Edition

Book

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

About

Features

The fastest way to master Oracle SQL: a complete book and - web, hands-on interactive course!

° Unique workbook pedagogy with hands-on exercises, programming projects and a free Web-based training module

° Covers every key Oracle SQL concept: SQL*Plus, DDL, DML, DQL, the Oracle Data Dictionary, and more!

° The author chairs the Database Application Development and Design track at Columbia University School of Continuing Education

Description

  • Copyright 2003
  • Edition: 2nd
  • Book
  • ISBN-10: 0-13-100277-5
  • ISBN-13: 978-0-13-100277-7

  • Write efficient Oracle SQL statements—now!
  • Fully updated for Oracle 9i (including Release 2)
  • No Oracle SQL experience necessary!
  • Avoid the common pitfalls
  • Dozens of self-review questions and answers make sure you understand!
  • 100's of exercises cover the real-world tasks that matter most!

New to Oracle development? This integrated book-and-Web learning solution will help you master Oracle SQL—fast! Fully updated for Oracle 9i, it teaches every Oracle SQL skill you need through hands-on labs, exercises, projects, and our exclusive, FREE Web-based training site. You'll master all types of joins (including the new ANSI join syntax); SQL functions; subqueries; set operators; sequences, views, and indexes; the data dictionary; dynamic SQL; security; optimization; the SQL*Plus and iSQL*Plus environments; and much more.

Every Prentice Hall Oracle Interactive Workbook is fully integrated with its own exclusive Web site, giving you all this and more:

  • "Test Your Thinking" project solutions and detailed explanations
  • Additional self-review exercises with instant feedback and explanations
  • Gain knowledge that takes years to acquire. This book makes you leap ahead of the pack!
  • The essential SQL knowledge and beyond, presented in an easy-to-understand format

Downloads

Downloads

On this page, you will find instructions for downloading the sample database used in the exercises and labs in Oracle SQL Interactive Workbook, 2nd 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. Twenty-three (23) 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 "4. Additional Example Table Installation Instructions."

Read More Tips and Techniques

Extras

Solutions

Below you will find solutions for each chapter's Test Your Thinking exercises.

  1. SQL and Data
  2. SQL: The Basics
  3. Character and Number Functions
  4. Date, Conversion, and Miscellaneous Functions
  5. Aggregate Functions, GROUP BY, and HAVING
  6. Equijoins
  7. Subqueries
  8. Set Operators
  9. Complex Joins
  10. Insert, Update, and Delete
  11. Create, Alter, and Drop Tables
  12. Views, Indexes, and Sequences
  13. The Data Dictionary and Dynamic SQL Scripts
  14. Security
  15. Advanced SQL Queries
  16. SQL Optimization

Sample Content

Online Sample Chapter

SQL and Data

Table of Contents

(NOTE: All chapters conclude with Test Your Thinking.)

Foreword.


Acknowledgments.


About the Author.


Introduction.


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. Using the New Oracle 9i DATETIME Datatypes. Performing Calculations with the INTERVAL Datatype. 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, Scalar Subquery Expressions, and the Subquery WITH Clause. 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 Dynamic SQL Scripts.

The Oracle Data Dictionary Views. Dynamic SQL Scripts.



14. Security.

Users, Privileges, Roles, and Synonyms.



15. Advanced SQL Queries.

Advanced SQL Concepts and Analytical Functions. ROLLUP and CUBE Operators. Hierarchical Queries.



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


Index.

Preface

Introduction

The SQL language is the de facto standard language for relational databases, and Oracle's database server is the leading relational database on the market today. The Oracle SQL Interactive Workbook, second edition, presents an introduction to the Oracle SQL language in a unique and highly effective format. Rather than being a reference book, it guides you through the basic skills until you reach a mastery of the language. The book challenges you to work through hands-on guided tasks rather than read through descriptions. You will be able to retain the material more easily and the additional example questions reinforce and further enhance the learning experience.

Who This Book Is For

This book is intended for anyone requiring a background in Oracle's implementation of the SQL language. In particular, application developers, system designers, and database administrators will find many practical and representative real-world examples. Students new to Oracle will gain the necessary experience and confidence to apply their knowledge in solving typical problems they face in the work situation. Individuals already familiar with Oracle SQL but wishing a firmer foundation or those interested in the new Oracle 9i features will discover many of the useful tips, tricks, and information.

The initial audience for the book was the students of the Oracle SQL class at Columbia University's Computer Technology and Applications program. The student body has a wide-ranging level of technology experience. Their questions, learning approaches, and feedback provided the framework for this book. Many students cited the hands-on exercises as critical to their understanding of database technology and the SQL language and continuously asked for more examples and additional challenging topics. This book shares much of the material presented in the classroom and looks at the various angles of many solutions to a particular issue.

What Makes This Book Different

Unlike other SQL books, this book discusses Oracle's specific implementation of the language. Learning the language alone is not enough. The book also teaches you how to adopt good habits and teaches you about many Oracle-specific technology features that are essential to successful systems development.

The book's exercises build knowledge step-by-step by introducing you to relational database concepts, the SQL*Plus environment, and the SQL language. Learning involves not just reading about a subject, but also doing it. The book's focus is to give the reader examples of how the SQL language is commonly used, with many exercises reinforcing the learning experience.

Unlike other SQL books, this book is not a reference book. The best way to learn the SQL language is to perform the exercises and compare your answers with the sample answers and accompanying explanations. After you have completed the exercises, the companion Web site contains additional questions to test your understanding. It also lists the solutions to the Test Your Thinking exercises at the end of each chapter. You can access the companion Web site at www.phptr.comischert.

This book does not cover the entire Oracle SQL syntax, but emphasizes the essentials of the most-frequently used features with many examples to reinforce the learning. We hope that you will enjoy this learning experience and come away with the knowledge you hoped to gain.

How This Book Is Organized

Each chapter of the book is divided into labs covering a particular topic. The objective of each lab is defined at its beginning, with brief examples that introduce the reader to the covered concepts.

Following the lab's introductory examples are exercises that are the heart of the lab which reinforce and expand the reader's knowledge of the subject. Each exercise consists of a series of steps to follow to perform specific tasks, or particular questions that are designed to help you discover the important aspects of the SQL language. The answers to these questions are given at the end of the exercises, along with more in-depth discussion of the concepts explored.

After you perform the exercises and compare the answers with the sample queries, answers, and explanations, the lab ends with multiple-choice Self-Review questions. These are meant to test that you have understood the material covered in the lab. The answers to these questions appear in Appendix A. There are additional Self-Review questions at this book's companion Web site.

At the end of each chapter, the Test Your Thinking section reinforces the topics learned in labs, and solidifies your skills. The answers to these questions are found on the companion Web site for this book.

Layout of a Chapter

Each chapter contains:

  • Lab
  • Exercises
  • Exercise Answers (with detailed discussion)
  • Self-Review Questions
  • Test Your Thinking Questions

The chapters should be completed in sequence because concepts covered in earlier chapters are required for the completion of exercises in later chapters.

About the Companion Web Site

On the companion Web site you will find three very important things:

  1. Installation files you need before you begin reading the workbook.
  2. Answers to the Test Your Thinking questions.
  3. Additional Self-Review questions.

Installation Files

All of the exercises and questions are based on a sample schema called STUDENT. The required files to install this STUDENT schema and the installation instructions can be downloaded from the Web site.

Test Your Thinking

The answers to the Test Your Thinking sections are also found at the Web site.

Additional Self-Review Questions

The Web site will have many other features, such as additional Self-Review questions, a message board, and periodically updated information about the book.

What You will Need

  • To complete the exercises you need the following:
  • The Oracle® database software
  • Oracle's SQL*Plus software or a Web browser
  • Access to the Internet

Oracle 9i

Oracle 9i is Oracle's relational database software and its flagship product. You can use either the Personal Edition or Enterprise Edition. The Enterprise editions of Oracle 9.01 and 9.2 were used to create the exercises for this book, but subsequent versions should be compatible. If you have a previous version of the Oracle database you will be able to complete a large majority of the exercises, however some syntax options and features are only available in Oracle 9i.

Oracle SQL*Plus Software or Web Browser

You can perform all the exercises in this book with Oracle's SQL*Plus software. Oracle introduced iSQL*Plus with Oracle version 8.1.7. This browser-based version does not require the installation of the Oracle SQL*Plus software on your individual machine. Only a browser is necessary and access to an HTTP iSQL*Plus server. The iSQL*Plus version simplifies the editing and offers a superior display of the result. However, iSQL*Plus does not allow certain functionality and this is pointed out where applicable. For a list of unsupported commands refer to Appendix C, "SQL*Plus Command Reference." Instead of SQL*Plus you can also use alternate SQL execution environments and a list is available in Appendix H, "Resources."

Access to the Internet

You will need access to the Internet so that you can access the companion Web site. (http://www.phptr.com.rischert) Here you will find files that are necessary to install the sample STUDENT schema.

About the Student Schema

Throughout this workbook, you access data from a sample schema called STUDENT, which contains information about a computer education program. The schema was designed to record data about instructors, courses, students, and their respective enrollments and grades.

After you download the installation files to create the schema within your Oracle database, you will be able to follow the exercises in the workbook. In Chapter 1, "SQL and Data," you are introduced to the relational concepts necessary to read the schema diagram. Appendix D, "Student Database Schema," shows you a graphical representation of the schema and Appendix E, "Table and Column Descriptions" lists descriptive information about each table and column.

Updates

Errata

Book Updates and Errata

  • Page 107. Question 2.4.1 d) The letter 'r' is missing from the column course_no in the SELECT statement.
  • Page 112. Answer 2.4.2 b) The query result should be 1195 instead of 1595. The correct query result should look like this:
    COST  ---------       1195     1 row selected.  	
  • Page 161. Replace the references to NULLIF with NVL instead. It will read then as:
    The above result can be achieved with the NVL function.  SELECT instructor_id, zip,         NVL(zip, 'NO zipcode!') "NVL Use"    FROM instructor   WHERE instructor_id IN (102, 110)  
  • Page 271. The last sentence of the first paragraph indicates the incorrect number of rows. It should be 50 instead of 75. Therefore, the paragaph should read "a total capacity of greater than 50 are returned in the result."
  • Page 638. Add the letter 'S' to the REFERENCES privilege in Table 14.2.
  • Page 850. Add the entry "SP2-0864 462" to the index.

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020