Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts

Table of contents

1. Copyright
2. Acknowledgments
3. Introduction
4. About the Author
5. Understanding Relational Databases
   1. Entities and Attributes
   2. Entity Relationship Diagram Concepts
   3. Keys and Joins
   4. Creating an Entity Relationship Diagram
   5. Converting the Entity Relationship Diagram to a Relational Model
   6. The Data Schema Diagram
   7. Table Relationship Diagram
   8. Employee Database Table Relationship Diagram
   9. What’s Next?
   10. Practice
6. Building the Database with the Data Definition Language
   1. Logging on to SQL*Plus
   2. Using the SQL*Plus Editor
   3. Database Objects That Store Data
   4. Oracle Object-Oriented Features
   5. TOAD
   6. What’s Next?
   7. Practice
7. The Data Control and Data Manipulation Languages and the Data Dictionary
   1. Creating User Accounts
   2. Data Control Language (DCL)
   3. Data Manipulation Language (DML)
   4. Oracle’s Data Dictionary
   5. What’s Next?
   6. Practice
8. Retrieving Database Records Using SQL: The Select, Where, and Order By Clauses
   1. Identifying Table and Column Names
   2. Making a Simple Database Query
   3. Limiting the Database Records Retrieved
   4. Ordering Records
   5. Printing the Results of Your Query
   6. What’s Next?
   7. Practice
9. Retrieving Records Using SQL: The FROM, GROUP BY, and HAVING Clauses
   1. The FROM Clause
   2. Computing Group Values
   3. Limiting Selected Records by Using a Group Function
   4. What’s Next?
   5. Practice
10. Creating New Values with Character Functions
    1. Character Functions
    2. Case Expressions
    3. What’s Next?
    4. Practice
11. Creating New Values with Numeric and Date Functions
    1. The MOD and TO_NUMBER Functions
    2. Using the NVL and the NVL2 Functions
    3. The ROUND Function
    4. The TRUNC Function
    5. Dates and Date Functions
    6. Date Functions
    7. What’s Next?
    8. Practice
12. Using Set Operators, Subqueries, and Variables
    1. Set Operator Rules
    2. Subqueries
    3. Variables
    4. What’s Next?
    5. Practice
13. Analytical Processing with SQL
    1. ROLLUP
    2. CUBE
    3. The GROUPING Function
    4. Ranking Functions
    5. Windowing
    6. Statistical Functions
    7. What’s Next?
    8. Practice
14. Using Database and Materialized Views
    1. Administering Database Views
    2. DML and Views
    3. Why Use Views?
    4. Materialized Views
    5. What’s Next?
    6. Practice
15. Using SQL*Plus as a Report-Writing Tool
    1. Title and Footer Settings
    2. Formatting Columns
    3. Breaks
    4. Subtotals
    5. Sending the Output to the Printer
    6. SET Commands
    7. What’s Next?
    8. Practice
16. What You Can Do If Your SQL Does Not Perform
    1. Indexes
    2. Optimization
    3. Join Operations
    4. EXPLAIN PLAN Statement
    5. Dynamic Performance Views
    6. Trace
    7. Odds and Ends
    8. What’s Next?
17. Using Business Objects
    1. Data Warehouses
    2. Business Objects
    3. Drilling Down and Exceptions
    4. Oracle Discoverer 4.0: The OLAP Tool of Choice
    5. What’s Next?
18. The Basics of PL/SQL
    1. Writing Your First Program
    2. Executing the PL/SQL Program
    3. Code Block Components and Block Labels
    4. Declaring Variables and Assigning Values
    5. The IF-THEN-ELSE and ELSIF Structures
    6. Cursors
    7. Loops
    8. FOR Loops
    9. What’s Next?
    10. Practice
19. Handling Exceptions and Using Named Procedures
    1. Exception Handling
    2. PL/SQL Records and Arrays
    3. Named Procedures
    4. Functions
    5. Packages
    6. What’s Next?
    7. Practice
20. Advanced PL/SQL Topics
    1. Cursor Variables
    2. Processing Objects
    3. PL/SQL and Java
    4. What’s Next?
21. Glossary
22. Answers
    1. Chapter 1
    2. Chapter 2
    3. Chapter 3
    4. Chapter 4
    5. Chapter 5
    6. Chapter 6
    7. Chapter 7
    8. Chapter 8
    9. Chapter 9
    10. Chapter 10
    11. Chapter 11
    12. Chapter 14
    13. Chapter 15
23. Bibliography