Mastering Oracle SQL and SQL*Plus

Contents

Foreword

About the Author

About the Technical Reviewers

Acknowledgments

Introduction

Chapter 1 Relational Database Systems and Oracle

1.1 Information Needs and Information Systems

1.2 Database Design

Entities and Attri

Generic vs. Specific

Redundancy

Consistency, Integrity, and Integrity Constraints

Data Modeling Approach,Methods, and Techniques

Semantics

Information Systems Terms Review

1.3 Database Management Systems

DBMS Components

Database Applications

DBMS Terms Review

1.4 Relational Database Management Systems

1.5 Relational Data Structures

Tables, Columns, and Rows

The Information Principle

Datatypes

Keys

Missing Information and Null Values

Constraint Checking

Predicates and Propositions

Relational Data Structure Terms Review

1.6 Relational Operators

1.7 How Relational Is My DBMS?

1.8 The Oracle Software Environment

1.9 Case Tables

The ERM Diagram of the Case

Chapter 2 Introduction to SQL, iSQL*Plus,and SQL*Plus

2.1 Overview of SQL

Data Definition

Data Manipulation and Transactions

Retrieval

Security

2.2 Basic SQL Concepts and Terminology

Constants (Literals)

Variables

Operators,Operands, Conditions, and Expressions

Functions

Database Object Naming

Comments

Reserved Words

2.3 Introduction to iSQL*Plus

2.4 Introduction to SQL*Plus

Entering Commands

Using the SQL Buffer

Using an External Editor

Using the SQL*Plus Editor

Saving Commands

Running SQL*Plus Scripts

Adjusting SQL*Plus Settings

Describing Database Objects

Executing Commands from the Operating System

Clearing the Buffer and the Screen

SQL*Plus Command Review

Chapter 3 Data Definition, Part I

3.1 Schemas and Users

3.2 Table Creation

3.3 Datatypes

3.4 Commands for Creating the Case Tables

3.5 The Data Dictionary

Chapter 4 Retrieval: The Basics

4.1 Overview of the SELECT Command

4.2 The SELECT Clause

Column Aliases

The DISTINCT Keyword

Column Expressions

4.3 The WHERE Clause

4.4 The ORDER BY Clause

4.5 AND, OR, and NOT

The OR Operator

The AND Operator and Operator Precedence Issues

The NOT Operator

4.6 BETWEEN, IN, and LIKE

The BETWEEN Operator

The IN Operator

The LIKE Operator

4.7 CASE Expressions

4.8 Subqueries

The Joining Condition

When a Subquery Returns Too Many Values

Comparison Operators in the Joining Condition

When a Single-Row Subquery Returns More Than One Row

4.9 Null Values

Null Value Display

The Nature of Null Values

The IS NULL Operator

Null Values and the Equality Operator

Null Value Pitfalls

4.10 Truth Tables

4.11 Exercises

Chapter 5 Retrieval: Functions

5.1 Overview of Functions

5.2 Arithmetic Functions

5.3 Text Functions

5.4 Regular Expressions

Regular Expression Operators and Metasymbols

Regular Expression Function Syntax

REGEXP_LIKE

REGEXP_INSTR

REGEXP_SUBST

REGEXP_REPLACE

5.5 Date Functions

EXTRACT

ROUND and TRUNC

MONTHS_BETWEEN and ADD_MONTHS

NEXT_DAY and LAST_DAY

5.6 General Functions

GREATEST and LEAST

NVL

DECODE

5.7 Conversion Functions

TO_NUMBER and TO_CHAR

Conversion Function Formats

Datatype Conversion

5.8 Stored Functions

5.9 Exercises

Chapter 6 Data Manipulation

6.1 The INSERT Command

Standard INSERT Commands

Multitable INSERT Commands

6.2 The UPDATE Command

6.3 The DELETE Command

6.4 The MERGE Command

6.5 Transaction Processing

The SQL*Plus AUTOCOMMIT Option

Transaction Design

Savepoints

6.6 Locking and Read Consistency

Locking

Read Consistency

Chapter 7 Data Definition, Part II

7.1 The CREATE TABLE Command

7.2 More on Datatypes

Character Datatypes

Numbers Revisited

7.3 The ALTER TABLE and RENAME Commands

7.4 Constraints

Out-of-Line Constraints

Inline Constraints

Constraint Definitions in the Data Dictionary

Case Table Definitions with Constraints

A Solution for Foreign Key References: CREATE SCHEMA

Deferrable Constraints

7.5 Indexes

7.6 Performance Monitoring with SQL*Plus AUTOTRACE

7.7 Sequences

7.8 Synonyms

7.9 The CURRENT_SCHEMA Setting

7.10 The DROP TABLE Command

7.11 The TRUNCATE Command

7.12 The COMMENT Command

7.13 Exercises

Chapter 8 Retrieval: Multiple Tables and Aggregation

8.1 Tuple Variables

8.2 Joins

Cartesian Products

Equijoin

Non-equijoins

Joins of Three or More Tables

Self-Joins

