Mastering Oracle SQL: Putting Oracle SQL to Work

by Sanjay Mishra, Alan Beaulieu

Overview

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 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.

Product Details

ISBN-13:	9780596552473
Publisher:	O'Reilly Media, Incorporated
Publication date:	06/22/2004
Sold by:	Barnes & Noble
Format:	eBook
Pages:	496
File size:	3 MB

About the Author

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