The Complete Guide to the Oracle Database SQL Expert CertificationThis article explains all you need to know about the Oracle Database SQL Expert certification, which is also referred to as 1Z0-147.

What Is The Oracle Database SQL Expert Certification?

This certification is a popular SQL certification offered by Oracle and widely recognised by the IT industry. Learning SQL and being highly skilled with it is a great advantage for a software developer, as it adds another valuable skill to the list of things that you’re capable of doing. Knowledge of SQL allows you to run queries and understand the queries that others have written.

The Oracle Database SQL Expert certification is an advanced level certification, as the name suggests. However, there isn’t any prerequisites for this one. There is no “Oracle Database SQL Beginner” or an Advanced level that you need to do first before this one.

 

What Other Names Is This Certification Known As?

Certifications can often have more than one name, often abbreviated versions or just referred to by the exam number. This certification can also be known as:

  • Oracle Database SQL Certified Expert
  • Oracle SQL Expert
  • Oracle Certified Expert
  • OCE
  • 1Z0-047 (the exam code)

 

What Exams and Exam Codes are Used For This Certification?

This certification only features one exam, and the exam code for this is 1Z0-047: Oracle Database SQL Expert. Note that this exam code starts with the number one, and it has zeroes, not the letter O (one Z zero, zero four seven).

The exam goes for 120 minutes (2 hours). It contains 75 multiple-choice questions, and the pass mark is 66%. This means you need to get 50 questions or more correct (66% of 75 is 49.5, rounded up to 50).

Exam Code 1Z0-047
Exam Time 120 minutes (2 hours)
Questions 75
Question Format Multiple choice
Pass Mark 66% (50/75)
Prerequisites None
Cost $245 USD

How Much Does The Oracle Database SQL Expert Certification Cost?

At the time of writing, this certification can be attempted for $245 USD. Many certifications require you to pay for each exam, but as there is only one exam for this, you only need to pay once.

 

What Topics Are Included In This Certification?

The topics for the 1Z0-047 exam are shown below.

Retrieving Data Using the SQL SELECT Statement

  • List the capabilities of SQL SELECT statements
  • Execute a basic SELECT statement
  • Describe how schema objects work

Restricting and Sorting Data

  • Limit the rows that are retrieved by a query
  • Sort the rows that are retrieved by a query
  • Use the SQL row limiting clause*
  • Create queries using the PIVOT and UNPIVOT clause*
  • Use pattern matching to recognize patterns across multiple rows in a table*

Using Single-Row Functions to Customize Output

  • Describe various types of functions that are available in SQL
  • Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements*
  • Describe the use of conversion functions

Reporting Aggregated Data Using the Group Functions

  • Identify the available group functions
  • Describe the use of group functions
  • Group data by using the GROUP BY clause
  • Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

  • Write SELECT statements to access data from more than one table using equijoins and nonequijoins
  • Join a table to itself by using a self-join
  • View data that generally does not meet a join condition by using outer joins
  • Generate a Cartesian product of all rows from two or more tables
  • Use the cross_outer_apply_clause*

Using Subqueries to Solve Queries

  • Define subqueries*
  • Describe the types of problems that subqueries can solve
  • Use subqueries*
  • List the types of subqueries
  • Write single-row and multiple-row subqueries
  • Create a lateral inline view in a query*

Using the Set Operators

  • Describe set operators
  • Use a set operator to combine multiple queries into a single query
  • Control the order of rows returned

Manipulating Data

  • Describe each data manipulation language (DML) statement
  • Insert rows into a table
  • Update rows in a table
  • Delete rows from a table
  • Control transactions

Using DDL Statements to Create and Manage Tables

  • Categorize the main database objects
  • Review the table structure
  • List the data types that are available for columns
  • Create a simple table
  • Explain how constraints are created at the time of table creation
  • Truncate tables, and recursively truncate child tables*
  • Use 12c enhancements to the DEFAULT clause, invisible columns, virtual columns and identity columns in table creation/alteration*

Creating Other Schema Objects

  • Create simple and complex views with visible/invisible columns*
  • Retrieve data from views
  • Create, maintain, and use sequences
  • Create and maintain indexes
  • Create private and public synonyms

Managing Objects with Data Dictionary Views

  • Use the data dictionary views to research data on your objects
  • Query various data dictionary views

Controlling User Access

  • Differentiate system privileges from object privileges
  • Grant privileges on tables and on a user*
  • View privileges in the data dictionary
  • Grant roles
  • Distinguish between privileges and roles

Managing Schema Objects

  • Add constraints
  • Create and maintain indexes including invisible indexes and multiple indexes on the same columns*
  • Create indexes using the CREATE TABLE statement
  • Creating function-based indexes
  • Drop columns and set column UNUSED
  • Perform FLASHBACK operations
  • Create and use external tables

Manipulating Large Data Sets

  • Manipulate data using subqueries
  • Describe the features of multitable INSERTs
  • Use the following types of multitable INSERTs (Unconditional, Conditional and Pivot)
  • Merge rows in a table
  • Track the changes to data over a period of time
  • Use explicit default values in INSERT and UPDATE statements*

