Notes for 1Z0-047 Part I

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.

Datatypes

• 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

Constraints

• “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

DML

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;

Select

Capabilities:

• 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

Functions

Single-row functions also reffered to as scalar functions
Trim –

 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)