Why did I write this article?
When I was preparing for Java 8 OCA and OCP I found a lot of useful articles about subjects on Habr that helped me to choose the optimal path and save a considerable amount of time.
When I started preparing for OCA Oracle Database SQL (1Z0-071) I didn’t find any materials on Habr about this matter and found there to be limited information available on the Internet. Because of this I decided to write a complete guide in order to help others who are interested in this certification to help them save time and successfully pass what I consider to be a pretty hard exam.
Why did I decide to prepare for the exam?
The main reason was my desire to streamline the knowledge that I had acquired throughout my years of study and work and fill in all the gaps. I also wanted to know what level of understanding the database vendor itself, in this case, Oracle, expects from developers.
I decided that simply reading a book about SQL was not enough as without real practice it would be easy to assume that I have a firm understanding of everything I have read. However, it became apparent that when preparing for certifications I would be required to complete tests on almost every detail of the book. This would entail reading and re-reading everything in great depth, which as you can imagine, was extremely time consuming.
I began to ask myself if I could read a book and practice detailed test questions, then why do I need to pass an official certification? As an additional motivation.
It was then, that it became clear to me that when taking tests, I can involuntarily take pity on myself and forgive my shortcomings, but the real exam will not allow for such flaws in my understanding and the certification is a fairly objective measure of knowledge.
Furthermore, you can show the certification to your current employer or use it as an additional credential for future employment. Hypothetically, it should characterize you as a person who can set your own goals, and show dedication through your ability to plan, prepare by process a large amount of information whilst developing a firm understanding of the course content at depth.
To conclude, the main reasons for obtaining the Oracle Database Certification were to gain confidence in my own knowledge and a certification badge that can now be displayed and used to great advantage.
What did I know before starting preparation?
At school in 10th grade, I sat a DMBS course, however, besides physically being present on the course, there is little I remember in terms of gaining an understanding for DMBS. A few years on at University, I embarked upon and passed two courses on databases, and enjoyed both of them very much.
The first course was "Database Theory" led by Dr. Gasanov the Russian language, a very fundamental and theoretical course.
The second also in Russian, was simply named "Databases" led by Dr. Kumskov. This was a practical course on database types, involving how to analyse requirements, how to design a database, and much more.
Both courses are publicly available, but to pass certification, of course, you do not need to study them.
In addition to taking various courses, like many, I continued using MySQL, PostgreSQL and SQLite both at home and in the workplace.
And whilst believing that I was pretty much prepared for day-to-day work as an engineer, there came a point where I felt that there were topics I had not covered and areas which were unfamiliar to me.
Why Oracle Database SQL?
To begin with, I considered three databases; PosgreSQL, MySQL, and Oracle Database SQL.
PostgreSQL does not have official certification, and the MySQL certification is old (and personally I do not fully understand its value), leaving me in no doubt that the Oracle Database SQL was by far my best option.
I chose Oracle because the certification has been recently updated, and I believe it holds more value than the others. Since it is purely an SQL certification (not Oracle specific PL/SQL) all things described will more or less work on all other SQL Databases as most of them support ANSI SQL.
How did I prepare?
Here I describe the preparation process in detail, the actions I took and the mistakes I made.
Experience is the son of mistakes
Before starting preparation, I wanted to install Oracle SQL. I had read on Habr that Oracle 18c XE had been released and so I went to download it from the web site but found that MacOS was not supported. Although I had VPS on Ubuntu at that time, I did not have Windows. Sadly I could not run Oracle Database on Ubuntu.
Eventually, I decided to purchase Digital Ocean VPS with CentOS and without any trouble installed DB. At that time, I was not familiar with official SQL Developer IDE but I had DataGrip by JetBrains already installed on my machine. This enabled me to connect DataGrip to Database on DigitalOcean and I finally began to work through the official exam preparation book.
I began by reading the book and taking the provided test at the end of each chapter. In addition to this, I would frequently test examples from the book on my database in order to fully understand each topic in detail.
After each test I would check for accuracy and should there be an error, I would read the answer explanation without re-reading the whole chapter.
I was studying three times per week for two-three hours, perhaps one week would be less, another week more. I can't say that it was pleasant reading, in fact sometimes I felt despair, but my goal had been set, and my plan laid down, so I continued on. Sometimes it was hard to understand some of the content of the book — even after re-reading a paragraph I still couldn't understand what author meant.
Finally at the beginning of June, after more than a month, I finished the book and took the first out of two mock exams in the emulator which was provided with the book. Unfortunately I lost track of time and completed less than half of the questions. By default, the emulator didn't allow me to continue the test (Unlinke Enthuware for Java which does allow for extra time). Evidently, with a score of less than 50%, I realised that I hadn’t fully learned the content of the book and that more work was needed.
Things begin to get better
Deciding to search for new studying materials I came across Udemy course. At first, I was sceptical about using a video course as I had always preferred to use books when preparing for exam. However, ince I could not find another alternative I decided to read the reviews and consequently decided to buy it for $73 without very little expectation of the product. (As it turned out, about once a week, the price drops to $12, so it's worth waiting for some time.)
By this point, I was tired of constantly connecting to VPS in order to practice on a real database and frustrated that DataGrip did not support SQL*Plus commands. It was for this reason, I eventually found a laptop on Windows and decided to install everything locally, carefully following the instructions from the video source.
Surprisingly, despite my low expectations, I was very pleased with the course. The author gave clear instructions on how to install the database, SQL Developer, and how to unlock the test user HR prepared by Oracle with pre-populated data. During the entire video course almost all examples were shown using HR user database. The author also showed that you need to use SQL Developers, which turned out to be the right and free choice.
It is strange that neither installation of a database, nor SQL Developer, nor HR user is described in the official book, asin the exam itself, there were often questions that are based on data from HR database. Had I have know this earlier on, I would definitely have started with the video course first, and only then move on to the book, a process which I believe would have saved me a great deal of time.
In each chapter of the course, there was first a brief theory, and immediately after, real practical examples performed by the author in SQL Developer. From the onset, I decided that I would repeat and fully understand each example locally on my laptop. Therefore, twenty hours of video turned into a month and a half of study and practice, although there were breaks too. Since the course was interesting, as a result, the motivation to finish it was much higher than the motivation to finish the book.
When I finished the course, I finally felt that the general picture had cleared up and that I understood all of the most important aspects. I performed the second test in the emulator, which comes with the book and again scored less than 50%, albeit better than the first time round. Again I messed up something with the settings and did not manage to finish the exam in the given time. Although by then, I was confidently answering most of the questions. As a result, I turned off the countdown and launched a mode in which it was possible to solve all questions at once without any restrictions.
I read the question, answered it, if I thought that I couldn't give definite answer, every time I wrote down the topic of the question in a notebook, even if the topic is already on the list. After I chose the answer, I checked the correctness and tried to delve into explanation that is given in the emulator. By the way, I find the quality of emulator and explanation of the questions at a high level.
I read the question, answered it, and if I thought that I couldn't give definite answer, I wrote down the topic of the question in a notebook (even if the topic was already on the list) After choosing the answer, I checked to see if I was correct and tried to delve into the explanation that was given in the emulator, which I found to be of very well written. After working through all of the questions from the emulator, I counted how many times each topic appeared in the list and sorted it in descending order of frequency, so the topics that I had the most trouble with appeared at the top. This allowed me not to re-read the chapters that I had learned well, but to focus only on those that I did not understand, resulting in me rereading four chapters from the list.
It was then, when I was re-reading, that everything seemed to fall into place and I truly began to understand everything. I again took the test at the end of each chapter that consisted of 10-15 questions. It should be noted there that although I had already solved these questions a month and a half previously I had forgotten most of the answers.
Practice
It was clear that the emulator from the book was not enough, and it was necessary to take two or three mock exams before passing. At first I thought of buying Kaplan Training. Oracle advises them on the official exam page, but the price of $189 for the full package discouraged me. Therefore, I bought the tests that author suggested in his Udemy course for $60 it includes PDF + emulator.
Unlike Enthuware, where the tests are divided into 7 independent exams, these tests are divided into two parts; in one part there are 272 questions, in another 27. Not understanding the logic behind this, I simply divided this list into 3 separate question packs with 78 in each as if in real exam.
I estimate the purchased materials below average, when compared with Enthuware. There were errors in questions and answers, and often there was no explanation for the question. There was no link to the forum in order to discuss an issue, and there was no way to look at detailed reports in order to understand which topics were misunderstood.
The process was as before, I would read the question, if I was not certain about the answer, I would write the name of the topic in a notebook. After the first mock exam, I read through the list, pick and re-read three chapters that presented the most questions, and checked the book examples on my local database. I repeated this one more time with second mock exam, In total, re-reading nine or ten chapters out of fourteen.
At this point, I was ready for the exam as I was able to confidently answer any remaining questions.
Exam Day
The day came to sign up for the exam. In my case there were not many dates available, so I did this immediately after finishing the video course. It should be noted that PearsonVue now offer the opportunity to take any of their exams online, but there were many addition conditions so I decided to take the test in a local test center. The exam cost is $245 US dollars.
I usually take exam at twelve o’clock, I have breakfast but not lunch and arrive 30 minutes early. I eat chocolate on the spot, take a few sips of water and go in. I don’t really want to go to the toilet during the exam, so usually take such precautions.
From preparation experience I knew that for me, two hours would not be enough, so I mentally broke the exam into 4 parts and knew that for every half an hour I had to solve twenty questions and move according to plan. At the beginning of the exam, on an issued sheet of paper, I wrote this simple plan so that it was visible to me throughout.
If there was an exhibit in the question, then the first thing they suggested was to look at it. I didn’t do this until I read the question and went through the options for answers, as very often you can give the answer either without looking at the exhibit, or you will already know t exactly what you need to pay attention to when analysing it, and are therefore able to save time.
Questions that took more than two or sometimes three minutes were answered by the method of exclusion and the choice of the most suitable answer in order to save time. Interestingly, during the exam itself, there were several occasions where I came across exactly the same questions as were approached during the preparation, and others were similar.
I remained calm throughout the exam. Following my time plan, and finishing with 10 minutes to spare, I used the remaining time to check over each question, ensuring everything was answered fully. Only fifteen minutes after completing the exam, I received an email saying that I could check my results. I went to the website and was over the moon to find out that I had passed, with a result of 85%.
My advice on exam preparation
Had I known in advance what materials to use and in what sequence to use them, I would have reduced my preparation time for the exam, down to just two to two and half months.
Most importantly, I would start with a video course instead of a book and I would not read the book completely but according to the scheme described above, I would stick to reading chapters on the least understood topics.
I would not deploy the database to VPS and would not use DataGrip. I would immediately install the DB and SQL Developer locally on my computer, following instructions from the video course.
The following is a layout of my exam preparation roadmap:
- Study the Udemy course and practice all examples on a locally installed DB.
- Install emulator from official preparation book by Steve O'Hearn, but do not read the book itself.
- Take a mock exam. Along the way write down all the topics that you can not give a definitive answer.
- Read the least understood chapters. Usually the first three is enough.
- Repeat steps 3 and 4 for the second mock exam from the emulator.
- Buy practice tests. Take two more tests repeating 3. and 4.
- Schedule and exam and pass!
Afterword
It is my hope that this article will help anyone who wishes to pass the Oracle Database SQL exam. My reason for describing each aspect of the process was firstly to describe my learning journey in detail and share my conclusion for the best way to approach the exam, and secondly, because there is very little information available regardingthis exam on the Internet, hence I was hopefully able to fill part of the gap.
Good luck in the exam!