Monthly Archives: December 2010

Continuation of my notes to review before taking the 1Z0-047 certification.

Aggregate Functions

• Can only be nested 2 levels deep: select avg(sum(count(x))) from table will throw an exception
• The GROUP BY and HAVING clauses can appear in either order:
  
select mod(rownum,10), count(*)
from dual
connect by level <= 105 group by mod(rownum,10) having count(*) <> 10
order by 1

  or
  
select mod(rownum,10), count(*)
from dual
connect by level <= 105 having count(*) <> 10
group by mod(rownum,10)
order by 1


Joins

Natural Join

• Joined by all columns whose name matches exactly
  • Datatype doesn’t have to match to attempt the join, and will raise a runtime exception if incompatible
• Common columns can’t be aliased anywhere in query or an exception is raised
• Always an inner join

USING

• Can be either inner or outer join (just replaces ON clause)
• Specified columns can’t be aliased anywere in query
  • value is shared value where they match; not null value otherwise (could come from either table)

Multi-Row Subqueries

ANY / SOME / ALL:

select *
from ships
where capacity > ANY (select capacity from ships where class = 1);


ANY / SOME – Returns true if condition passes for at least one row in subquery
ALL – Returns true only if condition passes for all rows in subquery

Scalar Subqueries

• Returns single value (one row, one column)
• Can’t be used in:
  • CHECK constraints

  • GROUP BY clause
  • HAVING clause
  • Function-based indexes
  • Default column value
  • Returning clause of DML
  • WHEN condition of CASE
  • START WITH / CONNECT BY clauses

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)