Advanced SQL Queries for Oracle Databases
Duration: 3 Days

Geoffrey Wiland

 4.7/5 (144)

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

  • Call 1-401-783-6172 for Group or Alumni Discounts.
    If you are a student of an accredited academic institution call us for a 25% discount.

    • Price Qty
    11/9 - 11/13show details + $2,700.00 (USD)  

    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
    Geoffrey Wiland
    https://skillbuilders.com/our_team/geoffrey-wiland/
     5/5

    I like the small class size and being able to ask questions as we go through topics.
    There are a lot of new topics I didn\'t know existed that I will be implementing into my job.


    Elise Dunn
    May 8, 2020
     4.8/5



    Matthew Urbanczyk
    May 8, 2020
     4.4/5
    Jennifer Bottom
    April 24, 2017
     4.8/5
    anil edupuganti
    April 24, 2017
     3.6/5
    Jonathan Dare
    April 24, 2017
     4.1/5
    Radhika Sangireddy
    April 24, 2017
     4.2/5
    mike gangi
    April 24, 2017
     5/5
    Mahmoud Mahran
    April 24, 2017
     4.5/5
    Naveen Gupta
    April 24, 2017
     5/5

    I learned some techniques that I can use immediately in my work.

    Some of the subject matter seemed like it was beginner material. Not needed in an advanced class.
    Aimee Schimmel
    April 17, 2017
     3.7/5
    Mike Rienti
    March 27, 2017
     3/5
    Faiyaz Sharezaman
    March 27, 2017
     3.7/5
    Joe Zullich
    March 27, 2017
     3/5
    Chris Sambuchi
    March 27, 2017
     4.4/5
    Eric Davilia
    March 27, 2017
     4.6/5
    Tamara Towles-Schwen
    March 27, 2017
     4.4/5
    Amanda Vanderwerf
    March 27, 2017
     5/5

    Course concepts were perfect for improving upon my current SQL skill set.

    Everything about the course was very good.
    Steve Thoel
    November 28, 2016
     4.7/5
    Rob Schindler
    November 28, 2016
     5/5

    Every topic explained in detail
    Muhammad Alvi
    November 28, 2016
     5/5
    Jeff Herman
    November 28, 2016
     4.9/5

    I will be able to use some new coding approaches.
    Eric Sims
    November 28, 2016
     4.5/5

    I was fortunate to be the only person in the class, so enjoyed the bonus of having one on one with Geoff.
    Pete LoGiudice
    June 6, 2016
     4.6/5

    The content and the quality of the instructor
    Vitor Oliveira
    April 4, 2016
     4.8/5

    It was an enjoyable learning experience

    The course emphasis was tailored somewhat to our own needs, so any improvement would have to come from us doing a better job of defining our needs. I have no improvement suggestions.
    Steven Denowski
    July 13, 2015
     5/5

    We appreciate Geoff's flexibility with the times
    Rachel Kimmitt
    July 13, 2015
     5/5

    I liked the relaxed atmosphere for learning and the abundance of valuable knowledge and reference material.
    Douglas Burkes
    July 13, 2015
     4.5/5
    Madhu Murthy Tumulati Ananda
    June 8, 2015
     4.5/5
    Siddhi Patel
    June 8, 2015
     5/5
    Satish Poreddy
    June 8, 2015
     4.9/5
    Constance Eberle
    June 8, 2015
     5/5

    Everything was explained very well and in a way you could understand

    The material was very good, some more advanced examples would be nice
    Jeffrey Miller
    June 8, 2015
     5/5

    [The] one-on-one was great - know that can't be a reality generally speaking because of cost.
    Nancy Crosby
    December 1, 2014
     5/5
    Steve Stowers
    October 20, 2014
     5/5

    [What did you particularly like about the course?] class size .. Geoff's presentation and depth of knowledge. enjoyed the interaction.

    [Improvements needed?] digital copy of the textbook
    John Rose
    April 28, 2014
     4.8/5

    [What did you particularly like about the course?] The areas which the students expressed interest in were all covered. The addition subjects covered were eye opening.
    Jim Arnold
    April 28, 2014
     5/5

    The instructor [Geoff Wiland] was interested in making sure we covered the topics most relevant to us.

    Maybe making it a little more interactive somehow.
    Josh Wester
    August 19, 2013
     4.1/5
    Jahnavi Vemuri
    August 19, 2013
     5/5

    Workshops reinforcing the lectures.
    Sandra Whiting
    March 11, 2013
     5/5

    [particularly liked] Using Perl scripts in a text field with the regexp_like() and regexp_replace)) functions.

    Slow down the course material a bit.
    Dan Pigg
    March 11, 2013
     5/5

    Enthusiasm of Instructor [Geoff Wiland] and vast topics covered.
    Sanjay Varma
    December 10, 2012
     5/5

    1/2 day sessions helped allow time for working independently to reinforce training and gave time to absorb subject without being overwhelmed

    hard copy was appreciated for note taking and future reference
    Jeff Hunt
    December 10, 2012
     4.5/5

    a few more bathroom breaks

    no. worked well.
    jeff eske
    December 10, 2012
     4/5

    the teacher [Geoff Wiland] was good.
    navin ikramullah
    December 10, 2012
     5/5

    This was my first online course and I found it to be very good. The content and delivery were exactly what I wanted.

    I thought everything was ok
    Rosalie McInnis
    December 10, 2012
     5/5

    The discussions about analytic functions
    ML objects and materialized views and analyticx

    The course is good the way it is.

    I think it was well done and does not need any changes
    Mark Gustafson
    June 27, 2012
     4.8/5

    Covered a lot of material
    Great Manual
    Lori Szilaski
    April 25, 2012
     4.7/5

    Refreshing
    Matilda Fabunmi
    April 25, 2012
     4.7/5

    Instructor was knowledgeable.

    [Online] Delivery was fine.
    Greg Williams
    April 25, 2012
     4.7/5
    Alondo McClees
    March 5, 2012
     5/5

    [Online course means] No travel!!
    Kim Monke
    March 5, 2012
     5/5

    Class size was small, so time for all our questions to be answered. Good to take class with others, as can learn from other people's questions.

    [Online class] It was great - good to have phone-in and web connections in case of computer problems.
    Kimberly McGregor
    January 18, 2012
     5/5

    The hands on exercises helped, and also the instructor going over them helped to make things clear

    I really don't have any suggestions for improving the class - think things were good the way they were

    Can't think of any improvements [for this class], I was very satisfied with the format
    Anthony Pompeii
    January 18, 2012
     4.8/5

    The instructer was very attentive to our questions and used typical examples. I definitely walked away with helpful information that I can currently work with.

    I have no suggestions for improvement as I was extremely pleased with the content and the presentation of the course.
    Joanne Barley
    November 16, 2011
     4.5/5

    Easy to participate.
    Timothy Moriarty
    November 16, 2011
     5/5

    That we had to to do the [hands-on] labs was what I particularly liked
    Lisa Boike
    May 11, 2011
     3.7/5

    I particularly liked the analytical functions and regular expression chapters
    Norma McSpadden
    May 11, 2011
     5/5

    I really liked that is was very open. Like you were sitting in a classroom.
    Shannon Mansfield
    May 11, 2011
     5/5

    Teacher explains in a very clear manner. Very enthusiastic.

    [Improvements needed?] No, I thought it was excellent.
    Kathleen Conklin
    May 11, 2011
     4.9/5

    I liked using GoToMeeting. it was easy to ask questions and get responses. The instructor knew the subject and was easy to understand.
    Linda Johnson
    May 11, 2011
     4.7/5
    steven chan
    March 14, 2011
     5/5

    It is clear and thorough. Instructor with excellent knowledge and presentation skills.
    Aurora Pecoraro
    March 14, 2011
     4.2/5

    instructor's experience & training content
    Srini Krishnan
    March 14, 2011
     4.5/5
    Craig Challborn
    March 14, 2011
     5/5
    David Galbreath
    March 14, 2011
     5/5
    Nancy Fleischauer
    March 14, 2011
     5/5

    knowledgable instructor!
    Sally Lafferty
    March 14, 2011
     5/5
    irina white
    March 14, 2011
     5/5
    Peter Leung
    March 14, 2011
     4.9/5
    Mohammad Zaheer
    March 14, 2011
     5/5

    [particularly like] Practices, workshop section
    Jonathan Dy
    March 14, 2011
     5/5

    [I particularly liked] new ansi join features and regular expressions
    Steve Bakaley
    March 14, 2011
     4.9/5

    I liked the coverage of SQL model query
    analytics
    bulk collect
    Henry Chen
    March 14, 2011
     5/5
    Suresh Tunuguntla
    March 14, 2011
     3.7/5

    I liked the rapid overview of oracle features

    Remove the labs and instead have group discussion regarding ramifications and uses for the oracle features. I would also bring in more information regarding performance issues (both positive and negative) of the features.
    rob macfarlane
    March 14, 2011
     4.8/5

    Add syntax of applicacable SQL in book. This would help in doing exercises.
    Steve Kessler
    March 14, 2011
     4.8/5

    The instructor was knowledgeable and presented material I can use
    Daniel Abell
    March 14, 2011
     5/5

    [particularly like] new technics
    Anna Losev
    March 14, 2011
     5/5

    [I particularly liked] New Techniques of writing SQLs.
    Abu Sufian Shaikh Mohammed
    March 14, 2011
     5/5
    Sandeep Sharma
    March 14, 2011
     3.7/5

    Compact materials, workshops
    Michael Kremer
    March 14, 2011
     5/5

    Instructor's knowledge and workshops
    Janet Leong
    March 14, 2011
     5/5

    challenging labs that moved quickly, and didn't wait till everyone finished every problem.
    Jeremy Pease
    March 14, 2011
     5/5
    Rajan Sadagopan
    March 14, 2011
     4.8/5

    Geoff is an excellent instructor and also really knows the material. These on-line lectures were as effective as other "in the room" instruction I have had.

    1) Suggest that users print or copy the exercises as a separate document so they can more easily use the course book as a reference during the exercises. Flipping back and forth caused me to overlook some important information in the exercises while doing

    The external table and XML exercises require file access to a disk space available to the Oracle server. This had not been set up. It was a first time oversite on using a cloud server for Oracle. the oracle server on the cloud itself worked fine. The Go T
    Chuck Rinehart
    December 7, 2010
     5/5

    I actually really enjoyed the online course. With courses involving code, a lot of times it's hard to see the code or the presenter is forced to divide up simple statements on multiple slides. With the online presentation, you could clearly see all
    Brian Kempf
    December 7, 2010
     5/5

    I enjoyed the fact that the course kept a steady pace and continued to build on the material previously presented.
    Natalie Cox
    November 15, 2010
     5/5
    Marc Venverloh
    November 15, 2010
     5/5
    Jay Reed
    November 15, 2010
     4.8/5

    Concise and clear
    Ruchita Sinha
    November 15, 2010
     4.7/5

    Geoff is a good instructor and explained concepts clearly. He was able to tailor instruction to our needs.
    FredErika Blatz
    November 15, 2010
     5/5
    Tony Lim
    November 15, 2010
     5/5
    Nataraj Vulchi
    November 15, 2010
     5/5

    Course was defined to meet our needs
    Lyudmila Abalyan
    November 15, 2010
     5/5

    Geoff is a great instructor and made the material interesting and fun.
    T Talda
    November 15, 2010
     4.7/5

    [particularly like] useful and applicable to current job responsibilities
    Veronica Valdes
    November 15, 2010
     4.6/5

    provided clear examples of concepts
    Nancy Sheppard
    November 15, 2010
     5/5

    Workshops to apply what was learned.
    Nathan Green
    October 25, 2010
     3.1/5
    Erin Schillie
    October 25, 2010
     3.7/5

    The labs helped make the concepts stick of the few that I did not know about.
    Nicole Albee
    October 25, 2010
     3.9/5
    Andrea Elberg
    October 25, 2010
     4.8/5

    I like how organized the information was and the delivery by the instructor.
    Emily Adams
    October 25, 2010
     5/5
    Jason Wang
    October 25, 2010
     4.8/5

    [particularly liked] Exercises and workshops. Printed textbook.
    Tomoko Lee
    October 25, 2010
     4.4/5
    Danielle Walters
    October 25, 2010
     5/5

    [particularly like] The workshops that help get a handle on what we learned.
    Michael Boysen
    October 25, 2010
     3.9/5
    Jeff Daood
    October 25, 2010
     4.5/5
    Max Harnphanich
    October 25, 2010
     4.5/5
    Sam Xiao
    October 25, 2010
     5/5
    Maxine Li
    October 25, 2010
     4.9/5

    I liked the workshops after each chapter to reinforce what was discussed.
    Andrea Spiess
    October 25, 2010
     4.4/5

    [particularly like] the workshops
    Lisa Ratz
    October 25, 2010
     4/5

    [particularly liked] The material
    Jason McClellen
    October 25, 2010
     4.7/5

    [I liked] Breath of subject covered.
    Vinay Volety
    October 25, 2010
     5/5
    Lois Lovejoy
    October 25, 2010
     4.5/5
    John Petry
    October 25, 2010
     4.9/5

    I liked that the instructor added a section to the class that was not explicitly mentioned in the course material on regular expressions.

    Aside from adding regular expressions as a permanent addition to the class, I can't think of any improvement.

    The GoToMeeting software worked very well for the information presented. There was virtually no lag and the instructor's voice was clear.
    Marshall Manning
    July 26, 2010
     5/5

    I like most of the subject that cover in the course.
    Wing Hsieh
    July 26, 2010
     5/5

    Geoffrey Wiland was very enthusiastic, patient, knowledgeable and was always willing to answer questions and expand on the subject matter. Small class size was also benefit to positive interactions.
    Jaron Shook
    July 26, 2010
     5/5

    course needs to be little longer for more detailed analysis.
    Stacey Tucker
    September 29, 2009
     5/5

    I can't think of anything [to improve]. The course was a good length, the book is helpful.

    I really liked the instructor. Geoff is a terrific teacher and speaker. It is clear that he knows and is comfortable with the course material. I also liked that Geoff could jump onto my computer and help me out.
    Nancy Roberson
    September 29, 2009
     4.9/5
    seema lukose
    August 31, 2009
     4.8/5

    The level of this training is just right for me. It improves my ability to do my job.
    Henry Jao
    August 31, 2009
     5/5

    Possibly expand the offering to an Intermediate class as well.

    Knowledgable & helpful instructor.
    Michael Postorino
    August 4, 2009
     4.9/5

    It provided the right amount of clarification of the topics. Good examples.
    Charlotte Jansson
    August 4, 2009
     5/5
    Marsha Toner
    May 20, 2009
     4.8/5
    Michelle Molitor
    May 20, 2009
     5/5
    Glenn Anderson
    May 20, 2009
     4.9/5

    I liked the faster pace of a 2 day class versus a 3 day class. Made it easier to stay focused. I also like the current examples given in addition to the book. I am looking forward to the external table and using that in future projects.
    Vicki Phillips
    May 20, 2009
     4.9/5
    Ronald Mason
    May 20, 2009
     5/5

    I learned new sql techniques which could be useful.
    Clarence Searcy
    May 20, 2009
     5/5

    I learned the differences between a DML and DDL and the way they implicitly or explicitly commit. The Analytic functions are a great thing to know about! Geoff is a really great teacher. He really knows his stuff. I really enjoyed having him back.
    Michelle Drake
    May 20, 2009
     4.5/5

    Geoff is very knowledgeable and a good instructor
    frank kingery
    May 20, 2009
     4/5

    [I particularly liked the] Instructor
    connie manes
    May 20, 2009
     5/5

    great presenter...knew what he was talking about and willing to deviate from schedule to discuss issues.
    Rhonda Nichols
    May 20, 2009
     4.9/5
    Phil Zimmer
    May 18, 2009
     4.2/5

    Seemed primarily oriented towards report writing rather than overall data manipulation and analysis. That might just be a product of the subject matter itself.

    Good exposure to advanced concepts without getting bogged down in minutia.
    Matthew Burack
    May 18, 2009
     5/5

    My SQL skills were very limited before taking this class. After going through the class & workshops, I understand so much more.
    Judy Young
    May 18, 2009
     5/5
    Keith Wortham
    May 18, 2009
     5/5

    It was timely and practical. The exercises did not build upon a prior chapter so you can always review it later and not hold the class back if you get stuck with a given task.
    Michael Hubbartt
    May 18, 2009
     4.7/5
    Karen Hammer
    May 18, 2009
     4.7/5

    would have preferred not to have received a big paper course book. electronic documents are easier to use and more environmentally friendly.

    some of the sql examples could be improved, ie. to follow the instructor's suggested best practices of alwa

    the breadth of topics was good, covering many interesting uses of sql that will be easy for me to integrate in to my daily development tasks.
    Matthew Feldt
    May 4, 2009
     5/5
    Sean Yang
    February 9, 2009
     5/5

    I liked the fact that you spread the course out over 4 afternoons. I was impressed at how easy it was to configure my system for the database and the online meeting.
    Cathy Farrell
    February 9, 2009

    You May Like

    Copyright © Skillbuilders.com

    Free Online Registration Required

    The tutorial session you want to view requires your registering with us.

    It’s fast and easy, and totally FREE.

    And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

     

    ×