Advanced SQL Queries for Oracle Databases

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

Database Flashback

• 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