Advanced SQL Queries for Oracle Databases

 

This course is designed for developers, analysts, DBAs, and other who need to design and code efficient queries to solve complex business problems.
 

Brief Review as Needed

• Relational Database Concepts
• Basic SELECT command and Oracle datatypes

Advanced Column Types and Datatypes

• Virtual columns
• Invisible columns
• Identity columns
• Extended string datatypes
• Datetime datatypes
• Workshop

Advanced Restricting and Sorting Data

• Top-N queries using FETCH
• FETCH FIRST [WITH TIES]
• FETCH with Offset
• FETCH a percent
• FETCH a random SAMPLE
• FETCH a random SAMPLE problem with non-key preserved views
• Alternative to SAMPLE clause
• Repeatable SAMPLE
• Case-insensitive sort
• Case-insensitive search
• Effect on index use
• Workshop

Advanced Sorting Data Using Connect By

• Using CONNECT BY for Hierarchical queries
• CONNECT BY syntax
• LEVEL
• Problem with WHERE clause
• SIBLING and LEVEL sorts
• Hierarchy path
• CONNECT BY joins
• Workshop

Review as Needed

• Single Row Functions
• Using Conversion Functions and Conditional Expressions

Advanced Use of DECODE, CASE, PIVOT, UNPIVOT

• Brief review of DECODE and CASE basics as needed
• Simple Case vs Searched Case
• Range comparisons
• Horizontal histograms
• Vertical histograms
• Flip table on side with CASE
• Flip table on side with PIVOT and UNPIVOT
• Workshop

Regular Expressions

• Purpose
• REGEXP_LIKE
• Metasymbols
• Perl expressions
• Matching sets and repeaters
• REGEXP_SUBSTR
• REGEXP_REPLACE and Backreferences
• POSIX operators
• REGEXP_COUNT
• Greedy vs Non-Greedy
• Workshop

Advanced Reporting of Aggregated Data Using Group Functions

• Brief review of group function basics as needed
• LISTAGG
• FIRST and LAST
• ROLLUP and Partial ROLLUP
• CUBE
• GROUPING function
• GROUPING SETS
• Workshop

Advanced Reporting Using Analytic Functions

• Purpose
• Syntax
• PARTITION BY clause
• ORDER BY clause
• Windowing clause
• Row vs Range windows
• Ranking functions
• Top-N queries
• LEAD and LAG
• LISTAGG function
• MATCH_RECOGNIZE function
• More advanced analytic functions
• Workshop

Advanced Displaying Data from Multiple Tables

• Brief review of join basics as needed
• Unqualified name issues
• Multi-table joins
• INNER vs OUTER vs CROSS vs Self joins
• ANSI standard syntax
• Pros and Cons of NATURAL, USING, and ON
• Workshop

Advanced Use of Subqueries

• Brief review of subquery basics as needed
• IN operator
• Avoiding errors when using NOT IN
• EXISTS and NOT EXISTS
• TOP-N queries using subqueries
• Simple vs Correlated subqueries
• Beware response time issues with Correlated subqueries
• Subquery factoring clause
• Star Transformations: Converting joins to subqueries
• Workshop

Advanced Use of Set Operators

• Brief review of set operator basics
• UNION vs UNION ALL vs INTERSECT vs MINUS
• Key concepts
• Precedence rules
• Workshop

Advanced Data Manipulation

• Brief review of data manipulation basics as needed
• Rounding on INSERT
• RETURNING clause
• Single vs Multi-row inserts/updates
• Multi-table inserts
• Beware of issues with TRUNCATE
• MERGE statement
• All or nothing concept with data manipulation
• Overriding all or nothing concept with DML Error logging
• Workshop

Alternatives to Tables

• Brief review of tables and other Oracle objects as needed
• Temporary tables
• Purpose and Concepts
• Syntax
• Limitations
• Workshop
• Materialized Views
• Purpose and Concepts
• Synchronous vs Asynchronous refresh
• Query Rewrite
• Syntax
• Indexing Materialized Views
• Workshop
• External tables
• Purpose and Concepts
• Directory Objects
• Syntax
• Limitations
• Workshop

Model Queries

• Concepts
• Single dimension arrays
• Multi-dimension arrays
• Model Clause
• Partition By
• Dimension BY
• Measure
• Rules
• Workshop

Database Transactions

• Overview
• COMMIT and ROLLBACK
• SET TRANSACTION and SAVEPOINT
• Statement Level Read Consistency
• Transaction Level Read Consistency
• Oracle Isolation Levels
• Phantom and Non-repeatable Reads
• Read Committed
• Serializable
• READ ONLY
• IMPLICIT commits
• Workshop

Database Concurrency

• Oracle Locks
• Row Lock Architecture
• Basic Locking Rules
• DML Locks
• DDL Locks
• Locking Issues:
• Lost Update
• Blocking
• Deadlocks
• Workshop

Record Locking in APEX and Connection Pooled Environments

• The Issue
• Lost Update Problem
• Pessimistic Locking – does NOT work in this environment
• Optimistic Locking – DOES work in this environment
• Optimistic Locking techniques
• Check all appropriate data values
• Create an extra column
• Store hash of original column values
• Store a timestamp or a SEQUENCE
• Beware using ora_rowscn (does NOT work)
• Workshop

How to use Flashback Query

• Concepts
• Session level flashback
• Statement level flashback
• Limitations
• Advanced features
• Workshop
• SQL Macros

Concepts

• Types of SQL Macros
• Advantages
• The Context Switching problem
• The Context Switching solution