Advanced SQL Queries for Oracle Databases
Duration: 3 Days
Learn the advanced SQL skills necessary to design and code complex queries against 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. Emphasis is placed on best practices using ANSI standard SQL as well as Oracle SQL enhancements.
Students use common query tools such as SQL Developer, TOAD, SQL*Plus, etc. to do course workshops to reinforce all concepts. Hands-on workshops constitute approximately 50% of the class. We will provide you with a dedicated Oracle database on our Amazon EC2 cloud servers for your labs.
This class is given as either public or private classes. For private classes the course can be customized to meet your specific training requirements.
This course includes instruction on the Oracle Analytic Functions.
Free online training videos; free registration required:
Learn to Use Oracle Analytic Functions.
Brief Review as Needed
- Relational Database Concepts
- Basic SELECT command and Oracle datatypes
- Virtual columns
- Invisible columns
- Identity columns
- Extended string datatypes
- Datetime datatypes
- Workshop
- 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
- Using CONNECT BY for Hierarchical queries
- CONNECT BY syntax
- LEVEL
- Problem with WHERE clause
- SIBLING and LEVEL sorts
- Hierarchy path
- CONNECT BY joins
- Workshop
- Single Row Functions
- Using Conversion Functions and Conditional Expressions
- 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
- 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
- Brief review of group function basics as needed
- LISTAGG
- FIRST and LAST
- ROLLUP and Partial ROLLUP
- CUBE
- GROUPING function
- GROUPING SETS
- Workshop
- Purpose
- Syntax
- PARTITION BY clause
- ORDER BY clause
- Windowing clause
- Row vs Range windows
- Ranking functions
- Top-N queries
- LEAD and LAG
- LISTAGG function
- More advanced analytic functions
- Workshop
- 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
- 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
- Brief review of set operator basics
- UNION vs UNION ALL vs INTERSECT vs MINUS
- Key concepts
- Precedence rules
- Workshop
- 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
- 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
- Concepts
- Single dimension arrays
- Multi-dimension arrays
- Model Clause
- Partition By
- Dimension BY
- Measure
- Rules
- Workshop
- 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
- Oracle Locks
- Row Lock Architecture
- Basic Locking Rules
- DML Locks
- DDL Locks
- Locking Issues:
- Lost Update
- Blocking
- Deadlocks
- Workshop
- 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
- Concepts
- Session level flashback
- Statement level flashback
- Limitations
- Advanced features
- Workshop
- Purpose and Concepts
- Unstructured vs Structured storage
- XMLTYPE datatype
- Namespaces
- Loading XML data
- Schema-typed data
- Inserting schema-typed data
- Workshop
