Continuation of my notes to review before taking the 1Z0-047 certification.
Hierarchical Queries
- fork is the term for a node that has 2 or more children
- If you have both JOIN and CONNECT BY, the join is processed before the connect by
- Order of START WITH and CONNECT BY clauses don’t matter with respect to each other, but both must be after WHERE and before ORDER BY
- Keyword prior can be used in seelct list to return values from the previous row in the hierarchy (which may or may not be the previous row in query output)
- ORDER BY will order all rows across all levels
- ORDER SIBLINGS BY will order rows just within the context of the current level
- Filter criteria can be added to CONNECT BY clause to exclude entire branches of the tree
- This will exclude node with employee_id = 10 and all of its children in hierarchy tree:
select *
from employees
start with manager_id is null
connect by manager_id = prior employee_id
and employee_id <> 10;
- This will exclude node with employee_id = 10 and all of its children in hierarchy tree:
Regular Expressions
- POSIX character classes must be all lower case, otherwise will throw a syntax error ([:digit:] works, [:DIGIT:] does not)
- If conflicting match parameters are specified at same time, last one is used
- ‘ic’ would result in case-sensitive matching from the “c”, even though case-insensitive matching was also specified by the “i”
- Parenthasis are used to look for exact strings and allows string to be combined with metacharacters. A Pipe inside the parenthasis acts as an OR between exact strings
- ‘[[:alpha:]]+(ing)’ will return first word ending in “ing”
- ‘[[:alpha:]]+(ing|ed)’ will return first word ending in “ing” or “ed”
Privileges
- ALL PRIVILEGES – Keyword PRIVILEGES optional for object privileges but mandatory for system privileges:
- Valid:
grant all on ships to george;
- Invalid:
grant all to george;
- Revoking all system privileges from a user will throw error unless that user currently has ALL system privileges
- Revoking all object privileges from a user will not error, even if that user currently has NO privileges on that object
- Valid:
- Privileges are dropped when associated objects are dropped, and must be recreated if object is recreated and you want them back
- Exception is when using FLASHBACK TABLE to restore to before drop, which also restores object privileges as well as table
- WITH ADMIN OPTION – Can be used when granting system privileges and roles to allow recipient to be able to grant that privilege / role to others
- If that privilege / role subsequently revoked, all privileges / roles that user granted to others still remain
- WITH GRANT OPTION – Can be used when granting object privileges to allow recipient to be able to grant that privilege to others
- If that privilege subsequently revoked, all privileges that user granted to others are also revoked
- There is no CREATE INDEX privilege; it comes as part of the CREATE TABLE privilege
This concludes my notes to review before taking the 1Z0-047 certification. Now I just need to study! 🙂