8.3 Alternative ANSI/ISO Standard Join Syntax

Natural Joins

Equijoins on Columns with the Same Name

8.4 Outer Joins

New Outer Join Syntax

Outer Joins and Performance

8.5 The GROUP BY Component

Multiple-Column Grouping

GROUP BY and Null Values

8.6 Group Functions

Group Functions and Duplicate Values

Group Functions and Null Values

Grouping the Results of a Join

The COUNT(*) Function

Valid SELECT and GROUP BY Clause Combinations

8.7 The HAVING Clause

HAVING Clauses Without Group Functions

A Classic SQL Mistake

Grouping on Additional Columns

8.8 Advanced GROUP BY Features

GROUP BY ROLLUP

GROUP BY CUBE

CUBE, ROLLUP, and Null Values

8.9 Partitioned Outer Joins

8.10 Set Operators

8.11 Exercises

Chapter 9 Retrieval: Some Advanced Features

9.1 Subqueries Continued

The ANY and ALL Operators

Correlated Subqueries

The EXISTS Operator

9.2 Subqueries in the SELECT Clause

9.3 Subqueries in the FROM Clause

9.4 The WITH Clause

9.5 Hierarchical Queries

START WITH and CONNECT BY

LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF

CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH

Hierarchical Query Result Sorting

9.6 Analytical Functions and Windows

Analytical Window Specification

Analytical Window Ordering

Partitioned Analytical Windows

9.7 Flashback Features

9.8 Exercises

Chapter 10 Views

10.1 What Are Views?

10.2 View Creation

Creating a View from a Query

Getting Information About Views from the Data Dictionary

Replacing and Dropping Views

10.3 What Can You Do with Views?

Simplifying Data Retrieval

Maintaining Logical Data Independence

Implementing Data Security

10.4 Data Manipulation via Views

Updatable Join Views

Nonupdatable Views

The WITH CHECK OPTION Clause

10.5 Data Manipulation via Inline Views

10.6 Views and Performance

10.7 Materialized Views

Properties of Materialized Views

Query Rewrite

10.8 Exercises

Chapter 11 SQL*Plus and iSQL*Plus

11.1 SQL*Plus Variables

SQL*Plus Substitution Variables

SQL*Plus User-Defined Variables

SQL*Plus System Variables

11.2 Bind Variables

Bind Variable Declaration

Bind Variables in SQL Statements

11.3 SQL*Plus Scripts

Script Execution

Script Parameters

SQL*Plus Commands in Scripts

The login.sql Script

11.4 Report Generation with SQL*Plus

The SQL*Plus COLUMN Command

The SQL*Plus TTITLE and BTITLE Commands

The SQL*Plus BREAK Command

The SQL*Plus COMPUTE Command

The Finishing Touch: SPOOL

11.5 HTML in SQL*Plus and iSQL*Plus

HTML in SQL*Plus

HTML in iSQL*Plus

11.6 Exercises

Chapter 12 Object-Relational Features

12.1 More Datatypes

Collection Datatypes

Methods

12.2 Varrays

Creating the Array

Populating the Array with Values

Querying Array Columns

12.3 Nested Tables

Creating Table Types

Creating the Nested Table

Populating the Nested Table

Querying the Nested Table

12.4 User-Defined Types

Creating User-Defined Types

Showing More Information with DESCRIBE

12.5 Multiset Operators

Which SQL Multiset Operators Are Available?

Preparing for the Examples

Using IS NOT EMPTY and CARDINALITY

Using POWERMULTISET

Using MULTISET UNION

Converting Arrays into Nested Tables

12.6 Exercises

APPENDIX A Quick Reference to SQL and SQL*Plus

Syntax Conventions Used in This Appendix

Starting and Stopping

Entering and Executing Commands

Working With the SQL*Plus Editor

Manipulating SQL*Plus Scripts

SQL*Plus Interactivity Commands

Variables and Parameters

Formatting Query Results

SQL: Data Manipulation (DML), Transactions, and Queries

SQL: Data Definition (DDL)

SQL: Other Commands

SQL: Operators

SQL: Functions

SQL: Regular Expressions

Rules for Naming Oracle Database Objects

SQL: Reserved Words

APPENDIX B Data Dictionary Overview

General Data Dictionary Views

ALL Views: Information About Accessible Objects

USER Views: Information About Your Own Data

DBA Views: Full Database Information

V$ Views: Dynamic Performance Views

APPENDIX C The Seven Case Tables

ERM Diagram

Table Structure Descriptions

Columns and Foreign Key Constraints

Contents of the Seven Tables

Hierarchical Employees Overview

Course Offerings Overview

APPENDIX D Answers to the Exercises

Chapter 4 Exercises

Chapter 5 Exercises

Chapter 7 Exercises

Chapter 8 Exercises

Chapter 9 Exercises

Chapter 10 Exercises

Chapter 11 Exercises

Chapter 12 Exercises

APPENDIX E Oracle Documentation, Web Sites, and Bibliography

Oracle Documentation

Oracle Web Sites

Bibliography

Index