Notes for 1Z0-047 Part I

By | December 16, 2010

Nothing groundbreaking here… Just some notes of some things I don’t want to forget when taking the exam. There will most likely be a few more posts like this one in the near future as I continue to study.

Object Naming

– After first character, can contain letters, numbers, $, _, and # without being inclosed in quotes.


  • LOBs – Can’t be included in PK, distinct, group by, order by or join
  • char(n) – n defaults to 1; max = 2000
  • varchar2(n) – n must be specified; max = 4000
  • number(n,m) – n = precision (sig figs), errors if exceeded; m = scale (digits to right of decimal), rounded if exceeded


  • “In Line” or “Column-level” constraints:
    create table ships
        ship_name varchar2(20) constraint shipname_pk primary key
  • “Out of Line” or “Table-level” constraints:
    create table ships
        ship_name varchar2(20),
        constraint shipname_pk primary key(ship_name)
  • Not Null constraints can not be created “out of line”
  • constraints with multiple columns can not be created “in line”
  • Check constraints can reference other columns
  • Timestamp w/ TimeZone can’t be used in Unique, PK, or FK constraints, but Timestamp w/ Local TimeZone can


Inserts – If column list omitted from insert, then values for all columns in table must be provided

Control Transactions

Commit/Rollback – Optionally include “work”: commit work; rollback work;



  • Projection – Limit which columns are returned
  • Selection – Limit which rows are returned
  • Joining – Get data from related sources

Unique is equiv. to Distinct
^= is equiv. to != or <>

Operator Order of Operations – Not > And > Or


Single-row functions also reffered to as scalar functions

 TRIM( LEADING/TRAILING/BOTH [trim_char] FROM [trim_source]) 

Time Zones

  • TZD – Time zone with Daylight Saving Time built in (e.g. EST, CST, EDT, etc.)
  • TZH – Time zone hours
  • TZM – Time zone minutes
  • TZR – Time zone region (e.g. America/Chicago)

Leave a Reply

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

Turn on pictures to see the captcha *