Wednesday, September 3, 2014

1Z0-047 topics being updated on 15-SEP-14. Don't get caught unawares.

A little less than two weeks from the date I'm writing this post, the topics list for the SQL Expert exam, 1Z0-047, will include a number of new subject areas. Simultaneously, two of the legacy topics will be removed, but their impact to test-takers is minimal. I wrote an article discussing the changes in more detail that was just published at CertMag here.

If you have been preparing for this exam and have purchased any existing study materials such as the Oracle Press OCA Oracle Database SQL Certified Expert Exam Guide by Steve O'Hearn or my Study Guide for 1Z0-047: Oracle Database SQL Expert, they will not cover these topics. I am working on a major update to my guide at this time that should be released in a few weeks. I do not have any information about when (or if) Steve's Oracle Press book will be updated to include the new material.  The full list of topics that are being added is:


  • Using pattern matching to recognize patterns across multiple rows in a table
  • Using the PIVOT and UNPIVOT clause
  • Using the SQL row limiting clause
  • Analytical functions including PERCENTILE_CONT, STDDEV, LAG, LEAD
  • Using 12c enhancements to the DEFAULT clause, invisible columns, virtual columns and identity columns
  • Using explicit default values in INSERT and UPDATE statements
  • Using the new index capabilities, such as invisible indexes and multiple indexes on the same columns.
  • Recursively truncate child tables
  • Create a lateral inline view in a query
  • Using the cross_outer_apply_clause


I have updated the 1Z0-047 Exam Detail page on my website to include links to articles for all of the new topics. As so often happens, many of the new links are from +Tim Hall and point to articles on his enormously informative Oracle-Base website. If you are in the final stages of preparing for the SQL Expert exam, you can use these articles along with the Oracle documentation to learn about the additions.