Oracle’s SQL Expert exam slated for facelift Sept. 15

Posted on
Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone

Of the more than 200 certifications offered by Oracle, their SQL Expert credential ranks in the Top 10 most popular according to a blog post by Oracle University. This is no surprise to me — knowledge of SQL is an extremely valuable skill and the SQL Expert designation is useful to both DBAs and developers. That said, as I noted in an earlier CertMag article, The Case for an Updated Oracle SQL Expert Exam, this test was developed when 10g was Oracle’s flagship database. There have been a number of changes in subsequent releases and 1Z0-047 is starting to show its age. In the article, I suggested that the subjects covered were too close to the SQL Fundamentals exam and that there were a number of advanced SQL capabilities which had existed in 10g that the test ignored. With several new SQL capabilities introduced with the release of Oracle 12c, I suggested they use the opportunity to perform a complete makeover of the exam.

Are you ready for the new SQL Expert exam from Oracle?Apparently, someone in test development at Oracle University reads CertMag. Oracle has released an updated topic list that incorporates the vast majority of advanced subjects from my article. Some of the new topics, such as the analytical functions, were pulled almost word-for-word from the article. The new exam will now include the following topics noted in the article:

— Using the PIVOT and UNPIVOT clause
— Using the SQL row limiting clause
— Analytical functions including PERCENTILE_CONT, STDDEV, LAG, LEAD
— Using pattern matching to recognize patterns across multiple rows in a table
— Using the cross_outer_apply_clause
— Create a lateral inline view in a query

The exam developers have also added several topics beyond those that I suggested. The subjects covered by these are all excellent additions to the exam. Looking at them with the benefit of 20-20 hindsight, I realize that my Case article was written from a very DML-centric viewpoint. The SQL Expert exam covers DDL commands as well as DML, and several new Data Definition Language capabilities have been added to the Oracle database in 11g and 12c. The topics related to DDL enhancements that will be tested on the updated exam include:

— Using 12c enhancements to the DEFAULT clause, invisible columns, virtual columns and identity columns
— Recursively truncate child tables
— Using the new index capabilities, such as invisible indexes and multiple indexes on the same columns.
— Using explicit default values in INSERT and UPDATE statements

I should note that the exam developers did not introduce some of the most disruptive changes that I offered in the Case article. For example, I had suggested the possibility of consolidating the 38 topics pulled straight from the ‘SQL Fundamentals’ exam into a single section of eight topics. Making a change that radical to the topic list would have completely changed the landscape of the SQL Expert exam. Instead of putting out an updated version of 1Z0-047, Oracle University would have had to retire the existing test and release a completely new exam. I can understand why the developers chose to make a more tightly focused upgrade.

Even though Oracle University did not consolidate the SQL Fundamentals topics, they did remove two legacy topics from the exam. One of them was removed because it was a duplicate. In one section of the exam there was a “Create and Maintain Indexes” topic, and a later section had a “Create Indexes” topic. The rationale behind the duplication was unclear and I joked about OU having a fixation on indexes in my study guide for the exam. The other removed item was “Define subqueries.” The test contains 10 separate topics on various aspects of subqueries. Allocating an 11th simply to define what they are seems to be overkill. Any information or questions about the definition of subqueries can be rolled into one of the remaining areas.

Feedback that I received from my original article suggests that the changes will not be universally welcomed. In forums that I frequent, several people posted comments to the effect that the current SQL Expert exam is already too difficult and that adding more topics would simply make that worse. It is very difficult for me to empathize with this thinking. The whole point of taking IT certification exams is to demonstrate that you are knowledgeable in a particular subject area. If the SQL Expert exam is missing a number of key skills that an SQL guru should have, then the value of earning the certification is significantly diminished. If you are going to put time, energy and money into becoming certified, it is in your best interest that the subject matter that is tested be comprehensive.

I heartily approve of this action by Oracle University. Obviously I felt that an update was necessary or I would not have written the earlier article. However, OU could have updated the exam and done a bad job of it. The updated topics list demonstrates that this did not happen. The test developers added key knowledge areas that I would have argued were the most important (including several that I missed), and removed some that I consider to be “noise.” The result is an exam that will ensure that certification candidates are knowledgeable about more of the advanced capabilities of Oracle SQL than was required for the original exam.

Love them or hate them, the changes will go into effect on Monday, Sept. 15. Anyone taking the 1Z0-047 exam before that date will have the legacy topics. Oracle University will not be performing a new beta to test the changes — they will slide directly into the production exam. Anyone who is currently preparing for this exam needs to be aware that they need to either take it before the Sept. 15, or locate study materials for the new topics. I expect that there are going to be a number of people who are caught out by the update. If you are currently preparing for the exam, be sure to check out the topic list on the Oracle Education site.

Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
Matthew Morris


Matthew Morris is an experienced DBA and developer. He holds Oracle DBA Certifications for every Oracle release from 7 through 12c; Expert certifications for SQL, SQL Tuning, and Application Express; and is an Oracle PL/SQL Developer Certified Professional. He is the author of more than 20 study guides for Oracle certification exams, as well as a suite of Oracle practice tests that are available at

Posted in News|


One thought on “Oracle’s SQL Expert exam slated for facelift Sept. 15”

  1. Pingback: Oracle's new recertification policy: A calamity of Biblical proportions? - CertMag

Comments are closed.