Oracle SQL by Example 

 

“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., Oracle Magazine PL/SQL Developer of the Year 2007, and President

Emeritus, New York Oracle Users Group

“This is a fascinating guide into the world of Oracle SQL with an abundance of well-collected examples. Without a doubt, this

book is helpful to beginners and experts alike who seek alternative ways to resolve advanced scenarios.”

—Oleg Voskoboynikov, Ph.D., Database Architect

 

The World’s #1 Hands-On Oracle SQL Workbook—Fully Updated for Oracle 11g

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 to regular expressions and with newly added coverage of Oracle’s powerful new SQL Developer tool,

you will focus on the tasks that matter most. Hundreds of step-by-step, guided lab exercises will systematically strengthen your expertise

in writing effective, high-performance SQL. Along the way, you’ll acquire a powerful arsenal of useful skills—and an extraordinary library of

solutions for your real-world challenges with Oracle SQL.

Coverage includes

 100% focused on Oracle SQL for Oracle 11g, today’s #1 database platform—not “generic” SQL!

Master all core SQL techniques including every type of join such as equijoins, self joins, and outer joins

Understand Oracle functions in depth, especially character, number, date, timestamp, interval, conversion, aggregate, regular expressions, analytical, and more

Practice all types of subqueries, such as correlated and scalar subqueries, and learn about set operators and hierarchical queries

Build effective queries and learn fundamental Oracle SQL Developer and SQL*Plus skills

Make the most of the Data Dictionary and create tables, views, indexes, and sequences

Secure databases using Oracle privileges, roles, and synonyms

Explore Oracle 11g’s advanced data warehousing features

Learn many practical tips about performance optimization, security, and architectural solutions

Avoid common pitfalls and understand and solve common mistakes

For every database developer, administrator, designer, or architect, regardless of experience!

 

ABOUT THE AUTHOR

ALICE RISCHERT, formerly chair of Columbia University’s Database Application Development and Design program, has taught SQL to

hundreds of students. She has worked with Oracle databases for almost two decades as a database administrator, database architect,

and project leader for Fortune® 100 companies.

 

 

 

TABLE OF CONTENTS

Foreword

Preface

CHAPTER 1 SQL and Data

LAB 1.1 The Relational Database

LAB 1.2 Data Normalization and Table Relationships

LAB 1.3 The STUDENT Schema Diagram

CHAPTER 2 SQL: The Basics

LAB 2.1 The SQL Execution Environment

LAB 2.2 The Anatomy of a SELECT Statement

LAB 2.3 An Introduction to SQL*Plus

CHAPTER 3 The WHERE and ORDER BY Clauses

LAB 3.1 The WHERE Clause

LAB 3.2 The ORDER BY Clause

CHAPTER 4 Character, Number, and Miscellaneous

LAB 4.1 Character Functions

LAB 4.2 Number Functions

LAB 4.3 Miscellaneous Single-Row Functions

CHAPTER 5 Date and Conversion Functions

LAB 5.1 Applying Oracle’s Date Format Models

LAB 5.2 Performing Date and Time Math

LAB 5.3 Understanding the TIMESTAMP and TIME ZONE Data Types

LAB 5.4 Performing Calculations with the Interval Data Types

LAB 5.5 Converting from One Data Type to Another

CHAPTER 6 Aggregate Functions,GROUP BY, and HAVING Clauses

LAB 6.1 Aggregate Functions

LAB 6.2 The GROUP BY and HAVING Clauses

CHAPTER 7 Equijoins

LAB 7.1 The Two-Table Join

LAB 7.2 Joining Three or More Tables

CHAPTER 8 Subqueries

LAB 8.1 Simple Subqueries

LAB 8.2 Correlated Subqueries

LAB 8.3 Inline Views and Scalar Subquery Expressions

LAB 8.4 ANY,SOME, and ALL Operators in Subqueries

CHAPTER 9 Set Operators

LAB 9.1 The Power of UNION and UNION ALL

LAB 9.2 The MINUS and INTERSECT Set Operators

CHAPTER 10 Complex Joins

LAB 10.1 Outer Joins

LAB 10.2 Self-Joins

CHAPTER 11 Insert, Update, and Delete

LAB 11.1 Creating Data and Transaction Control

LAB 11.2 Updating and Deleting Data

LAB 11.3 The SQL Developer Data Tab

CHAPTER 12 Create,Alter, and Drop Tables

LAB 12.1 Creating and Dropping Tables

LAB 12.2 Altering Tables and Manipulating Constraints

CHAPTER 13 Indexes, Sequences, and Views

LAB 13.1 Indexes

LAB 13.2 Sequences

LAB 13.3 Views

CHAPTER 14 The Data Dictionary, Scripting, and Reporting

LAB 14.1 The Oracle Data Dictionary Views

LAB 14.2 Scripting and Reporting

CHAPTER 15 Security

LAB 15.1 Users, Privileges, Roles, and Synonyms

CHAPTER 16 Regular Expressions and Hierarchical Queries

LAB 16.1 Regular Expressions

LAB 16.2 Hierarchical Queries

CHAPTER 17 Exploring Data Warehousing Features

LAB 17.1 Advanced SQL Concepts,Analytical Functions, and the WITH Clause

LAB 17.2 ROLLUP and CUBE Operators

CHAPTER 18 SQL Optimization

LAB 18.1 The Oracle Optimizer and Writing Effective SQL Statements

APPENDIX A Answers to Quiz 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 the Oracle Documentation

APPENDIX H Resources

APPENDIX I Oracle Data Types

Index