Studying for 1Z0-047: Oracle Database SQL Expert

1Z0-047 is one of the more popular Oracle certifications available. SQL knowledge is extremely useful in the IT industry and being acknowledged as an expert in this skill is a valuable addition to a resume. That said, this is not an easy exam to pass and I have seen several people in certification forums admit to failing it on one or more attempts. This article is intended to give you a glimpse into some of what will be expected from you on the test to help guide your preparation.

All of the topics that will be covered in the 1Z0-047 exam are listed on the Oracle Education website. There are capabilities in Oracle SQL that are not on that list, but they will not show up on the test. The topic lists from Oracle Education are always complete. The SQL Expert exam has thirty-eight topics that are also in the 1Z0-051 SQL Fundamentals exam. The expert test does not focus on these. Anything from them is fair game, but the majority of the questions will come from the thirty-eight subject areas that are specific to 1Z0-047. If you are trying for this certification, your knowledge of basic SQL should be a given.

As you would expect, almost every question in 1Z0-047 will have a SQL statement involved. Sometimes the question will contain a statement and you will have to choose among answers that indicate what it does. Other questions will describe a desired result and the available answers will consist of different SQL statements. You will have to choose the SQL that best fits the request. The SQL tends to be longer than what is in the SQL Fundamentals exam and relatively heavy on joins. For the test you need to be proficient at ANSI join syntax — the legacy Oracle syntax will not be used. You also need to have the ability to parse SQL in your head and determine what it will do. Most of the SQL in this test will execute without error but will not produce the intended results. This is much harder to detect than SQL that will simply fail when run.

You must be very knowledgeable about SQL syntax and be able to differentiate between what is possible and what is not. Some of the questions are likely to have SQL that is perfectly legal, but which is written in a way that might lead you to believe it is not. There are a number of legitimate ways to create SQL statements that hardly anyone ever uses. An example would be a HAVING clause placed before a GROUP BY clause. This will work, but I have never seen anyone write SQL that way. You’ll also need to recognize common SQL functions and be able to determine what the outcome of DDL statements will be. You’ll need to be familiar with several topics on subjects that even experienced SQL developers use rarely if at all. I’ve written SQL for seventeen years and have never used a ROLLUP or CUBE query yet in the workplace. I use REGEXP functions and hierarchical queries once in a blue moon. You’ll need to know all of these to do well on the test.

As to the SQL Expert exam itself, there will be 70 multiple-choice or multiple-answer questions. At this time 66 percent is the passing score. For the multiple-answer questions, there is no partial credit. Not answering a question counts against your score as much as answering one incorrectly, so you don’t want to leave any question unanswered — even if that means simply picking a letter at random. 1Z0-047 contains a much higher number of exhibits than the norm for Oracle certification exams. They are primarily entity relationship diagrams that provide background on the tables referenced by the SQL in the questions. You must be able to read entity relationship diagrams even though this is not listed as a test topic. Many of the diagrams are fairly complex and only a tiny portion of the diagram is really crucial to answering any given question. In addition, when I took the test, using the exhibit was not required for more than half of the questions that had them. The questions that have exhibits suggest that you view the diagram and then answer the question, but I would suggest reading the question first. This is likely to save you some time as you may be able to skip the exhibit entirely. If you do need to view it, you will know specifically what information you need to look for. When dealing with questions where the answer is one or more SQL statements, look though all of the answers. Often you can find at least one with a flaw that rules it out as a correct answer. Ruling out one or two of the answers will allow you to concentrate your efforts on the remaining possibilities.

1Z0-047 is intended to pass only those people who really have a thorough understanding of SQL, and it does a good job of that. You’ll find on this test that the wrong answers are not obviously wrong and the right answer does not stand out. The SQL and associated exhibits are reasonably complex and a fair number of the questions test your experience rather than your ability to memorize facts. Make sure you are comfortable with all seventy-six topics before scheduling the exam. Good luck on the test.