Mastering Oracle SQL, 2/e (Paperback)

Description:

The vast majority of Oracle SQL books discuss some syntax, provide the barest rudiments of using Oracle SQL, and perhaps include a few simple examples. It might be enough to pass a survey course, or give you some buzz words to drop in conversation with real Oracle DBAs. But if you use Oracle SQL on a regular basis, you want much more. You want to access the full power of SQL to write queries in an Oracle environment. You want a solid understanding of what's possible with Oracle SQL, creative techniques for writing effective and accurate queries, and the practical, hands-on information that leads to true mastery of the language. Simply put, you want useful, expert best practices that can be put to work immediately, not just non-vendor specific overview or theory.

Updated to cover the latest version of Oracle, Oracle 10g, this new edition of the highly regarded Mastering Oracle SQL has a stronger focus on technique and on Oracle's implementation of SQL than any other book on the market. It covers Oracle s vast library of built-in functions, the full range of Oracle SQL query-writing features, regular expression support, new aggregate and analytic functions, subqueries in the SELECT and WITH clauses, multiset union operators, enhanced support for hierarchical queries: leaf and loop detection, and the CONNECT_BY_ROOT operator, new partitioning methods (some introduced in Oracle9i Release 2), and the native XML datatype, XMLType.

Mastering Oracle SQL, 2nd Edition fills the gap between the sometimes spotty vendor documentation, and other books on SQL that just don't explore the full depth of what is possible with Oracle-specific SQL. For those who want to harness the untapped (and often overlooked) power of Oracle SQL, this essential guide for putting Oracle SQL to work will prove invaluable.

Table of Contents:

Preface 

1. Introduction to SQL

      What Is SQL? 

      A Brief History of SQL 

      A Simple Database 

      DML Statements 

      So Why Are There 17 More Chapters? 

2. The WHERE Clause

      Life Without WHERE 

      WHERE to the Rescue 

      WHERE Clause Evaluation 

      Conditions and Expressions 

      WHERE to Go from Here 

3. Joins

      What Is a Join Query? 

      Join Conditions 

      Types of Joins 

      Joins and Subqueries 

      DML Statements on a Join View 

4. Group Operations

      Aggregate Functions 

      The GROUP BY Clause 

      The HAVING Clause 

      Nested Group Operations 

5. Subqueries

      What Is a Subquery? 

      Noncorrelated Subqueries 

      Correlated Subqueries 

      Inline Views 

      Subquery Case Study: The Top N Performers 

6. Handling Temporal Data

      Time Zones 

      Temporal Data Types in Oracle 

      Literals of Temporal Types 

      Getting Temporal Data In and Out of a Database 

      Date and Time Formats 

      Manipulating Temporal Data 

7. Set Operations

      Set Operators 

      Precedence of Set Operators 

      Comparing Two Tables 

      Using NULLs in Compound Queries 

      Rules and Restrictions on Set Operations 

8. Hierarchical Queries

      Representing Hierarchical Information 

      Simple Hierarchy Operations 

      Oracle SQL Extensions 

      Complex Hierarchy Operations 

      Restrictions on Hierarchical Queries 

      Enhancements in Oracle Database 10g 

9. DECODE and CASE

      DECODE, NULLIF, NVL, and NVL2 

      The Case for CASE 

      DECODE and CASE Examples 

10. Partitioning

      Partitioning Concepts 

      Partitioning Tables 

      Partitioning Indexes 

      Partitioning Methods 

      Specifying Partitions 

      Partition Pruning 

11. PL/SQL

      What Is PL/SQL? 

      Procedures, Functions, and Packages 

      Calling Stored Functions from Queries 

      Restrictions on Calling PL/SQL from SQL 

      Stored Functions in DML Statements 

      The SQL Inside Your PL/SQL 

12. Objects and Collections

      Object Types 

      Collection Types 

      Collection Instantiation 

      Querying Collections 

      Collection Unnesting 

      Collection Functions 

      Comparing Collections 

      Manipulating Collections 

      Multilevel Collections 

13. Advanced Group Operations

      Multiple Summary Levels 

      Pushing the GROUPING Envelope 

      The GROUPING_ID and GROUP_ID Functions 

14. Advanced Analytic SQL

      Analytic SQL Overview 

      Ranking Functions 

      Windowing Functions 

      Reporting Functions 

      Summary 

15. SQL Best Practices

      Know When to Use Specific Constructs 

      Avoid Unnecessary Parsing 

      Consider Literal SQL for Decision-Support Systems 

16. XML

      What Is XML? 

      Storing XML Data 

      Generating XML Documents 

      Summary 

17. Regular Expressions

      Elementary Regular Expression Syntax 

      Advanced Function Options 

      Advanced Regular Expression Syntax 

18. Model Queries

      Basic Elements of a Model Query 

      Cell References 

      Rules 

      Iterative Models 

      Reference Models 

Appendix: Oracle's Old Join Syntax 

Index