Oracle 1Z0-071 Exam Guide and FAQ (OCA Certification)

Are you interested in getting the Oracle Certified SQL Associate certification, and passing the 1Z0-071 exam?

If so, then this is the guide for you.

I recently achieved this certification, so in this guide, I’ll explain all there is to know about it.

Keep reading to learn all about this certification and exam.

What is the Oracle Certified SQL Associate?

The OCA, or Oracle Certified SQL Associate, is an entry-level certification for Oracle SQL. It’s offered by Oracle and is targeted at those who have some knowledge of Oracle SQL, but not as advanced as some of Oracle’s other certifications.

The full list of topics is listed below, but it requires you to know what INSERT, UPDATE, DELETE statements do, the different keywords and clauses of the SELECT statement, some DDL, how transactions work, and several other features.

It contains one exam, which has the code 1Z0-071.

 

What Exam is Required for the Oracle Certified SQL Associate?

You only need to pass one exam for the OCA certification, and that exam has the code 1Z0-071.

This exam is a multiple-choice exam containing 73 questions. It’s offered via a computer (not handwritten) and you’ll have 100 minutes to complete it (1 hour 40 minutes).

The pass mark is 63%, which means 46 questions will give you a pass.

 

How Much Does the 1Z0-071 Exam Cost?

The exam costs USD $225. I would recommend checking the official certification page on Oracle for up-to-date exam prices.

 

What Version of Oracle Does This Exam Cover?

The exam covers versions up to 12c Release 1 version 12.1.0.1.0.

This is not a very new version. The current version is 19c – but don’t let the large difference in numbers fool you.

Oracle changed their versioning in late 2017 to align with the years of releases as part of their aim to release more frequently. So the Oracle versions contain fewer changes each release but are released annually. The Oracle versions are:

• 11g
• 12c
• 18c
• 19c

So, Oracle 12c is only a few years old. Fortunately, the basics between 12c and 19c haven’t changed that much. An INSERT statement still looks and works the same, for example.

I haven’t had a lot of experience with 19c but I didn’t have any issues with understanding how the SQL worked when I took the exam.

 

What Topics are In the 1Z0-071 Exam?

The list of topics is listed on the Oracle exam page, but these are the topics as of mid-2019:

Using Structured Query Language (SQL)
– Explain the relationship between a database and SQL
Using Data Manipulation Language (DML) and Transaction Control Language (TCL)
– Describe the purpose of DML
– Use DML to manage data in tables
– Use TCL to manage transactions
Using Basic SELECT statements
– Build a SELECT statement to retrieve data from an Oracle Database table
– Use the WHERE clause to the SELECT statement to filter query results
Defining Table Joins
– Describe the different types of joins and their features
– Use joins to retrieve data from multiple tables
– Use self joins
Using Conversion Functions and Conditional Expressions
– Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
– Apply general functions and conditional expressions in a SELECT statement
Displaying Data from Multiple Tables
– Use 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
Using the Set Operators
– Use a set operator to combine multiple queries into a single query
– Control the order of rows returned
Using DDL Statements to Create and Manage Tables
– Describe data types that are available for columns
– Create a simple table
– Create constraints for tables
– Describe how schema objects work
– Execute a basic SELECT statement
Managing Objects with Data Dictionary Views
– Use the data dictionary views to research data on objects
– Query various data dictionary views
Managing Schema Objects
– Manage constraints
– Create and maintain indexes including invisible indexes and multiple indexes on the same columns
– Drop columns and set column UNUSED
– Perform flashback operations
– Create and use external tables
Using Data Definition Language (DDL)
– Describe the purpose of DDL
– Use DDL to manage tables and their relationships
– Explain the theoretical and physical aspects of a relational database
Defining SELECT Statements
– Identify the connection between an ERD and a database using SQL SELECT statements
Restricting and Sorting Data
– Use the ORDER BY clause to sort SQL query results
– Limit the rows that are retrieved by a query
– Sort the rows that are retrieved by a query
– Use ampersand substitution to restrict and sort output at runtime
Using Single-Row Functions to Customize Output
– Use various types of functions available in SQL
– Use conversion functions
– Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements
Reporting Aggregated Data Using the 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
Using Subqueries to Solve Queries
– Define subqueries
– Describe the types of problems subqueries can solve
– Describe the types of subqueries
– Use correlated subqueries
– Update and delete rows using correlated subqueries
– Use the EXISTS and NOT EXISTS operators
– Use the WITH clause
– Use single-row and multiple-row subqueries
Manipulating Data
– Insert rows into a table
– Update rows in a table
– Delete rows from a table
– Control transactions
Creating Other Schema Objects
– Create simple and complex views with visible/invisible columns
– Create, maintain and use sequences
Controlling User Access
– Differentiate system privileges from object privileges
– Grant privileges on tables and on a user
– Distinguish between privileges and roles
Manipulating Large Data Sets
– Describe the features of multitable INSERTs
– Merge rows in a table

 

