Close
Contact Us info@learnquest.com

??WelcomeName??
??WelcomeName??
« Important Announcement » Contact Us 877-206-0106 | USA Flag
Close
Close
Close
photo

Thank you for your interest in LearnQuest.

Your request is being processed and LearnQuest or a LearnQuest-Authorized Training Provider will be in touch with you shortly.

photo

Thank you for your interest in Private Training.

We look forward to helping you develop the perfect training solution to help you meet your company's goals.

For immediate assistance, speak with one of our representatives using the chat module below. Otherwise, LearnQuest or a LearnQuest-Authorized Training Provider will be in touch with you shortly.

Close
photo

Thank you for your interest in LearnQuest!

Now, you will be able to stay up-to-date on our latest course offerings, promotions, and training discounts. Watch your inbox for upcoming special offers.

title

Date: xxx

Location: xxx

Time: xxx

Price: xxx

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

Price
2,100 USD
3
DBOR-973
Classroom Training, Online Training
Oracle

AWS Training Pass

Take advantage of flexible training options with the AWS Training Pass and get Authorized AWS Training for a full year.

Learn More

Prices reflect a 22.5% discount for IBM employees (wherever applicable).
Prices reflect a 24% discount for Kyndryl employees (wherever applicable).
Prices reflect the Accenture employee discount.
Prices shown are the special AWS Partner Prices.
Prices reflect the Capgemini employee discount.
Prices reflect the UPS employee discount.
Prices reflect the ??democompanyname?? employee discount.
GSA Private/Onsite Price: ??gsa-private-price??
For GSA pricing, please go to GSA Advantage.
 

Class Schedule

Delivery Formats

Sort results

Filter Classes

Guaranteed to Run

Modality

Location

Language

Date

    Sorry, there are no public classes currently scheduled in your country.

    Please complete this form, and a Training Advisor will be in touch with you shortly to address your training needs.

View Global Schedule

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

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

Prerequisites


     

Topics

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
Top 20 Training Industry Company - IT Training

Need Help?

Call us at 877-206-0106 or e-mail us at info@learnquest.com

Personalized Solutions

Need a personalized solution for your Training? Contact us, and one of our training advisors will help you find the best solution.

Contact Us

Need Help?

Do you have a question about the courses, instruction, or materials covered? Do you need help finding which course is best for you? We are here to help!

Talk to us

LearnPass Year-End Offer

Get Up to 25% Additional Training Funds Before the Year Ends!

Act Now

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

??spvc-wbt-warning??

Exam Terms & Conditions

??exam-warning??
??group-training-form-area??
??how-can-we-help-you-area??
??personalized-form-area??
??request-quote-area??

Sorry, there are no classes that meet your criteria.

Please contact us to schedule a class.
Close

self-paced
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

Close
Nothing yet
here's the message from the cart

To view the cart, you can click "View Cart" on the right side of the heading on each page
Add to cart clicker.

Purchase Information

??elearning-coursenumber?? ??coursename??
View Cart

title

Date: xxx

Location: xxx

Time: xxx

Price: xxx

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.

If you would like to request a quote for 5 or more students, please contact CustomerService@learnquest.com to be assigned an account representative.

Need more Information?

Speak with our training specialists to continue your learning journey.

 

Delivery Formats

Close

By submitting this form, I agree to LearnQuest's Terms and Conditions

heres the new schedule
This website uses third-party profiling cookies to provide services in line with the preferences you reveal while browsing the Website. By continuing to browse this Website, you consent to the use of these cookies. If you wish to object such processing, please read the instructions described in our Privacy Policy.
Your use of this LearnQuest site affirms your consent to our use of session and persistent cookies to track how you use our website.