book

Mastering Oracle SQL, 2nd Edition

by Sanjay Mishra, Alan Beaulieu

June 2004

Beginner to intermediate

494 pages

13h 19m

English

O'Reilly Media, Inc.

Read now

Unlock full access

A Note Regarding Supplemental Files
Preface
Why We Wrote This BookWhat’s New in Oracle SQL?Objectives of This BookAudience for This BookPlatform and VersionStructure of This BookConventions Used in This BookUsing Code ExamplesComments and QuestionsAcknowledgmentsFrom SanjayFrom Alan
1. Introduction to SQL
1.1. What Is SQL?1.2. A Brief History of SQL1.2.1. Oracle’s SQL Implementation1.2.2. Theoretical Versus Practical Terminology1.3. A Simple Database1.4. DML Statements1.4.1. The SELECT Statement1.4.1.1. SELECT clause elements1.4.1.2. Ordering your results1.4.1.3. Removing duplicates1.4.2. The INSERT Statement1.4.2.1. Single-table inserts1.4.2.2. Multitable inserts1.4.3. The DELETE Statement1.4.4. The UPDATE Statement1.4.5. The MERGE Statement1.5. So Why Are There 17 More Chapters?
2. The WHERE Clause
2.1. Life Without WHERE2.2. WHERE to the Rescue2.3. WHERE Clause Evaluation2.4. Conditions and Expressions2.4.1. Equality/Inequality Conditions2.4.2. Membership Conditions2.4.3. Range Conditions2.4.4. Matching Conditions2.4.5. Regular Expressions2.4.6. Handling NULL2.4.7. Placement of Join Conditions2.5. WHERE to Go from Here
3. Joins
3.1. What Is a Join Query?3.2. Join Conditions3.2.1. The USING Clause3.2.2. Conditions Involving Multiple Columns3.2.3. The Natural Join Clause3.3. Types of Joins3.3.1. Cross Joins/Cartesian Products3.3.2. Inner Joins3.3.3. Outer Joins3.3.3.1. Left outer joins3.3.3.2. Right outer joins3.3.3.3. Full outer joins3.3.4. Equi-Joins Versus Non-Equi-Joins3.3.5. Self Joins3.3.5.1. Self outer joins3.3.5.2. Self non-equi-joins3.3.6. Partition Outer Joins3.4. Joins and Subqueries3.5. DML Statements on a Join View3.5.1. Key-Preserved Tables3.5.2. INSERT Statements on a Join View3.5.3. DELETE Statements on a Join View3.5.4. UPDATE Statements on a Join View3.5.5. Data Dictionary Views to Find Updatable Columns3.5.6. Impact of WITH CHECK OPTION
4. Group Operations
4.1. Aggregate Functions4.1.1. NULLs and Aggregate Functions4.1.2. Use of DISTINCT and ALL4.2. The GROUP BY Clause4.2.1. Correspondence Between SELECT and GROUP BY4.2.1.1. Aggregate expressions generally require a GROUP BY clause4.2.1.2. GROUP BY clause must include all nonaggregate expressions4.2.1.3. Aggregate functions not allowed in GROUP BY clause4.2.1.4. Constants can be omitted from the GROUP BY clause4.2.1.5. Scalar functions may be grouped by their underlying column4.2.1.6. Concatenated columns may be grouped in either of two ways4.2.1.7. You can sometimes exclude a nonaggregate expression from the GROUP BY clause4.2.1.8. You are not required to show your GROUP BY columns4.2.2. GROUP BY Clause and NULL Values4.2.3. GROUP BY Clause with WHERE Clause4.3. The HAVING Clause4.4. Nested Group Operations
5. Subqueries
5.1. What Is a Subquery?5.2. Noncorrelated Subqueries5.2.1. Single-Row, Single-Column Subqueries5.2.2. Multiple-Row, Single-Column Subqueries5.2.3. Multiple-Column Subqueries5.2.4. The WITH Clause5.3. Correlated Subqueries5.4. Inline Views5.4.1. Inline View Basics5.4.2. Query Execution5.4.3. Data Set Fabrication5.4.4. Overcoming SQL Restrictions5.4.4.1. Hierarchical queries5.4.4.2. Aggregate queries5.4.5. Inline Views in DML Statements5.4.6. Restricting Access Using WITH CHECK OPTION5.4.7. Global Inline Views5.5. Subquery Case Study: The Top N Performers5.5.1. A Look at the Data5.5.2. Your Assignment5.5.3. Second Attempt5.5.4. Final Answer
6. Handling Temporal Data
6.1. Time Zones6.1.1. Database Time Zone6.1.2. Session Time Zone6.2. Temporal Data Types in Oracle6.2.1. The DATE Data Type6.2.2. The TIMESTAMP Data Types6.2.2.1. TIMESTAMP6.2.2.2. TIMESTAMP WITH TIME ZONE6.2.2.3. TIMESTAMP WITH LOCAL TIME ZONE6.2.3. The INTERVAL Data Types6.2.3.1. INTERVAL YEAR TO MONTH6.2.3.2. INTERVAL DAY TO SECOND6.3. Literals of Temporal Types6.3.1. DATE Literals6.3.2. TIMESTAMP Literals6.3.3. INTERVAL Literals6.3.3.1. YEAR TO MONTH interval literals6.3.3.2. DAY TO SECOND interval literals6.4. Getting Temporal Data In and Out of a Database6.4.1. TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ6.4.1.1. Using the default formats6.4.1.2. Specifying a format6.4.1.3. Converting to TIMESTAMP WITH LOCAL TIME ZONE6.4.2. TO_YMINTERVAL and TO_DSINTERVAL6.4.3. NUMTOYMINTERVAL and NUMTODSINTERVAL6.4.4. TO_CHAR6.5. Date and Time Formats6.5.1. AD/BC Indicators6.5.2. AM/PM Indicators6.5.3. Case-Sensitivity of Formats6.5.4. Two-Digit Years6.5.5. ISO Standard Issues6.5.5.1. ISO standard weeks6.5.5.2. ISO standard year6.5.6. Database Parameters6.6. Manipulating Temporal Data6.6.1. Using the Built-in Temporal Functions6.6.2. Addition6.6.2.1. Adding numbers to a DATE6.6.2.2. Adding months to a DATE6.6.2.3. Adding true INTERVAL values rather than numbers6.6.3. Subtraction6.6.3.1. Subtracting one DATE from another6.6.3.2. Subtracting one TIMESTAMP from another6.6.3.3. Subtracting a number from a DATE6.6.3.4. Subtracting months from a DATE6.6.3.5. Number of months between two DATEs6.6.3.6. Time interval between two DATEs6.6.3.7. Subtracting an INTERVAL from a DATE or TIMESTAMP6.6.4. Determining the First Day of the Month6.6.5. Rounding and Truncating Dates6.6.6. SELECTing Data Based on Date Ranges6.6.7. Creating a Date Pivot Table6.6.8. Summarizing by a Date/Time Element
7. Set Operations
7.1. Set Operators7.1.1. UNION ALL7.1.2. UNION7.1.3. INTERSECT7.1.4. MINUS7.2. Precedence of Set Operators7.3. Comparing Two Tables7.4. Using NULLs in Compound Queries7.5. Rules and Restrictions on Set Operations
8. Hierarchical Queries
8.1. Representing Hierarchical Information8.2. Simple Hierarchy Operations8.2.1. Finding Root Nodes8.2.2. Finding a Node’s Immediate Parent8.2.3. Finding Leaf Nodes8.3. Oracle SQL Extensions8.3.1. Tree Traversal Using ANSI SQL8.3.2. START WITH . . . CONNECT BY and PRIOR8.3.3. The LEVEL Pseudocolumn8.4. Complex Hierarchy Operations8.4.1. Finding the Number of Levels8.4.2. Listing Records in Hierarchical Order8.4.3. Checking for Ascendancy8.4.4. Deleting a Subtree8.4.5. Listing Multiple Root Nodes8.4.6. Listing the Top Few Levels of a Hierarchy8.4.7. Aggregating a Hierarchy8.4.8. Ordering Hierarchical Data8.4.9. Finding the Path to a Node8.5. Restrictions on Hierarchical Queries8.6. Enhancements in Oracle Database 10g8.6.1. Getting Data from the Root Row8.6.2. Ignoring Cycles8.6.3. Identifying Cycles8.6.4. Identifying Leaf Nodes

