We reach the level of expert! 50 shades of exam 1Z0-047 (Oracle Database SQL Certified Expert)
Hello! In this article we will talk about one of the basic certifications from ORACLE - Oracle Database SQL Certified Expert.
To get a certificate, you need to pass the exam 1Z0-047 .
There is an exam for different countries in different ways. For Azerbaijan it is 150 USD . There is also an option to buy tests for practice from Transcender along with an exam voucher (bundle). It will cost you 200 USD . And it is more profitable than buying tests separately.
He prepared himself for the following books and documents + practice based on:
')
- Oracle Database 11g SQL (Oracle Press) by Jason Price (an old but good book)
- OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061) (Oracle Press) (I was very pleasantly surprised when I learned how many useful tips it carries)
- Beginning Oracle SQL (Expert's Voice in Oracle) (a lot of useful. A good book)
- OCE Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047) (Oracle Press) (classics of the genre)
- Oracle Database Sample Schemas 12c Release 2 (12.2) (required when deploying test schemas)
I can not say that the exam is simple.
Therefore, I decided to share the gadgets and hidden tricks that await you there.
So let's go!
1. SQL is a declarative programming language that describes which calculations should be made, but not how . As is the lot of imperative languages.
2. When you are asked to rate a code or a schema or table, do not waste time studying them in detail. First, study the issue completely. You may not even need a table, a schema, or an SQL expression.
3. The maximum dimension of the NUMBER type is 38 characters.
4. The default date mask is DD-MON-YY . Although only the last two digits of the year are visible, by default all four are still stored.
5. The most common form for transactional databases is the 3rd normal form.
6. FOREIGN KEY may not necessarily be tied to a PRIMARY KEY . The only condition is that this field must be UNIQUE .
7. Maximum dimension of VARCHAR2, NVARCHAR2, RAW :
8. Valid range for DATE - from January 1, 4712 BC. until December 31, 9999.
9. The dimension of fractional seconds for TIMESTAMP (of all subspecies) can be from 0 to 9. But the default is 6.
10. In total on examination are considered:
11. WHERE will return FALSE if the list of values for the NOT IN operator is NULL . Be careful!
12. Number of joins = number of tables in joins - 1.
13. Using the name of a table or alias in USING is prohibited!
14. SELECT implements the basic three concepts: PROJECTION, SELECTION, JOINING .
15. Be attentive to punctuation. Test compilers can intentionally make a mistake in the code!
16. Any arithmetic operation with NULL will necessarily return NULL .
17. It is a mistake to assume that the result of an arithmetic operation with dates will also be a type of date. Not. As a rule, the type of response is either of type INTERVAL or type NUMBER .
18. DISTINCT can be used with any of the aggregation functions.
19. The order of commands in the usual request:
20. There is no privilege DROP TABLE . There is a DROP ANY TABLE .
21. To give a privilege to all users, you must give it PUBLIC .
22. INDEX and CONSTRAINT have their own namespace.
23. Aggregation functions are prohibited in the WHERE clause .
24. GROUP BY can be used without HAVING , but HAVING can only be used with GROUP BY .
25. CONSTRAINT of type NOT NULL cannot be created outside the field description when creating the table, but only together with the field description.
26. In the MONTHS_BETWEEN () function, during parameter substitution, it is allowed to set a smaller date as the first parameter, and then a larger date. Just the answer will be no.
27. The DATE type cannot store fractional seconds and time zones.
28. You can use any aggregate function with ROLLUP .
29. Grouping functions can be nested on a maximum of 2 levels.
30. In ORDER BY the column can be accessed in 3 ways:
31. NATURAL + USING or NATURAL + ON - not a place in one request. You will get an error.
32. In the FROM clause there can be an infinite number of nested subqueries, but in the WHERE clause - a maximum of 255.
33. In total there are 3 types of subqueries: SINGLE-ROW, MULTIPLE-ROW and CORRELATED .
34. The counting of characters in the string starts from 1, not from 0.
35. The only SET OPERATOR , in which the rows are not sorted - UNION ALL .
36. Cases where a transaction is forcibly completed:
37. The most appropriate fields for indexing:
38. In ORACLE SQL, there is no type BOOLEAN .
39. That COUNT (1) is faster than COUNT (*) is just a myth .
40. It is impossible to change the database time zone if there is at least one table with a field of type TIMESTAMP WITH LOCAL TIME ZONE .
41. The function can accept from 0 or more parameters. But always returns 1 value. No more and no less.
42. COUNT can never return NULL . If there are no rows, returns 0.
43.
44. SEMIJOIN is a query using the EXISTS operator.
45. If NEXTVAL was called, the counter will work regardless of whether the request was successful or not.
46. If a synonym was created for the object, and the object was liquidated, then the synonym continues to exist.
47. FLASHBACK TABLE cannot be rolled back.
48. For n expressions, CUBE returns 2 to the power of n groupings.
49. SQL, PL / SQL and JAVA are native languages for ORACLE DB .
50. To form a hierarchical query, the presence of START WITH or CONNECT BY is mandatory!
This article is a slide show on YouTube .
Good luck to you all! See you soon.
To get a certificate, you need to pass the exam 1Z0-047 .
There is an exam for different countries in different ways. For Azerbaijan it is 150 USD . There is also an option to buy tests for practice from Transcender along with an exam voucher (bundle). It will cost you 200 USD . And it is more profitable than buying tests separately.
He prepared himself for the following books and documents + practice based on:
')
- Oracle Database 11g SQL (Oracle Press) by Jason Price (an old but good book)
- OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061) (Oracle Press) (I was very pleasantly surprised when I learned how many useful tips it carries)
- Beginning Oracle SQL (Expert's Voice in Oracle) (a lot of useful. A good book)
- OCE Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047) (Oracle Press) (classics of the genre)
- Oracle Database Sample Schemas 12c Release 2 (12.2) (required when deploying test schemas)
I can not say that the exam is simple.
Therefore, I decided to share the gadgets and hidden tricks that await you there.
So let's go!
1. SQL is a declarative programming language that describes which calculations should be made, but not how . As is the lot of imperative languages.
2. When you are asked to rate a code or a schema or table, do not waste time studying them in detail. First, study the issue completely. You may not even need a table, a schema, or an SQL expression.
3. The maximum dimension of the NUMBER type is 38 characters.
4. The default date mask is DD-MON-YY . Although only the last two digits of the year are visible, by default all four are still stored.
5. The most common form for transactional databases is the 3rd normal form.
6. FOREIGN KEY may not necessarily be tied to a PRIMARY KEY . The only condition is that this field must be UNIQUE .
7. Maximum dimension of VARCHAR2, NVARCHAR2, RAW :
- 32767 bytes - if MAX_STRING_SIZE = EXTENDED
- 4000 bytes - if MAX_STRING_SIZE = STANDARD
8. Valid range for DATE - from January 1, 4712 BC. until December 31, 9999.
9. The dimension of fractional seconds for TIMESTAMP (of all subspecies) can be from 0 to 9. But the default is 6.
10. In total on examination are considered:
- 5 DML commands: SELECT, INSERT, UPDATE, DELETE, MERGE
- 8 DDL commands: CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT, FLASHBACK, PURGE
- 2 DCL commands: GRANT, REVOKE
- 3 TCL commands: COMMIT, ROLLBACK, SAVEPOINT
11. WHERE will return FALSE if the list of values for the NOT IN operator is NULL . Be careful!
12. Number of joins = number of tables in joins - 1.
13. Using the name of a table or alias in USING is prohibited!
14. SELECT implements the basic three concepts: PROJECTION, SELECTION, JOINING .
15. Be attentive to punctuation. Test compilers can intentionally make a mistake in the code!
16. Any arithmetic operation with NULL will necessarily return NULL .
17. It is a mistake to assume that the result of an arithmetic operation with dates will also be a type of date. Not. As a rule, the type of response is either of type INTERVAL or type NUMBER .
18. DISTINCT can be used with any of the aggregation functions.
19. The order of commands in the usual request:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
20. There is no privilege DROP TABLE . There is a DROP ANY TABLE .
21. To give a privilege to all users, you must give it PUBLIC .
22. INDEX and CONSTRAINT have their own namespace.
23. Aggregation functions are prohibited in the WHERE clause .
24. GROUP BY can be used without HAVING , but HAVING can only be used with GROUP BY .
25. CONSTRAINT of type NOT NULL cannot be created outside the field description when creating the table, but only together with the field description.
26. In the MONTHS_BETWEEN () function, during parameter substitution, it is allowed to set a smaller date as the first parameter, and then a larger date. Just the answer will be no.
27. The DATE type cannot store fractional seconds and time zones.
28. You can use any aggregate function with ROLLUP .
29. Grouping functions can be nested on a maximum of 2 levels.
30. In ORDER BY the column can be accessed in 3 ways:
- By the name of the column.
- By its alias.
- By serial number.
31. NATURAL + USING or NATURAL + ON - not a place in one request. You will get an error.
32. In the FROM clause there can be an infinite number of nested subqueries, but in the WHERE clause - a maximum of 255.
33. In total there are 3 types of subqueries: SINGLE-ROW, MULTIPLE-ROW and CORRELATED .
34. The counting of characters in the string starts from 1, not from 0.
35. The only SET OPERATOR , in which the rows are not sorted - UNION ALL .
36. Cases where a transaction is forcibly completed:
- The user himself issued a COMMIT or ROLLBACK command.
- The user has issued any DDL or DCL command.
- User session has expired.
- Broken ORACLE (God forbid, of course!)
37. The most appropriate fields for indexing:
- Foreign key fields.
- Fields most used in WHERE , GROUP BY and ORDER BY .
38. In ORACLE SQL, there is no type BOOLEAN .
39. That COUNT (1) is faster than COUNT (*) is just a myth .
40. It is impossible to change the database time zone if there is at least one table with a field of type TIMESTAMP WITH LOCAL TIME ZONE .
41. The function can accept from 0 or more parameters. But always returns 1 value. No more and no less.
42. COUNT can never return NULL . If there are no rows, returns 0.
43.
SELECT COUNT (ALL DUMMY) FROM DUAL is a syntactically valid query.44. SEMIJOIN is a query using the EXISTS operator.
45. If NEXTVAL was called, the counter will work regardless of whether the request was successful or not.
46. If a synonym was created for the object, and the object was liquidated, then the synonym continues to exist.
47. FLASHBACK TABLE cannot be rolled back.
48. For n expressions, CUBE returns 2 to the power of n groupings.
49. SQL, PL / SQL and JAVA are native languages for ORACLE DB .
50. To form a hierarchical query, the presence of START WITH or CONNECT BY is mandatory!
This article is a slide show on YouTube .
Good luck to you all! See you soon.
Source: https://habr.com/ru/post/327012/
All Articles