Database Systems Using Oracle: A Simplified Guide to SQL and PL/SQL

✍ Scribed by Nilesh Shah

• Publisher: Pearson Education
• Year: 2004
• Tongue: English
• Leaves: 457
• Edition: 2
• Category: Library

✦ Table of Contents

COVER
CONTENTS
FOREWORD
FOREWORD
PREFACE
The Reader
The Text
The Software
Using the Text
Acknowledgments
Part 1: Database Concepts
CHAPTER 1 DATABASE CONCEPTS: A RELATIONAL APPROACH
Database: An Introduction
Relationships
Database Management System (DBMS)
The Relational Database Model
Integrity Rules
Theoretical Relational Languages
In a Nutshell . . .
Exercise Questions
CHAPTER 2 DATABASE DESIGN: DATA MODELING AND NORMALIZATION
Data Modeling
Dependency
Database Design
Normal Forms
Dependency Diagrams
Denormalization
Another Example of Normalization
In a Nutshell . . .
Exercise Questions
Part 2: Oracle SQL
CHAPTER 3 ORACLE9i: AN OVERVIEW
Personal Databases
Client/Server Databases
Oracle9i: An Introduction
The SQLPlus Environment
Structured Query Language (SQL)
Logging in to SQL Plus
SQLPlus Commands
Oracle Errors and Online Help
Alternate Text Editors
SQLPlus Worksheet
iSQL Plus
Sample Databases
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 4 ORACLE TABLES: DATA DEFINITION LANGUAGE (DDL)
Naming Rules and Conventions
Data Types
Constraints
Creating an Oracle Table
Displaying Table Information
Altering an Existing Table
Dropping a Table
Renaming a Table
Truncating a Table
Oracle’s Various Table Types
Spooling
Error Codes
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 5 WORKING WITH TABLES: DATA MANAGEMENT AND RETRIEVAL
Data Manipulation Language (DML)
Adding a New Row/Record
Customized Prompts
Updating Existing Rows/Records
Deleting Existing Rows/Records
Retrieving Data from a Table
Arithmetic Operations
Restricting Data with a WHERE Clause
Sorting
Revisiting Substitution Variables
DEFINE Command
CASE Structure
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 6 WORKING WITH TABLES: FUNCTIONS AND GROUPING
Built-In Functions
Grouping Data
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 7 MULTIPLE TABLES: JOINS AND SET OPERATORS
Join
Set Operators
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 8 SUBQUERIES: NESTED QUERIES
Subquery
Top-N Analysis
MERGE Statement
Correlated Subquery
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 9 ADVANCED FEATURES: OBJECTS, TRANSACTIONS, AND DATA CONTROL
Views
Sequences
Synonyms
Index
ROWID Pseudocolumn
Transactions
Locking Rows for Update
Controlling Access
In a Nutshell . . .
Exercise Questions
Lab Activity
SQL REVIEW: SUPPLEMENTARY EXAMPLES
Script for Creation of Tables
Script for Insertion of Rows into Tables
Insertion of Rows with Substitution Variables
Part 3: PL/SQL
CHAPTER 10 PL/SQL: A PROGRAMMING LANGUAGE
A Brief History of PL/SQL
Fundamentals of PL/SQL
PL/SQL Block Structure
Comments
Data Types
Other Data Types
Variable Declaration
Anchored Declaration
Assignment Operation
Bind Variables
Substitution Variables in PL/SQL
Printing in PL/SQL
Arithmetic Operators
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 11 MORE ON PL/SQL: CONTROL STRUCTURES AND EMBEDDED SQL
Control Structures
Nested Blocks
SQL in PL/SQL
Data Manipulation in PL/SQL
Transaction Control Statements
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 12 PL/SQL CURSORS AND EXCEPTIONS
Cursors
Implicit Cursors
Explicit Cursors
Explicit Cursor Attributes
Implicit Cursor Attributes
Cursor FOR Loops
SELECT . . . FOR UPDATE Cursor
WHERE CURRENT OF Clause
Cursor with Parameters
Cursor Variables: An Introduction
Exceptions
Types of Exceptions
More Sample Programs
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 13 PL/SQL COMPOSITE DATA TYPES: RECORDS, TABLES, AND VARRAYS
Composite Data Types
PL/SQL Records
PL/SQL Tables
PL/SQL Varrays
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 14 PL/SQL NAMED BLOCKS: PROCEDURE, FUNCTION, PACKAGE, AND TRIGGER
Procedures
Functions
Packages
Triggers
Data Dictionary Views
In a Nutshell . . .
Exercise Questions
Lab Activity
Part 4: Miscellaneous Topics
CHAPTER 15 ORACLE WITH JAVA: A TUTORIAL ON JDBC AND SQLj
Java: A Programming Language
JDBC
Sun’s JDBC Driver and the Oracle Data Source
OracleDriver and Oracle thin Driver
Java Applet: Putting It All Together
SQLj
Host Variables
SQLj Iterators
PL/SQL from SQLj
In a Nutshell . . .
Exercise Questions
Lab Activity
CHAPTER 16 ORACLE9i: ARCHITECTURE AND ADMINISTRATION
Database Administrator (DBA)
Oracle Architecture: An Overview
Installation
Connecting to the Oracle9i Database
Instance and Database
Working with Oracle Enterprise Manager (OEM)
System Privileges
Oracle Data Dictionary
In a Nutshell . . .
Exercise Questions
APPENDIX A: SAMPLE DATABASES: TABLE DEFINITIONS
The Indo–US (IU) College Student Database
The NamanNavan (N2) Corporation Employee Database
APPENDIX B: QUICK REFERENCE TO SQL AND PL/SQL SYNTAX
SQL Key Words
PL/SQL Key Words
SQL and PL/SQL Syntax
Creating a Table
Column-Level Constraint
Table-Level Constraint
Adding a Column to an Existing Table
Modifying an Existing Column
Adding a Constraint to a Table
Dropping a Column (Oracle8 Onward)
Setting a Column as Unused (Oracle8 Onward)
Dropping an Unused Column (Oracle8 Onward)
Renaming a Column (Oracle9i Onward)
Renaming a Constraint (Oracle9i Onward)
Dropping a Table
Renaming a Table
Truncating a Table
Inserting a New Row into a Table
Customized Prompts
Updating Rows
Deleting Rows
Dropping a Constraint
Enabling|Disabling a Constraint
Retrieving Data from a Table
DEFINE Command
DECODE Function
CASE Structure
Joining Tables: Equijoin or Outer Join
Set Operation
SELECT Subquery
Creating a Table Using a Subquery
Inserting a Row Using a Subquery
Inserting into Multiple Tables
Updating Using a Subquery
Deleting Using a Subquery
Top-N Query
MERGE Statement
Creating a View
Altering a View
Dropping a View
Creating a Sequence
Modifying a Sequence
Creating a Synonym
Dropping a Synonym
Creating an Index
Rebuilding an Index
Locking Rows for Update
Creating a User
Changing a User’s Password
Granting System Privileges
Granting Object Privileges
Revoking Privileges
PL/SQL Anonymous Block
PL/SQL Variable/Constant Declaration
Anchored Variable Declaration
Assignment Operation
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-END IF
CASE Statement
Basic Loop
WHILE Loop
FOR Loop
Bind/Host Variable
SELECT-INTO in PL/SQL
Explicit Cursor Declaration
Opening an Explicit Cursor
Fetching a Row from an Explicit Cursor
Closing an Explicit Cursor
Cursor FOR Loop
Cursor FOR Loop with a Subquery
WHERE CURRENT OF Clause
Cursor with SELECT-FOR UPDATE
Cursor with Parameters
REF CURSOR Type
Opening a Cursor Variable
Fetching from a Cursor Variable
EXCEPTION Section
PRAGMA EXCEPTION_INIT Directive
RAISE_APPLICATION_ERROR Procedure
Creating a PL/SQL Record
Declaring a PL/SQL Table
Declaring a PL/SQL Varray
PL/SQL Procedure
Calling a Procedure
Recompiling a Procedure
PL/SQL Function
PL/SQL Package Specification
PL/SQL Package Body
PL/SQL Trigger
Creating a Tablespace
Starting Up an Instance
Shutting Down an Instance
Creating a User from the Command Line with Various Clauses
Dropping a User
Logging into SQLPLUS from the Command Line
APPENDIX C: REFERENCE TO SQLPlus COMMANDS
SQLPlus Editing Commands
SQL*Plus File-Related Commands
APPENDIX D: OBJECT ORIENTATION
An Object
SQL Queries for Objects
Retrieving Data from an Object Table
Inserting a Row into an Object Table
Updating an Object
Deleting Rows from on Object Table
APPENDIX E: WHAT’S NEW IN ORACLE9i SQL AND PL/SQL?
New Features in SQL
New Features in PL/SQL
APPENDIX F: ADDITIONAL REFERENCES
Web Sites
Books and Other Published Material
INDEX
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
R
S
T
U
V
W