Monthly Archives: December 2010

Presenting at OOUG: Jan 20

I will be presenting at the Ohio Oracle Users Group meeting on January 20. My presentation will be on making sense of execution plans. I will post the presentation here once it is complete. It will be my first Users Group presentation, so hopefully it goes alright, and at least one person learns something new!

Notes for 1Z0-047 Part II

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

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)

NoCOUG SQL Challenge #2

I was happy to read Iggy Fernandez announce that the second installment of the NoCOUG SQL Challenge is coming soon. I was very impressed with the quality of answers that were submitted for the first challenge last year, especially the winners answers which made my answer appear extremly elementary. 🙂

Studying for Certification

I have decided to try to get some Oracle certifications, starting with 1Z0-047 (SQL Expert). My posts for a while will probably just be some notes of things that I find while studying that I didn’t know off the top of my head.

If you have taken this exam and have any advice I would love to hear it.