50 Shades of Oracle Database Certification Exam

In this article, I would like to talk about one of the basic certifications from Oracle – Oracle Database SQL Certified Expert. Unfortunately, this certification has become unavailable recently, but still, this article may be useful for preparing for other certifications and exams from Oracle. I wish a good read to everyone who wants to know which questions and tricks may await them and wants to be ahead of the game.

To get a certificate, I had to pass exam 1Z0-047.

Leaping ahead, I can tell that the exam was difficult and featured lots of hidden tricks and surprises. You will learn how to overcome tricky questions and what knowledge is required for successful pas of the 1Z0-047 exam from the following 50 items.

So, let’s start!

1. SQL is a declarative programming language that describes which calculation must be performed, not how. How is an issue of the imperative languages.
2. When you are asked to evaluate either schema or table, do not waste your time on their detailed investigation. Firstly, explore the whole question. Perhaps, you will need neither table, nor schema, nor SQL expression.
3. The maximum dimension of the NUMBER type is 38 characters.
4. The default date mask is DD-MON-YY. Though only last two digits are visible, all four are stored by default.
5. The most widespread form of the transaction DBs is 3NF.
6. FOREIGN KEY can be linked not only to PRIMARY KEY. The only condition is that this field must be UNIQUE.
7. The maximum dimension of VARCHAR2, NVARCHAR2, and RAW is:
   • 32767 bytes, if MAX_STRING_SIZE = EXTENDED
   • 4000 bytes, if MAX_STRING_SIZE = STANDARD
8. The valid range for DATE is January 1, 4712 BC – December 31, 9999.
9. The dimension of fractional seconds for TIMESTAMP (of all kinds) may be from 0 to 9. The default one is 6.
10. In general, the following statements are considered within the exam:
    • 5 DML statements: SELECT, INSERT, UPDATE, DELETE, MERGE
    • 8 DDL statements: CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT, FLASHBACK, PURGE
    • 2 DCL statements: GRANT, REVOKE
    • 3 TCL statements: COMMIT, ROLLBACK, SAVEPOINT
11. WHERE always returns FALSE, if there is NULL in the value list for the NOT IN keyword. Be careful!
12. The number of joins = the number of table in joins – 1.
13. Usage of table name or aliases in USING is forbidden!
14. SELECT implements the following basic conceptions: PROJECTION, SELECTION, JOINING.
15. Mind punctuation. The authors of the test may have made intentional errors in the code!
16. Any arithmetic operation with NULL always returns NULL.
17. An assumption that the result of an arithmetic operation with dates will be of the date type is wrong. As a rule, it is of the INTERVAL or NUMBER type.
18. DISTINCT can be used with any aggregate function.
19. The sequence of command execution in a standard query:
    • FROM
    • WHERE
    • GROUP BY
    • HAVING
    • SELECT
    • ORDER BY
20. There is no DROP TABLE privilege. There is DROP ANY TABLE.
21. To grant a privilege to all users, grant it to PUBLIC.
22. INDEXes and CONSTRAINTs have their own namespace.
23. Aggregate functions are prohibited in the WHERE block.
24. GROUP BY can be used without HAVING, but HAVING can be used only with GROUP BY.
25. CONSTRAINT of the NOT NULL type cannot be created beyond the description of the field when creating a table. It can be created only with the field description.
26. When setting parameters for the All MONTHS_BETWEEN() function, it is allowed to put the lesser date first, and then the greater one. In this case, the result will be negative.
27. The DATE type can not store functional seconds and time zones.
28. Any aggregate function can be used with ROLLUP.
29. Aggregate functions do not support more than two levels of nesting.
30. The ORDER BY column can be referred in the following three ways:
    • By the column name.
    • By its alias.
    • By its ordinal number.
31. NATURAL + USING or NATURAL + ON  cannot exist in one query. You will get an error.
32. The FROM section can contain an unlimited number of nested subqueries, but a maximum number of nested subqueries for the WHERE section is 255.
33. There are 3 kinds of subqueries: SINGLE-ROW, MULTIPLE ROW and CORRELATED.
34. Counting of characters in a row begins with 1, not 0.
35. The only SET OPERATOR operator that does not sort rows is UNION ALL.
36. A transaction is forcibly closed in the following cases:
    • A user issued the COMMIT or DCL ROLLBACK command on their own.
    • A user issued any DDL or DCL command.
    • User session has expired.
    • ORACLE has broken (God forbid)).
37. The most suitable fields for indexing are:
    • Foreign key fields
    • Fields that are frequently used in WHERE, GROUP BY and ORDER BY.
38. There is no BOOLEAN type in ORACLE SQL.
39. A belief that COUNT(1) is faster than COUNT(*) is just a myth.
40. There is no way to change the database time zone if there is at least one table with a field of the TIMESTAMP WITH LOCAL TIME ZONE type.
41. A function can accept from 0 to many parameters. But it always returns 1 value.
42. COUNT will never be able to return NULL. It will rerun 0 when there are no rows.
43. SELECT COUNT (ALL DUMMY) FROM DUAL is a syntactically correct query.
44. SEMIJOIN is a query with the EXISTS clause.
45. If NEXTVAL is called, the counter will begin to work regardless of the query execution success.
46. If an alias is created for an object, and the object does not exist anymore, the alias will go on existing.
47. FLASHBACK TABLE can’t be rolled back.
48. For n expressions, CUBE returns 2 raised to the power of n groupings.
49. SQL, PL/SQL and JAVA are native languages for ORACLE DB.
50. To create a hierarchical query, START WITH or CONNECT BY must be present.

Thank you for reading. I look forward to your questions and comments.