Mastering Oracle Sql.

by Mishra, Sanjay

• ISBN13: 9780596006327
• ISBN10: 0596006322
• Edition: 2nd
• Format: Paperback
• Copyright: 2004-06-01
• Publisher: Oreilly & Associates Inc

Summary

Updated for Oracle Database 10g, our new edition of Mastering Oracle SQL goes far beyond other books, delivering the full power of SQL to write queries in an Oracle environment. This guide 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, and the native XML datatype, XMLType. More than just a manual, this book is about creative techniques for writing effective and accurate queries.

Author Biography

Sanjay Mishra is a certified Oracle database administrator with more than ten years of IT experience. He has been involved in the design, architecture, and implementation of many mission-critical and decision support databases. He has worked extensively in the areas of database architecture, database management, backup / recovery, performance tuning, Oracle Parallel Server, and parallel execution. He has a Bachelor of Science degree in Electrical Engineering, and a Master of Engineering degree in Systems Science and Automation. He is the coauthor of Oracle Parallel Processing and Oracle SQL Loader: The Definitive Guide (both published by O'Reilly & Associates). Presently, he works as a database architect at Dallas Based i2 Technologies, and can be reached at [e-mail].

Alan Beaulieu has been designing, building, and implementing custom database applications for over 13 years. He currently runs his own consulting company that specializes in designing Oracle databases and supporting services in the fields of Financial Services and Telecommunications. In building large databases for both OLTP and OLAP environments, Alan utilizes such Oracle features as Parallel Query, Partitioning, and Parallel Server. Alan has a Bachelor of Science degree in Operations Research from the Cornell University School of Engineering. He lives in Massachusetts with his wife and two daughters and can be reached at [e-mail].

Table of Contents

Preface	ix
1. Introduction to SQL	1	(17)
What Is SQL?	1	(2)
A Brief History of SQL	3	(1)
A Simple Database	4	(1)
DML Statements	5	(12)
So Why Are There 17 More Chapters?	17	(1)
2. The WHERE Clause	18	(13)
Life Without WHERE	18	(1)
WHERE to the Rescue	19	(1)
WHERE Clause Evaluation	20	(3)
Conditions and Expressions	23	(7)
WHERE to Go from Here	30	(1)
3. Joins	31	(31)
What Is a Join Query?	31	(1)
Join Conditions	32	(5)
Types of Joins	37	(16)
Joins and Subqueries	53	(1)
DML Statements on a Join View	53	(9)
4. Group Operations	62	(18)
Aggregate Functions	62	(4)
The GROUP BY Clause	66	(8)
The HAVING Clause	74	(3)
Nested Group Operations	77	(3)
5. Subqueries	80	(32)
What Is a Subquery?	80	(1)
Noncorrelated Subqueries	81	(7)
Correlated Subqueries	88	(3)
Inline Views	91	(15)
Subquery Case Study: The Top N Performers	106	(6)
6. Handling Temporal Data	112	(54)
Time Zones	112	(2)
Temporal Data Types in Oracle	114	(6)
Literals of Temporal Types	120	(6)
Getting Temporal Data In and Out of a Database	126	(8)
Date and Time Formats	134	(12)
Manipulating Temporal Data	146	(20)
7. Set Operations	166	(15)
Set Operators	167	(5)
Precedence of Set Operators	172	(1)
Comparing Two Tables	173	(3)
Using NULLs in Compound Queries	176	(2)
Rules and Restrictions on Set Operations	178	(3)
8. Hierarchical Queries	181	(29)
Representing Hierarchical Information	181	(3)
Simple Hierarchy Operations	184	(3)
Oracle SQL Extensions	187	(6)
Complex Hierarchy Operations	193	(11)
Restrictions on Hierarchical Queries	204	(1)
Enhancements in Oracle Database 10g	204	(6)
9. DECODE and CASE	210	(20)
DECODE, NULLIF, NVL, and NVL2	210	(4)
The Case for CASE	214	(3)
DECODE and CASE Examples	217	(13)
10. Partitioning	230	(13)
Partitioning Concepts	230	(1)
Partitioning Tables	231	(1)
Partitioning Indexes	231	(1)
Partitioning Methods	232	(6)
Specifying Partitions	238	(1)
Partition Pruning	239	(4)
11. PL/SQL	243	(15)
What Is PL/SQL?	243	(1)
Procedures, Functions, and Packages	244	(2)
Calling Stored Functions from Queries	246	(5)
Restrictions on Calling PL/SQL from SQL	251	(3)
Stored Functions in DML Statements	254	(2)
The SQL Inside Your PL/SQL	256	(2)
12. Objects and Collections	258	(21)
Object Types	258	(9)
Collection Types	267	(1)
Collection Instantiation	268	(1)
Querying Collections	269	(1)
Collection Unnesting	270	(1)
Collection Functions	271	(2)
Comparing Collections	273	(2)
Manipulating Collections	275	(1)
Multilevel Collections	276	(3)
13. Advanced Group Operations	279	(41)
Multiple Summary Levels	279	(22)
Pushing the GROUPING Envelope	301	(10)
The GROUPING_ID and GROUP_ID Functions	311	(9)
14. Advanced Analytic SQL	320	(32)
Analytic SQL Overview	320	(6)
Ranking Functions	326	(13)
Windowing Functions	339	(7)
Reporting Functions	346	(5)
Summary	351	(1)
15. SQL Best Practices	352	(13)
Know When to Use Specific Constructs	352	(6)
Avoid Unnecessary Parsing	358	(5)
Consider Literal SQL for Decision-Support Systems	363	(2)
16. XML	365	(24)
What Is XML?	365	(1)
Storing XML Data	366	(16)
Generating XML Documents	382	(5)
Summary	387	(2)
17. Regular Expressions	389	(28)
Elementary Regular Expression Syntax	389	(12)
Advanced Function Options	401	(4)
Advanced Regular Expression Syntax	405	(12)
18. Model Queries	417	(32)
Basic Elements of a Model Query	417	(2)
Cell References	419	(8)
Rules	427	(15)
Iterative Models	442	(3)
Reference Models	445	(4)
Appendix: Oracle's Old Join Syntax	449	(6)
Index	455