Expert Oracle Database Architecture Table of Contents
Return to Expert Oracle Database Architecture
Oracle Database:Oracle SQL, PL/SQL, Oracle on Kubernetes, IBM Mainframe Oracle, Oracle Database topics, Oracle Cloud, Oracle, Oracle Database books, Awesome Oracle Database (navbar_oracledb)
Part I: Core SQL
Chapter 1: Correlating Inline Views
Scalar subqueries and multiple columns
Outer joining correlated inline view
Chapter 2: Pitfalls of Set Operations
The three set operators
Minus vs. multiset except
Chapter 3: Divide and Conquer with Subquery Factoring
Products and sales data
Best-selling years of the less strong beers
Modularization using the with clause
Multiple uses of the same subquery
Chapter 4: Tree Calculations with Recursion
Multiplying hierarchical quantities
Dynamic SQL in PL/SQL function
Chapter 5: Functions Defined Within SQL
Function with PRAGMA UDF
Encapsulated in a view
Chapter 6: Iterative Calculations with Multidimensional Data
Live neighbor count with the model clause
Chapter 7: Unpivoting Columns to Rows
More than one dimension and/or measure
Dynamic mapping to dimension tables
Chapter 8: Pivoting Rows to Columns
Pivoting single measure and dimension
Do-it-yourself manual pivoting
Multiple measures
Multiple dimensions as well
Chapter 9: Splitting Delimited Text
Customer favorites and reviews
Straight SQL with row generators
Treating the string as a JSON array
Combining apex_string.split and substr
Row generators and regexp_substr
Chapter 10: Creating Delimited Text
Delimited lists of products
Custom aggregate function stragg
When it doesn’t fit in a VARCHAR2
Get just the first part of the result
Try to make it fit with reduced data
Use a CLOB instead of a VARCHAR2
Part II: Analytic Functions
Chapter 11: Analytic Partitions, Ordering, and Windows
Sums of quantities
Flexibility of the window clause
Windows on value ranges
The danger of the default window
Chapter 12: Answering Top-N Questions
Top-N of sales data
Which kind of Top-3 do you mean?
The sales data for the beer
Analytic functions for ranking
Handling of ties
What the row limiting clause cannot do
The lateral trick for the row limiting clause
Chapter 13: Ordered Subsets with Rolling Sums
Solving picking an order by FIFO
Easy switch of picking principle
Finalizing the complete picking SQL
Chapter 14: Analyzing Activity Logs with Lead
Analyzing departures and arrivals
Complete picking cycle analysis
Teaser: row pattern matching
Chapter 15: Forecasting with Linear Regression
Calculating the basis for regression
Chapter 16: Rolling Sums to Forecast Reaching Minimums
The data
Accumulating until zero
Restocking when minimum reached
Part III: Row Pattern Matching
Chapter 17: Up-and-Down Patterns
Classifying downs and ups
V + V = W shapes
Overlapping W shapes
Chapter 18: Grouping Data Through Patterns
Chapter 19: Merging Date Ranges
Attempts comparing to the previous row
Better comparing to the maximum end date
Chapter 20: Finding Abnormal Peaks
The counter data
Patterns in the raw counter data
More complex patterns
Inventory to be packed in boxes
Bin fitting with unlimited number of bins of limited capacity
Showing where box capacity is too small
Bin fitting with limited number of bins of unlimited capacity
Chapter 22: Counting Children in Trees
Hierarchical tree of employees
Counting subordinates of all levels
Counting with row pattern matching
The details of each match
Fiddling with the output