Notes for 1Z0-047 Part II

By | December 20, 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


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


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


  • 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


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

Leave a Reply

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

Turn on pictures to see the captcha *