You are previewing Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts.
O'Reilly logo
Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts

Book Description

Concise and practical, this indispensable volume brings the world’s most popular Relational Database Management System (RDBMS), its query language SQL, and its programming language PL/SQL into clear focus for today’s busy database professional or business analyst. Covering the most commonly used Oracle features, Oracle SQL and PL/SQL Handbook is ideal for the developer or business user challenged with implementing, maintaining, and retrieving mission-critical data within the Oracle database environment.

Broadly designed as both a basic reference and how-to, this book offers extensive coverage of Oracle’s SQL language and database concepts, providing an excellent review for the Oracle certification exams. The initial chapters offer a complete introduction to the relational database environment, including a discussion of how to use the logical data model to understand the database. Oracle database objects are explored, along with uses of the Data Definition Language (DDL), the Data Control Language (DCL), and the Data Manipulation Language (DML). Later chapters offer complete coverage of the Select command used to retrieve information from the database. Also included are coverage of Oracle’s new analytic functions, a chapter on performance-tuning techniqes needed for complex SQL, and a detailed overview of Oracle’s PL/SQL language. In addition, many chapters provide practice questions designed to reinforce newly introduced concepts, as well as numerous examples of SQL techniques.

The broad-based and comprehensive coverage also includes:

  • Understanding the database using entity relationship diagrams and database schema diagrams

  • Applying the Data Definition, Data Control, and Data Manipulation languages to create and maintain the database

  • Using the SQL language to retrieve information from the database

  • Creating special business information using Oracle’s new analytic functions

  • Making your SQL perform better with common troubleshooting techniques

  • Producing business information with business objects

  • Using Java with PL/SQL

  • Authoritative and practical, Oracle SQL and PL/SQL Handbook provides today’s administrator and business analyst alike with the most comprehensive sourcebook of solutions and techniques for improving their use of Oracle.


    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