9. DECODE and CASE
9.1. DECODE, NULLIF, NVL, and NVL29.1.1. DECODE9.1.2. NULLIF9.1.3. NVL and NVL29.2. The Case for CASE9.2.1. Searched CASE Expressions9.2.2. Simple CASE Expressions9.3. DECODE and CASE Examples9.3.1. Result Set Transformations9.3.2. Selective Function Execution9.3.3. Conditional Update9.3.4. Optional Update9.3.5. Selective Aggregation9.3.6. Checking for Existence9.3.7. Division by Zero Errors9.3.8. State Transitions
10. Partitioning
10.1. Partitioning Concepts10.2. Partitioning Tables10.3. Partitioning Indexes10.4. Partitioning Methods10.4.1. Range Partitioning10.4.2. Hash Partitioning10.4.3. Composite Range-Hash Partitioning10.4.4. List Partitioning10.4.5. Composite Range-List Partitioning10.5. Specifying Partitions10.6. Partition Pruning
11. PL/SQL
11.1. What Is PL/SQL?11.2. Procedures, Functions, and Packages11.3. Calling Stored Functions from Queries11.3.1. Stored Functions and Views11.3.2. Avoiding Table Joins11.3.3. Deterministic Functions11.4. Restrictions on Calling PL/SQL from SQL11.4.1. Purity Level11.4.2. Trust Me...11.4.3. Other Restrictions11.5. Stored Functions in DML Statements11.6. The SQL Inside Your PL/SQL
12. Objects and Collections
12.1. Object Types12.1.1. Subtyp es12.1.2. Object Attributes12.1.3. Object Tables12.1.4. Object Parameters12.2. Collection Types12.2.1. Variable Arrays12.2.2. Nested Tables12.3. Collection Instantiation12.4. Querying Collections12.5. Collection Unnesting12.6. Collection Functions12.7. Comparing Collections12.8. Manipulating Collections12.9. Multilevel Collections12.9.1. Querying Multilevel Collections12.9.2. DML Operations on Multilevel Collections
13. Advanced Group Operations
13.1. Multiple Summary Levels13.1.1. UNION13.1.2. ROLLUP13.1.3. Partial ROLLUPs13.1.4. CUBE13.1.5. Partial CUBE13.1.6. The GROUPING Function13.1.7. GROUPING SETS13.2. Pushing the GROUPING Envelope13.2.1. Repeated Column Names in the GROUP BY Clause13.2.2. Grouping on Composite Columns13.2.3. Concatenated Groupings13.2.3.1. Concatenated groupings with GROUPING SETS13.2.3.2. ROLLUP and CUBE as arguments to GROUPING SETS13.3. The GROUPING_ID and GROUP_ID Functions13.3.1. GROUPING_ID13.3.2. GROUPING and GROUPING_ID in ORDER BY13.3.3. GROUP_ID
14. Advanced Analytic SQL
14.1. Analytic SQL Overview14.2. Ranking Functions14.2.1. RANK, DENSE_RANK, and ROW_NUMBER14.2.1.1. Handling NULLs14.2.1.2. Top/bottom N queries14.2.1.3. FIRST/LAST14.2.2. NTILE14.2.3. WIDTH_BUCKET14.2.4. CUME_DIST and PERCENT_RANK14.2.5. Hypothetical Functions14.3. Windowing Functions14.3.1. Working with Ranges14.3.2. FIRST_VALUE and LAST_VALUE14.3.3. LAG/LEAD Functions14.4. Reporting Functions14.4.1. Report Partitions14.4.2. RATIO_TO_REPORT14.5. Summary
15. SQL Best Practices
15.1. Know When to Use Specific Constructs15.1.1. EXISTS Is Preferable to DISTINCT15.1.2. WHERE Versus HAVING15.1.3. UNION Versus UNION ALL15.1.4. LEFT Versus RIGHT OUTER JOIN15.2. Avoid Unnecessary Parsing15.2.1. Using Bind Variables15.2.2. Using Table Aliases15.3. Consider Literal SQL for Decision-Support Systems
16. XML
16.1. What Is XML?16.1.1. XML Resources16.1.2. Oracle and XML16.2. Storing XML Data16.2.1. Storing XML as a CLOB16.2.2. Inspecting the XML Document16.2.2.1. XPath16.2.2.2. The extract( ) member function16.2.2.3. The extractValue( ) member function16.2.2.4. The existsNode( ) member function16.2.2.5. Moving data to relational tables16.2.3. Storing XML as a Set of Objects16.2.3.1. Registering your schema16.2.3.2. Assigning the schema to a column16.2.3.3. Inserting data16.2.3.4. XMLType validity functions16.2.3.5. Updating document content16.3. Generating XML Documents16.3.1. XMLElement( )16.3.2. XMLAgg( )16.3.3. XMLForest( )16.3.4. Putting It All Together16.4. Summary
17. Regular Expressions
17.1. Elementary Regular Expression Syntax17.1.1. Matching a Single Character17.1.2. Matching Any of a Set of Characters17.1.3. Matching Repeating Sequences17.1.4. Defining Alternate Possibilities17.1.5. Subexpressions17.1.6. Anchoring an Expression17.1.7. Understanding Greediness17.2. Advanced Function Options17.3. Advanced Regular Expression Syntax17.3.1. Using Backreferences17.3.2. Using Named Character Classes17.3.3. Specifying Collation Elements17.3.4. Defining Equivalence Classes
18. Model Queries
18.1. Basic Elements of a Model Query18.2. Cell References18.2.1. Symbolic Cell References18.2.2. Positional Cell References18.2.3. Combined Positional and Symbolic References18.2.4. NULL Measures and Missing Cells18.2.5. UNIQUE DIMENSION/UNIQUE SINGLE REFERENCE18.2.6. Returning Rows18.3. Rules18.3.1. Constructing a Rule18.3.1.1. CV( )18.3.1.2. ANY18.3.1.3. FOR loops18.3.1.4. IS ANY18.3.1.5. IS PRESENT18.3.1.6. PRESENTV18.3.1.7. PRESENTNNV18.3.2. Range References on the Righthand Side18.3.3. Order of Evaluation of Rules18.3.3.1. SEQUENTIAL ORDER18.3.3.2. AUTOMATIC ORDER18.3.4. Creating and Updating Cells18.4. Iterative Models18.4.1. Knowing how many iterations have occurred18.4.1.1. Referencing values from the previous iteration18.5. Reference Models
A. Oracle’s Old Join Syntax
A.1. Old Inner Join SyntaxA.2. Old Outer Join SyntaxA.2.1. Restrictions on Old Outer Join SyntaxA.2.2. Full Outer Join Using the Old SyntaxA.3. Advantages of the New Join Syntax
About the Authors
Colophon
Copyright