Generating Reports by Grouping Related Data

  • Use the ROLLUP operation to produce subtotal values
  • Use the CUBE operation to produce crosstabulation values
  • Use the GROUPING function to identify the row values created by ROLLUP or CUBE
  • Use GROUPING SETS to produce a single result set

Managing Data in Different Time Zones

  • Use Various datetime functions
  • TZ_OFFSET
  • FROM_TZ
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_YMINTERVAL
  • TO_DSINTERVAL
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • LOCALTIMESTAMP
  • DBTIMEZONE
  • SESSIONTIMEZONE
  • EXTRACT

Retrieving Data Using Subqueries

  • Write a multiple-column subquery
  • Use scalar subqueries in SQL
  • Solve problems with correlated subqueries
  • Update and delete rows using correlated subqueries
  • Use the EXISTS and NOT EXISTS operators
  • Use the WITH clause

Hierarchical Retrieval

  • Interpret the concept of a hierarchical query
  • Create a tree-structured report
  • Format hierarchical data
  • Exclude branches from the tree structure

Regular Expression Support

  • Use Meta Characters
  • Regular Expression Functions
  • Use Replacing Patterns
  • Use Regular Expressions and Check Constraints

 

These topics were refreshed on 15 Sep 2014, which meant that some topics were removed, added, or updated on this date. The items marked as * above were added or updated on this date, and the ones with a strikethrough were removed on this date.

 

What Are The Prerequisites For The Oracle Database SQL Expert Certification?

There are no prerequisites for this certification or the 1Z0-047 exam. It is recommended that you have some experience with SQL, though, as this is not an entry-level certification.

 

What Is This Exam’s Retake Policy? What If I Fail The 1Z0-047 Exam?

Oracle has stated that if you fail this exam, you need to wait 14 days before retaking this exam. You can’t retake this exam if you have passed it.

 

How Long Is The Certification Valid For? When Does This Certification Expire?

Oracle certifications don’t have an expiration date, unlike other certifications. You are certified for life, unless Oracle changes its policy at some point in the future.

 

What Job Roles Is This Certification Targeted To?

This certification is ideal for the following roles in the IT industry:

 

What Level of Experience Is The Oracle Database SQL Expert Certification Aimed At?

I believe this exam is aimed at those with an advanced level of Oracle knowledge. You need to know more than just the basics of writing an SQL query to be able to pass this exam. It can be quite hard if you haven’t had this experience.

 

Where Can I Take The 1Z0-047 Exam?

You can take this exam at any PearsonVUE centre. You can find your nearest one on the PearsonVUE website here.

 

How Popular Is The  Oracle Database SQL Expert Certification?

At the time of writing, I could not find any actual data on how many people have this certification, although Oracle states there are over 1.5 million certified Oracle professionals (which is for all certifications).

However, reports are that it is a popular certification among SQL practitioners who work on Oracle. It’s also the first certification that I earned, as my employer noticed that this certification was useful and assisted us in gaining it.

 

Where Has This Certification Appeared In Top Lists?

This certification has appeared in several lists of certifications:

 

What Does The Community Say About This Certification?

Many websites, blogs, and forums mention this exam, and the feedback on it is:

  • It can be quite a hard and challenging exam. Knowing your topics is important
  • It’s not for SQL beginners or those with minimal Oracle SQL experience
  • The time allocated is enough, but you shouldn’t waste a lot of time on each question
  • Some of the more difficult areas included regular expressions, operator precedence, nested Group BY functions, NOT IN with NULL rows, NEXTVAL and CURRVAL behaviour

 

What Impact Will Getting This Certification Have on My Salary?

It’s hard to tell what the impact this will have on your salary. According to a white paper prepared by Oracle in 2013, 77% of certified IT professionals believe that certifications have impacted their salary. As I’ve mentioned in a recent article on improving your salary as a database administrator, certifications are a great way to do this.

It may also have an indirect improvement, as you’ll be more knowledgeable about Oracle SQL, and likely be more experienced, both of which have a good impact on your salary.

 

What Resources Are Available For This Certification?

I’ve created a course that covers introductory topics for Oracle SQL, which you can find here. Note that while this course is excellent, it doesn’t cover all of the topics in this exam, as it’s focused on beginners and intermediate users of Oracle SQL. It is a great starting point, though.

Oracle offers several exam preparations for this exam:

There are also some Amazon books on this exam which have proven useful.

 

What Other Certifications or Exams Are Related to This One?

Oracle offers another certification, the Oracle Database SQL Fundamentals. The exam code for the 11g version is 1Z0-051. This exam uses a lot of the same material and topics, and is a more beginner-level exam. So, if you’re looking for a starting point, you may want to begin with that certification.

 

What Do You Get When You Pass The Exam or Complete the Certification?

When you complete the exam and pass the certification, you get the following things:

  • A membership card
  • A printed certificate on hard paper
  • A welcome letter to the Oracle Certified Professional community and instructions on how to download the logo and open an account for certified members

 

I hope you get a lot of value from this article. Do you have any other questions on the Oracle Database SQL Expert certification, or the 1Z0-047 exam? Ask them in the section below.

If you’re interested in learning more about Oracle SQL, take my Introduction to Oracle SQL course.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Improve Your Oracle SQL With My 10-Day Email Course

x