Contents
About the Authors
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Relational Database Systems and Oracle
1.1 Information Needs and Information Systems
1.2 Database Design
Entities and Attributes
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
Table Descriptions
Chapter 2: Introduction to SQL and SQL Developer
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 SQL Developer
Installing and Configuring SQL Developer
Connecting to a Database
Exploring Objects
Schema Browser
Entering Commands
Browsing table data
Run Statement
Run Script
Saving Commands to a Script
Running a Script
Exporting Your Data
User-Defined Reports
Tuning Your SQL
Writing PL/SQL
Data Modeller
Chapter 3: Data Definition, Part I
3.1 Schemas and Users
3.2 Table Creation
3.3 Datatypes
Number Datatype
Character Datatype
Date Datatype
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_SUBSTR
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
SAMPLE Function
5.7 Conversion Functions
TO_NUMBER and TO_CHAR
Conversion Function Formats
Datatype Conversion
CAST
5.8 Stored Functions
Function in WITH Clause of Query
5.9 Exercises
Chapter 6: Data Manipulation
6.1 The INSERT Command
Standard INSERT Commands
INSERT Using Subqueries
6.2 The UPDATE Command
6.3 The DELETE Command
6.4 The MERGE Command
6.5 Transaction Processing
6.6 Locking and Read Consistency
Locking
Read Consistency
Terms Review
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
Index Creation
Index Management
7.6 Performance Monitoring with SQL Developer 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
Equijoins
Non-equijoins
Joins of Three or More Tables
Self-Joins
8.3 The JOIN Clause
Natural Joins
Equijoins on Columns with the Same Name
8.4 Outer Joins
Old Oracle-Specific Outer Join Syntax
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
The Difference Between WHERE and HAVING
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 Analytic Functions
Partitions
Function Processing
9.7 Row Limiting
9.8 Flashback Features
AS OF
VERSIONS BETWEEN
FLASHBACK TABLE
9.9 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 Global Temporary Table
10.9 Invisible Columns
10.10 Exercises
Chapter 11: SQL*Plus Basics and Scripting
11.1 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
Specifying Directory Path Specifications
Adjusting SQL*Plus Settings
Spooling a SQL*Plus Session
Describing Database Objects
Executing Commands from the Operating System
Clearing the Buffer and the Screen
SQL*Plus Command Review
11.2 SQL*Plus Variables
SQL*Plus Substitution Variables
SQL*Plus User-Defined Variables
SQL*Plus System Variables
11.3 Bind Variables
Bind Variable Declaration
Bind Variables in SQL Statements
11.4 SQL*Plus Scripts
Script Execution
Script Parameters
SQL*Plus Commands in Scripts
The login.sql Script
11.5 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.6 HTML in SQL*Plus
HTML in SQL*Plus
11.7 Building SQL*Plus Scripts for Automation
What Is a SQL*Plus Script?
Capturing and Using Input Parameter Values
Passing Data Values from One SQL Statement to Another
Handling Error Conditions
SQL*Plus Error Logging
11.8 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: 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 B: Answers to the Exercises
Chapter 1, 2 and 3: No exercises
Chapter 4 Exercises
Chapter 5 Exercises
Chapter 6: No exercises
Chapter 7 Exercises
Chapter 8 Exercises
Chapter 9 Exercises
Chapter 10 Exercises
Chapter 11 Exercises
Chapter 12 Exercises
Index