Content preview from Mastering Oracle SQL, 2nd Edition

Chapter 17. Regular Expressions

One of the most exciting SQL features in Oracle Database 10g is support for regular expressions. Open source databases such as MySQL and PostgreSQL have supported regular expressions for years. Frankly, we were tired of having that thrown in our faces, of feeling one-upped by the open source competition. In the Oracle Database 10g release, Oracle has implemented regular expression support with a vengeance. We were surprised and delighted at what we found. You will be, too.

Elementary Regular Expression Syntax

Regular expressions provide a syntax to describe patterns in text. Being able to describe a passage of text gives you power, the power to manipulate that text in ways that would otherwise be impossible. For example, regular expressions enable you to easily perform an operation such as the following:

Find all apparent phone numbers in a free-text, comment column, and reformat those numbers, at least the U.S. and Canadian numbers, so that all appear in the form: (999) 999-9999.

By the end of this chapter, we’ll have shown you exactly how to implement this directive. For now, we want to familiarize you with some elementary regular expression syntax.

Matching a Single Character

The most basic regular expression metacharacter is probably the period (.). Use it to match any single character, except for the newline (usually chr(10)). For example, assume that users of our book’s example database have taken to entering employee address and phone number ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596006322Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Mastering Oracle SQL, 2nd Edition

by Sanjay Mishra, Alan Beaulieu

Chapter 17. Regular Expressions