The Case for an Updated Oracle SQL Expert Exam

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

Based on the number of questions I see about this exam on forums and other venues, 1Z0-047: Oracle Database SQL Expert is one of the most popular of Oracle’s Expert series of certification exams. Being highly skilled with SQL is a definite plus in today’s job market and learning the skills covered by this exam is certainly one way to improve your knowledge of the SQL language and simultaneously let employers know that you have. That aside, in my opinion this exam has some shortcomings that Oracle University could address by putting out a new release as part of their 12c offerings.

The SQL Expert exam currently has 76 topics, 38 of which are pulled directly from 1Z0-051: Oracle Database 11g: SQL Fundamentals I. Having this many common topics has always confused test candidates, making them think that the two exams are largely interchangeable. This is unfortunate, because the SQL Expert exam is much more difficult and should not be attempted by candidates new to Oracle SQL. On the exam, the majority of the exam questions revolve around the 38 topics that are specific to 1Z0-047.

One of the issues I consider to be a weakness of the exam is with the questions that are on the fundamental SQL topics. Generally these questions are not “hard” so much as they are “tricky.” SQL is by design a very straightforward language. When a SQL statement is well-written, the intent is normally very clear. Since the exam is intended for “experts,” the exam designers deliberately made many of the statements unclear. I will grant that being able to read poorly-written SQL is a valuable skill for expert developers. The inclusion of these questions, however, really made it feel to me like the exam creators were stretching to inflate the difficulty of the exam.

The primary value I gain from Oracle certifications is in what I learn while preparing for them. What would have made 1Z0-047 more valuable to me is if it contained a larger quantity of topics on advanced SQL. Adding additional advanced topics would also mean that the number of artificially obscured questions on the exam about basic SQL skills would be reduced. The fundamentals on this new exam could be reduced to eight topics under a single section:

SQL Fundamentals

        Retrieve, sort, and filter data using SELECT statements
        Use single-row functions to customize query output
        Aggregate and filter data using Group Functions
        Retrieve data from multiple tables using JOIN operations
        Use single and multiple-row subqueries
        Combine data with SET operators and sort the output
        Manipulate data with DML
        Use DDL Statements to Create and Manage Tables


The 1Z0-047 exam was created while Oracle 10G was current. Several enhancements have been added to Oracle SQL in releases 11g and 12c. In addition, several advanced SQL topics that existed in 10g were left out. Advanced SQL topics that existed in 10g and could have been part of the exam include:

Analytic functions — Analytic functions compute an aggregate value based on a group of rows. They have similarities to aggregate functions but can return multiple rows for each group. RANK, PERCENT_RANK, PERCENTILE_CONT, STDDEV, LAG, LEAD and others are all very useful analytic functions that are not well understood by many SQL developers.

XML Functions — XML functions operate on or return XML documents or fragments. XML is becoming increasingly widespread. Promoting a better understanding how to make use of the XML functions built into Oracle SQL would be a worthwhile addition to the exam.

Nested Tables — The ability to create and query nested tables is another powerful aspect of Oracle that is not well understood by many SQL developers.

A few SQL enhancements were added in 11g and even more in 12c. These would not only make an excellent addition to the exam but provide a reason for creating a new release of the exam at this time. Some of the new features that would fit well into an updated SQL Expert exam include:

MATCH_RECOGNIZE — This new clause enables native SQL queries to match specified patterns in sequences of rows. Previously pattern matching of this type required the use of PL/SQL. The syntax incorporates regular expressions and full conditional logic to allow for very precise and flexible pattern definition.

PL/SQL in WITH clause — It is now possible to define PL/SQL functions in the SQL WITH clause. Once defined in the WITH clause, the block can be used as an ordinary function in the statement and provides better performance when compared to schema-level functions.

Query Row Limits — Oracle SQL now supports query row limits and row offsets with the FETCH FIRST and OFFSET clauses. The new clauses limit the number of rows returned and specify a starting row for the return set respectively. Both of these capabilities have been desired by SQL developers for years.

APPLY — The APPLY SQL syntax provides the capability for a table-valued function to be invoked for each row returned by a query’s outer table expression.

LATERAL — The LATERAL keyword is part of the ANSI standard and is an extension of the inline view syntax that provides left-correlation scoping within the inline view.

PIVOT and UNPIVOT — The PIVOT and UNPIVOT syntax was added to the SELECT capabilities in 11g. The PIVOT syntax provides a method for rotating rows into columns. The UNPIVOT operation performs the reverse operation, allowing columns to be rotated into rows.

Regular Expression Enhancements — The REGEXP_INSTR and REGEXP_SUBSTR functions now have an optional subexpr parameter. This parameter allows a particular substring of the regular expression being evaluated to be targeted. In addition, the REGEXP_COUNT function was added in 11g. This expression counts the number of occurrences of a specified regular expression pattern in a source string.

The 1Z0-061: Oracle Database 12c: SQL Fundamentals exam removed five topics that were common between the SQL Expert exam and the 11G SQL Fundamentals exam (1Z0-051). This change widened the separation between the two exams slightly. If Oracle University creates an updated SQL Expert exam with some of the advanced topics above, the end product would be vastly different from 1Z0-061. I believe that the result would be an exam that would provide a considerably greater return for Oracle professionals.

Like what you see? Share it.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 Certification|