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 tablewill 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