Notes for 1Z0-047 Part V

By | January 25, 2011

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;

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
  • 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! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

Turn on pictures to see the captcha *