Practical Oracle SQL: Mastering the Full Power of Oracle Database

Table of Contents
About the Author
Acknowledgments
Introduction
Part I: Core SQL
	Chapter 1: Correlating Inline Views
		Brewery products and sales
		Scalar subqueries and multiple columns
		Correlating inline view
			Outer joining correlated inline view
		Lessons learned
	Chapter 2: Pitfalls of Set Operations
		Sets of beer
		Set operators
			Set concatenation
			The three set operators
		Multiset operators
			Multiset union
			Multiset intersect
			Multiset except
		Minus vs. multiset except
		Lessons learned
	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
			Listing column names
		Lessons learned
	Chapter 4: Tree Calculations with Recursion
		Bottles in boxes on pallets
		Multiplying hierarchical quantities
			Recursive subquery factoring
			Dynamic SQL in PL/SQL function
		Lessons learned
	Chapter 5: Functions Defined Within SQL
		Table with beer alcohol data
		Blood alcohol concentration
		Function with PRAGMA UDF
		Function in the with clause
			Encapsulated in a view
		Lessons learned
	Chapter 6: Iterative Calculations with Multidimensional Data
		Conway’s Game of Life
		Live neighbor count with the model clause
		Iterating generations
		Lessons learned
	Chapter 7: Unpivoting Columns to Rows
		Data received in columns
		Unpivoting to rows
			Do-it-yourself unpivoting
			More than one dimension and/or measure
		Using dimension tables
			Dynamic mapping to dimension tables
		Lessons learned
	Chapter 8: Pivoting Rows to Columns
		Tables for pivoting
		Pivoting single measure and dimension
			Do-it-yourself manual pivoting
		Multiple measures
			Multiple dimensions as well
		Lessons learned
	Chapter 9: Splitting Delimited Text
		Customer favorites and reviews
		Delimited single values
			Pipelined table function
			Built-in APEX table function
			Straight SQL with row generators
			Treating the string as a JSON array
		Delimited multiple values
			Custom ODCI table function
			Combining apex_string.split and substr
			Row generators and regexp_substr
			Transformation to JSON
		Lessons learned
	Chapter 10: Creating Delimited Text
		Delimited lists of products
		String aggregation
			Aggregate function listagg
			Aggregate function collect
			Custom aggregate function stragg
			Aggregate function xmlagg
		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
		Lessons learned
Part II: Analytic Functions
	Chapter 11: Analytic Partitions, Ordering, and Windows
		Sums of quantities
		Analytic syntax
			Partitions
			Ordering and windows
		Flexibility of the window clause
		Windows on value ranges
		The danger of the default window
		Lessons learned
	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
		Traditional rownum method
		Analytic functions for ranking
		Fetch only the first rows
			Handling of ties
			What the row limiting clause cannot do
		Top-N in multiple partitions
			The lateral trick for the row limiting clause
		Lessons learned
	Chapter 13: Ordered Subsets with Rolling Sums
		Data for goods picking
		Building the picking SQL
			Solving picking an order by FIFO
			Easy switch of picking principle
			Solving optimal picking route
			Solving batch picking
			Finalizing the complete picking SQL
		Lessons learned
	Chapter 14: Analyzing Activity Logs with Lead
		Picking activity log
		Analyzing departures and arrivals
		Analyzing picking activity
			Complete picking cycle analysis
		Teaser: row pattern matching
		Lessons learned
	Chapter 15: Forecasting with Linear Regression
		Sales forecasting
			Time series
			Calculating the basis for regression
			Linear regression
			Final forecast
		Lessons learned
	Chapter 16: Rolling Sums to Forecast Reaching Minimums
		Inventory, budget, and order
			The data
		Accumulating until zero
		Restocking when minimum reached
		Lessons learned
Part III: Row Pattern Matching
	Chapter 17: Up-and-Down Patterns
		The stock ticker example
		Classifying downs and ups
		Downs + ups = V shapes
			Revisiting if SAME is needed
		V + V = W shapes
			Overlapping W shapes
		Lessons learned
	Chapter 18: Grouping Data Through Patterns
		Two sets of data to group
		Three grouping conditions
			Group consecutive data
				Analytic Tabibitosan vs. match_recognize
				Consecutive dates instead of integers
				Gap detection
			Group until gap too large
			Group until fixed limit
		Lessons learned
	Chapter 19: Merging Date Ranges
		Job hire periods
			Temporal validity
		Merging overlapping ranges
			Attempts comparing to the previous row
			Better comparing to the maximum end date
			Handling the null dates
		Lessons learned
	Chapter 20: Finding Abnormal Peaks
		Web page counter history
		The counter data
			Patterns in the raw counter data
		Looking at daily visits
			Patterns in daily visits data
			More complex patterns
		Lessons learned
	Chapter 21: Bin Fitting
		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
		Lessons learned
	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
		Lessons learned
Index