Studying for 1Z0-047: Oracle Database SQL Expert
1Z0-047 is one of the most popular Oracle certifications available. SQL knowledge is extremely useful in the IT industry and being recognized as an expert in this skill is a valuable addition to a resume. That being said, this is not an easy exam to pass and I have seen several people on certification forums admit to failing one or more attempts. The purpose of this article is to give you an idea of what is expected of you on the exam to help guide your preparation.
All 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 appear in the test. Oracle Education topic lists are always complete. The SQL Expert exam has thirty-eight topics that are also found in the 1Z0-051 SQL Fundamentals exam. The expert evidence does not focus on these. Anything from them is fair game, but most questions will come from the thirty-eight subject areas that are specific to 1Z0-047. If you are trying to earn this certification, you must provide your knowledge of basic SQL.
As expected, almost every question in 1Z0-047 will have an SQL statement involved. Sometimes the question will contain a statement and you will have to choose from the answers that state what it does. Other questions will describe a desired result and the available answers will consist of different SQL statements. You will need to choose the SQL that best suits the request. The SQL tends to be longer than the SQL Fundamentals exam and relatively heavy on joins. For the test, you must be proficient in ANSI join syntax; the Oracle legacy syntax will not be used. You also need to have the ability to parse SQL in your head and figure out what to do. Most of the SQL in this test will execute without errors but will not produce the expected results. This is much harder to detect than SQL, which will simply fail when executed.
You must have a good understanding of 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 is written in a way that might lead you to believe it isn’t. There are several legitimate ways to create SQL statements that hardly anyone uses. An example would be a HAVING clause placed before a GROUP BY clause. This will work, but I’ve never seen anyone write SQL like that. You will also need to understand common SQL functions and be able to determine what the result of DDL statements will be. You will need to be familiar with several topics that even experienced SQL developers rarely use, if at all. I have been writing 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 will need to know all of this to do well on the exam.
As for the SQL Expert exam itself, there will be 70 multiple choice or multiple answer questions. Right now, 66 percent is the passing score. For multiple-choice questions, there is no partial credit. Not answering a question counts against your score just as much as answering one incorrectly, so you don’t want to leave any questions unanswered, even if it means just picking a random letter. 1Z0-047 contains a much larger number of addendums than the standard for Oracle certification exams. They are primarily entity relationship diagrams that provide background on the tables referenced by the SQL in the questions. You should be able to read entity-relationship diagrams even if this is not listed as a test topic. Many of the diagrams are quite complex and only a small part of the diagram is really crucial to answer any question. Also, when I took the test, display use was not required for more than half of the questions that did. Questions that have attachments suggest that you view the diagram and then answer the question, but I suggest that you read the question first. This is likely to save you some time, since you can skip the display entirely. If you need to see it, you’ll know specifically what information to look for. For questions where the answer is one or more SQL statements, analyze all the answers. Often you can find at least one with an error which rules it out as a correct answer. Discarding one or two of the answers will allow you to focus your efforts on the remaining possibilities.
1Z0-047 is meant to go only to those people who really have a deep understanding of SQL, and it does a pretty good job of it. You will find in this test that the wrong answers are not obviously wrong and the correct answer does not stand out. The SQL and associated exhibits are reasonably complex, and a fair number of questions test your expertise rather than your ability to memorize facts. Make sure you are comfortable with all 76 topics before scheduling the exam. Good luck in the exam.