What Materials Should I Get to Pass?

There are a few materials out there that can be used to pass the exam.

I found a course on Udemy that offers a range of videos to help pass the exam.

I also found a book on Amazon: “Study Guide for 1Z0-071: Oracle Database 12c SQL: Oracle Certification Prep”. I bought this book and thought this was a useful way to prepare.

However, I didn’t find a lot of other good-quality material to help me pass the exam. Perhaps it’s not a very common exam for those working with Oracle.

For this reason, I created a study course for the 1Z0-071 exam inside the Database Star Academy, the online training area for database developers. It also includes a practice exam with over 100 questions, so using both the study course and the practice test should help you pass the real exam.

I’ve also written a range of articles on different areas of Oracle SQL, which you can find on the SQL Roadmap page. They don’t yet cover everything on the exam but it should be helpful.

You can, of course, use Google to search for materials on the exam topics. You don’t need to use a course or a study guide.

 

Can I Get Practice Questions For This Exam?

Yes, you can, but you might find it hard to find genuine questions.

Why is that?

Almost all of the practice questions I found while I was preparing for the exam were “certification dump” or “brain dumps”.

I didn’t want anything to do with these. Why is that? Because certification dumps are against the rules and can actually hurt your career, which I’ve written more about here.

It’s hard to tell if a practice exam is a brain dump from the exam or are genuine questions.

As part of the study course I created for Database Star Academy, I created my own practice exam. This includes 100 practice questions based on a different dataset and different questions, but cover the same topics.

If you’re looking for practice questions, I would suggest doing a Google search for reviews before purchasing them, to see if anyone has any reports on their quality and to check they are not dumps.

 

How Should I Prepare to Pass the Exam?

Here’s how I suggest you prepare to pass the exam.

First, set yourself a timeline to get the exam. I would base this on the number of hours you can dedicate to study for the exam and your level of knowledge on Oracle SQL.

I gave myself two months, but that’s because I was pretty comfortable with most of the content of the exam.

Then, I would suggest actually purchasing the exam and booking it in. Why so early? Because having a date set of the exam will give you a deadline and ensure you study for it in time. Without a date, it’s easy to put off the exam and not get it done for a while.

So, book the exam in for a few months in the future. Whether it’s 3 months, 4 months, or some other period, doesn’t matter.

Now it’s time to get studying. Whether you decide to enrol in the Database Star Academy for the study course and practice questions, or you have your own materials to study (e.g. from Amazon, Udemy, or somewhere else), it’s time to start studying. You can also practice using one of the many SQL practice resources online.

Break down the exam topics into areas and study each of them. Ensure you are comfortable with the topics and can read and write SQL that uses a range of commands. For example, you should know the different ways to insert multiple records into one or many tables, what these methods are called, and how they are different.

By the end of your study period, or the time between starting and taking the exam, you should be pretty comfortable with the topics. Review the material you feel less comfortable about, and then go and take the exam!

 

What Else Should I Know About the Exam?

The process to purchase the exam goes like this:

1. Visit the Oracle exam website
2. Purchase a voucher from Oracle
3. Redeem the voucher at a testing centre, which is one of several companies that have the facilities to offer the exam (e.g. PearsonVUE).
4. Visit a testing centre
5. Take the exam

I didn’t have any trouble finding a testing centre where I live (Melbourne, Australia). Oracle has a page where you can find testing centres near you, so I suggest using that to find one.

The exam is computer-based. One of the administrators at the testing centre set it up for me, and I just went in an did the exam.

You’ll have 100 minutes to complete the exam. I took most of that time to finish the exam. I passed on my first attempt, which I put down to the study I did and the experience I’ve had with Oracle SQL.

You can’t take your phone into the exam and won’t have access to the Internet, but you’ll have a pen and notepad to take notes on, which can be useful.

 

Conclusion

The Oracle Certified SQL Associate is an entry-level certification, but you do need to have a good level of knowledge of Oracle SQL to pass the exam. It’s not difficult if you’ve had quite a bit of experience, but you’ll need to study for it before you pass it.

Hopefully this guide has helped you and explained a bit more about the exam to help you pass and get your OCA certification.