Oracle SQL Interactive Workbook

About

Features

• Comprehensive, clear writing style.

  • Provides students with an understandable introduction to the Oracle Forms. Ex.___

• Guided hands-on interaction.

  • Provides students with detailed step-by-step instructions on how to use the product. Ex.___

• Real-world lab exercises with answers.

  • Draws students in and reinforces the material so they can more easily understand the subject. Ex.___

• Self-Review and Test Your Thinking questions—In each chapter.

  • Enables students to test their understanding and solidify skills learned. Ex.___

• Companion Website.

  • Provides students with necessary files and answers to Test Your Thinking questions. Ex.___

• Advice/Quote icons.

  • Focuses students' attention on special advice or guidelines. Ex.___

• Tips boxes.

  • Provides students with helpful time- and trouble-saving tips and tricks. Ex.___

• Warning flags.

  • Provides students with precautions and information designed to avoid computer troubles. Ex.___

• Web flags.

  • Directs students to the book's companion Web site for reference. Ex.___



Description

• Copyright 2000
• Dimensions: 7 X 9-1/4
• Pages: 516
• Edition: 1st

• Book
• ISBN-10: 0-13-015745-7
• ISBN-13: 978-0-13-015745-4

Foreword by Peter Koletzke
Technical Advisor: Douglas Scherer

The Hands-On Oracle SQL Course That's Fast, Easy, & Web-charged!

FREE Access to Interactive Oracle SQL Training Web Site

• Learn to write SQL fluently—now!
• No Oracle SQL experience necessary!
• Covers all key Oracle SQL concepts
• Real-life labs and "Test Your Thinking" Q&As

New to Oracle development? This integrated book-and-Web learning solution will help you master Oracle SQL — fast! Learn every Oracle SQL skill you need, through hands-on, real-world labs, exercises, projects, and our great Web-based training site. Master entity-relationship diagrams, SQL*Plus, SELECT statements, Boolean operations, functions, joins, NULLS, subqueries, relational operators, data and database object manipulation, and the Oracle dictionary.

Totally integrated with a FREE, state-of-the-art Oracle SQL learning Web site!

Every Prentice Hall 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
• Author's Corner: Your personal connection to this book's expert author
• Additional self-review exercises with instant feedback and explanations
• An exclusive Virtual Study Lounge where you can interact with other students!

Dozens of exercises cover the real-world tasks that matter most!

100s of self-review questions and answers make sure you understand!

Master the skills you need! Alex Morrison and Alice Rischert are experienced Oracle SQL instructors and database consultants who have taught hundreds of students how to effectively write Oracle SQL. Benefit from their combined Oracle knowledge of over 20 years. The Prentice-Hall Oracle Interactive Workbook series is an integrated learning system that's proven to work!



Sample Content

Table of Contents

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

1. SQL and Data.

Data, Databases, and the Definition of SQL. A Case Study Schema Diagram. Referential Integrity and Table Relationships. The SQL *PLUS Environment.

2. SQL: The Basics.

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

3. Character and Number Functions.

Character Functions. Number Functions.

4. Date and Conversion Functions.

Converting from One Datatype to Another. Applying Oracle's Date Format Models. Performing Date and Time Math. The NVL and DECODE Functions.

5. Aggregate Functions, GROUP BY, and HAVING.

Aggregate Functions. The GROUP BY and HAVING Clauses.

6. Equijoins.

The Two Table Join. Joining More Than Two Tables.

7. Subqueries.

Simple Subqueries. Correlated Subqueries. ANY, COME, 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.

Manipulaitng Data and Transaction Control.

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.

DECODE Magic and In-line Views. Hierarchical Queries.

16. SQL Optimization.

The Oracle Optimizer and Writing Effective SQL Statement.

Appendix A: Answers to Self-Review Questions.


Appendix B: SQL Formatting Guide and SQL*Plus Command Reference.


Appendix C: Student Database Schema.


Index.

Preface

Preface

Who This Book Is For

The SQL language is the de facto standard language for relational databases. Oracle's database server is the leading relational database on the market today. This book is intended for anyone requiring a background in Oracle's implementation of the SQL language.

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. Rather, its emphasis is on the exercises and the learning-by-doing experience. The best way to learn the SQL language is to perform the exercises and compare your answers with the sample answers and accompanying explanations.

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, found at http://www.phptr.com/phptrinteractive/workbooks/index.html

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 can also be found on the companion Web site for this book.

Layout of a Chapter

ChapterLabExercisesExercise Answers with detailed discussionSelf-Review QuestionsLabExercisesExercise Answers with detailed discussionSelf-Review QuestionsTest 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

The companion Web site is located at

http://www.phptr.com/morrison

Here 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 files required 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. These answers will be textual or in the form of downloadable files.

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.

Visit the companion Web site and download the required files before starting the labs and exercises.

What You'll Need

To complete the exercises you need the following:

• The Oracle database software
• Oracle's SQL*Plus software
• Access to the WWW and installation files from the companion Web site

Oracle8

Oracle8 is Oracle's relational database software and its flagship product. You can use either Oracle Personal Edition or Oracle Enterprise Edition. Oracle 8.1.5 Enterprise Edition was used to create the exercises for this book, but subsequent versions should be compatible.

Access to the WWW

You will need access to the Internet and the WWW so that you can access the companion Web site.

http://www.phptr.com/morrison

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 transfer the schema into 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 C, "Student Database Schema," shows you a graphical representation of the schema and useful descriptive information about each table and column.



Updates

Submit Errata

