title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.
Advanced SQL Queries for Oracle Databases
Course Description
Overview
Learn the advanced SQL skills necessary to design and code complex queries against Oracle databases.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 students with a dedicated Oracle database on our Amazon EC2 cloud servers for their labs.
This course includes instruction on the Oracle Analytic Functions.
Objectives
Audience
Topics
- 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
- MATCH_RECOGNIZE 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
- SQL Macros
- Types of SQL Macros
- Advantages
- The Context Switching problem
- The Context Switching solution
Related Courses
-
Introduction to Oracle Database and SQL
DBOR-970- Duration: 3
- Delivery Format: Classroom Training, Online Training
- Price: 2,100.00 USD
-
An Introduction to JSON in Oracle Databases
DBOR-972- Duration: 2
- Delivery Format: Classroom Training, Online Training
- Price: 1,400.00 USD
Self-Paced Training Info
Learn at your own pace with anytime, anywhere training
- Same in-demand topics as instructor-led public and private classes.
- Standalone learning or supplemental reinforcement.
- e-Learning content varies by course and technology.
- View the Self-Paced version of this outline and what is included in the SPVC course.
- Learn more about e-Learning
Course Added To Shopping Cart
bla
bla
bla
bla
bla
bla
Self-Paced Training Terms & Conditions
Exam Terms & Conditions
Sorry, there are no classes that meet your criteria.
Please contact us to schedule a class.
STOP! Before You Leave
Save 0% on this course!
Take advantage of our online-only offer & save 0% on any course !
Promo Code skip0 will be applied to your registration
Purchase Information
title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.