Cover
Contents
Preface
Prologue - The Software Engineering Process and Relational Databases
What is a Database?
Database Models
The Hierarchical Model
Other Cardinalities
The Network Model
Contemporary Databases: The Relational Model
The First, Second, and Third Normal Forms
The First Normal Form
Non-1NF to First Normal Form (1NF)
The Second Normal Form
Non-2NF to 2NF
The Third Normal Form
Non-3NF to 3NF
What is the Software Engineering Process?
Chapter 1 - Getting Started with Oracle
Getting Started with Oracle in UNIX
Signing on to Oracle in UNIX
Setting Your System Parameters
Setting the PAUSE Parameter
Setting the Prompt Parameter
Showing Timing Statistics
Viewing a List of System Parameters
Oracle's HELP Command in UNIX
Using Oracle Commands
Understanding SQL and Its Sublanguage
Using SELECT Statement Syntax
Re-executing a Command
Accessing Tables
Adding Comments to Statements
A Few More Examples and Further Comments about Case
Editing SQL Statements
Option 1: Editing SQL Statements Using an Editor
Defining an Editor
Editing the Buffer
Saving the Buffer
Using GET
Using a Script File to Save Your Query
Option 2: Editing SQL Statements or Queries Using SQLPlus
Using the CHANGE Command
Using the LIST Command
Using the APPEND Command
Using the INPUT Command
Using INPUT to Insert a Line
Using the DELETE Command
Displaying the Student-Course Database
Displaying the Course Table (the Course Relation)
Creating a Synonym for the Course Table
Deleting a Synonym
Introducing the Oracle Data Dictionary
Using DESC
Using a Convention for Writing SQL Statements
Printing Query Results and Using Host
Signing Off from Oracle
Exercises for Chapter 1
Chapter 2 - More "Beginning" SQL Commands
An Extended SELECT Statement
SELECTing Attributes (Columns)
Using ORDER BY
SELECTing Rows
Using AND
Using OR
Using BETWEEN
A Simple CREATE TABLE Command
Inserting Values into an Existing Table
INSERT INTO .. VALUES
INSERT INTO .. SELECT
The UPDATE Command
The DELETE Command
Deleting a Table
ROLLBACK, COMMIT, and SAVEPOINT
The ALTER TABLE Command
Data Types
Common Number Data Types
CHAR Data Type
VARCHAR2 Data Type
NCHAR and NVARCHAR2 Data Types
LONG, RAW, LONG RAW, and BOOLEAN Data Types
Large Object (LOB) Data Types
Abstract Data Types
The XML Data Type
The DATE Data Type and Type Conversion Functions
Entering Four-Digit Years
Exercises for Chapter 2
Chapter 3 - Joins
The Cartesian Product
The Join
Join Using ANSI Join Syntax
Theta Joins
Qualifiers
Table Aliases and an Introduction to Multi-Table Joins
More on Comments
More on Multiple Table Joins and Join Conditions
Column Aliases
Scripting
COUNT and Rownum
Using COUNT
Using Rownum
Outer Joins
Left Outer Join
Right Outer Join
Handling Full Outer Joins
Outer Join with an AND Condition
Chaining Outer Joins
Self Joins
Self Join and Outer Join
Exercises for Chapter 3
Chapter 4 - Functions
The COUNT Function
Using SELECT and COUNT with DISTINCT
More Basic Functions
Aggregate Functions
Row-Level Functions
The NVL Function
String Functions
The SUBSTR and INSTR Functions
The RPAD and LPAD Functions
The LTRIM and RTRIM Functions
The LENGTH Function
Matching Substrings Using LIKE
LIKE as an Existence Match
LIKE with a Positioned Match and a Wildcard
The UPPER and LOWER Functions
The Data Dictionary Revisited
Exercises for Chapter 4
Chapter 5 - Query Developement, Privileges, and Derived Structures
Query Development
Using SAVE and EDIT
Deleting a Query
Parentheses in SQL Expressions
Derived Structures
Views
Using the CREATE OR REPLACE VIEW Statements
Adding ORDER BY to CREATE OR REPLACE VIEW Statements
Developing a Query Using Views
Creating Special View Column Names
Granting and Revoking Privileges on Tables and Views
Query Development and Derived Structure
Step 1: Developing a Query Step-by-Step
Step 2: Using a Derived Structure
Option 1: Make Your Query a View
Option 2: Create a Temporary Table
Option 3: Use an Inline View
Option 4: Use a Snapshot
Exercises for Chapter 5
Chapter 6 - Set Operations
UNION Operations
The IN and NOT .. IN Predicates
Using IN
Using NOT .. IN
The Difference Operation
Exercises for Chapter 6
Chapter 7 - Subqueries versus Joins
The IN Subquery
The Subquery as a Join
When the Join Cannot Be Turned into a Subquery
More Examples Involving Joins and IN
Example 1
Example 2
Example 3
Subqueries with Operators
Exercises for Chapter 7
Chapter 8 - Group By and Having
Aggregate/Column Functions
The GROUP BY Clause
GROUP BY and ORDER BY
The HAVING Clause
HAVING and WHERE
GROUP BY and HAVING: Aggregates of Aggregates
Auditing IN Subqueries
Nulls Revisited
Exercises for Chapte 8
Chapter 9 - Correlated Subqueries
Non-Correlated Subqueries
Correlated Subqueries
Existence Queries and Correlation
EXISTS
From IN to EXISTS
NOT EXISTS
SQL Universal and Existential Qualifiers - the "for all" Query
Example 1
Example 2
Example 3
Exercises for Chapte 9
Chapter 10 - Create Table and SQLLOADER
The "Simple" CREATE TABLE
The NOT NULL Constraint
PRIMARY KEY Constraints
Creating the PRIMARY KEY Constaint
At the Column Level
At the Table Level
Using the ALTER TABLE Command
Adding a Concatenated Primary Key
Another Example of Adding a Concatenated Primary Key
The UNIQUE Constraint
The CHECK Constraint
Referential Integrity
Defining the Referential Integrity Constraint
Adding the Foreign Key after Tables are Created
Using DELETE and the Referential Constraint
ON DELETE RESTRICT
ON DELETE CASCADE
ON DELETE SET NULL
More on Constaint Names
SQLLOADER
SQLLOADER Example 1
Another SQLLOADER Example
Exercises for Chapte 10
Chapter 11 - Multiple Commands, START Files, and Reports in SQLPlus
Creating a File (a START Table) and Starting It
A START File (Script) with Editing Features
Using the DECODE, GREATEST, and LEAST Functions
DECODE
GREATEST and LEAST
Adding Reporting Features to aa START File
A New and Improved Script
Using START Files with ACCEPT and PROMPT
Using START Files with Positional Input
Exercises for Chapter 11
Chapter 12 - Beginning PL/SQL: Anonymous Blocks, Procedures, Functions, and Packages
Anonymous Blocks
Elementary Procedures with Sequence Structures
A Simple Example of a Procedure
Reusing a Procedure
Deleting a Procedure
Adding a Parameter List to a Procedure
Performing More than One Action in a Procedure
Procedures with Selection and Iteration Control Structures
Example of a Procedure with Selection
Example of a Procedure with Iteration
Functions
Example of a Function
Deleting a Function
Packages
Creating a Package
Another Approach to Creating This Package
Deleting a Package
Defining a PL/SQL INDEX BY Table
Using a PL/SQL Table
Exercises for Chapter 12
Chapter 13 - Introduction to Triggers
What is a Trigger?
A Simple Trigger Example
How the Trigger Worked
Row-Level Triggers versus Statement-Level Triggers
Enablng and Disabling Triggers
Enabling All Triggers for a Table
Deleting Triggers
Values in the Trigger
Using WHEN
Performance Issues Using WHEN
A Trigger Where One Table Affects Another Trigger
Mutating Tables
Exerecises for Chapter 13
Chapter 14 - SQL and XML
Overview of XML
Oracle and XML
XMLFOREST
Using XMLELEMENT
Using XMLATTRIBUTES
Creating a Table Using the XMLType Data Type
Inserting Values into Tables with an XMLType Data Type
Extracting Information Using XPATH
Using EXTRACTVALUE
Using EXISTSNODE
Exercises for Chapter 14
Appendix A - Some UNIX Commands
Commonly Used UNIX Commands
Summary Table
Other Miscellaneous Commands
Editors
Using vi as Your Editor
Other vi Commands
Using joe as Your Editor
Appendix B - The Data Dictionary
Beginning to Explore the Data Dictionary
Choosing a View from the Dictionary
Choosing the View You Want to See
Describing the View You Want to See
Finding the "Right" Columns
Finding out How Many Rows are in the Views
Views of TABLES
Other Objects - Tablespaces and Constraints
Views of Tablespaces
Views of Constraints
Exercises for Appendix B
Appendix ะก - The Student Database and Other Tables Used in this Book
The Student-Course Database
Entity Relationship Diagram of the Student-Course Database
Other Tables Used in This Book
Appendix D - Glossary of Terms
Appendix E - Important Commands and Functions
Index