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. Get a head start learning the analytic functions with SkillBuilders’ Free Tutorial: How to Use Oracle Analytic Functions
Course Features
- Lectures 0
- Quizzes 0
- Duration 5 Half Days (~20 hours)
- Skill level All levels
- Language English
- Students 353
- Certificate Yes
- Assessments Yes
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
Login with Google