Oracle SQL in Practice: Joins, Subqueries, Hierarchies & Analytics
Read and reason through real Oracle SQL on a fictional company schema: ANSI and (+) joins, subqueries, CONNECT BY hierarchies, recursive WITH, ROLLUP and CUBE, analytic functions and MERGE, with every answer checked as you go.
- One-time payment
- Instant access right after payment
- Secure checkout with Stripe
About this course
In this intermediate course you work through 22 hands-on labs on a fictional company schema and predict exactly what real Oracle SQL returns. You join tables in ANSI and classic (+) syntax, spot Cartesian products and lost rows, and evaluate single-row, multi-row and correlated subqueries, EXISTS and the NOT IN NULL trap. You walk org charts with START WITH, CONNECT BY, LEVEL, SYS_CONNECT_BY_PATH and recursive WITH, build subtotals with ROLLUP, CUBE and GROUPING SETS, rank and compare rows with analytic functions, LAG and window frames, and predict the effect of a MERGE. Every answer is checked instantly, the first lesson is free, and a final exam leads to your certificate.
Lessons
- ANSI Joins in Oracle: JOIN … ON, USING and Lost RowsFree
- Old-Style Comma Joins and the Cartesian Product Trap Locked
- Non-Equi Joins: Matching Rows to Ranges with BETWEEN Locked
- Outer Joins: LEFT, RIGHT, FULL and Oracle's (+) Operator Locked
- Self Joins: Employees Next to Their Managers Locked
- Single-Row Subqueries: Compare Against a Computed Value Locked
- Multi-Row Subqueries: IN, ANY and ALL Locked
- Correlated Subqueries: Compare Each Row With Its Own Group Locked
- EXISTS, NOT EXISTS and the NOT IN NULL Trap Locked
- Top-N Queries: ROWNUM, Inline Views and FETCH FIRST Locked
- Hierarchical Queries: START WITH, CONNECT BY and LEVEL Locked
- Paths, Leaves and Subtrees: SYS_CONNECT_BY_PATH and CONNECT_BY_ISLEAF Locked
- Walking Up the Tree and Pruning Branches Locked
- Recursive WITH: Anchor, Recursive Member and Running Values Locked
- CONNECT_BY_ROOT, Several Roots and Cycles Locked
- ROLLUP: Subtotals and Grand Totals in One Query Locked
- CUBE and GROUPING SETS: Every Subtotal You Ask For Locked
- Analytic Functions: SUM OVER, PARTITION BY and RATIO_TO_REPORT Locked
- Ranking: RANK, DENSE_RANK, ROW_NUMBER and Top-N per Group Locked
- LAG, LEAD and Window Frames: Changes, Running Totals, Moving Averages Locked
- MERGE: Update and Insert in One Statement Locked
- Final Project: A Department Scorecard With Joins and Analytics Locked