Oracle SQL, tested
Oracle SQL for 19c and 26ai, from the first SELECT to MATCH_RECOGNIZE and execution plans.
Every page asks one real question, shows the exact rows the answer must return, and solves it two or three ways.
Every query is run on both versions before it's published.
Free89 pages148 tested questions19c and 26aiNo EMP or DEPT
Contents
Pages marked 19c+ run on Oracle Database 19c and everything newer. 26ai only needs Oracle AI Database 26ai, and the page always shows the 19c way too.
1SELECT basics8 pages
2Joins6 pages
3Aggregation5 pages
4Subqueries and WITH6 pages
5Analytic functions9 pages
- 5.0 Window functions: totals without losing the rows
- 5.1 ROW_NUMBER, RANK and DENSE_RANK
- 5.2 Running totals, and the frame you didn't ask for
- 5.3 LAG and LEAD: the row before, the row after
- 5.4 FIRST_VALUE, LAST_VALUE, and the LAST_VALUE trap
- 5.5 NTILE, PERCENT_RANK and CUME_DIST: where do you stand?
- 5.6 Moving averages: ROWS for the last n, RANGE for the last n days
- 5.7 Top n per group
- 5.8 Practice: three window-function problems
6Row pattern matching (MATCH_RECOGNIZE)12 pages
- 6.0 MATCH_RECOGNIZE: regular expressions for rows
- 6.1 Three or more sixes in a row
- 6.2 The longest spells of dot balls
- 6.3 Every hat-trick, ball by ball
- 6.4 Quantifiers: how many rows a variable can match
- 6.5 Conditions that look back: PREV, FIRST and running totals
- 6.6 AFTER MATCH SKIP: where the next match starts
- 6.7 Either order: alternation, PERMUTE, anchors and SUBSET
- 6.8 Partnerships: cutting a sequence into sessions
- 6.9 Showing the rows around a match: WITH UNMATCHED ROWS and {- -}
- 6.10 Running vs final, and the gotchas in one place
- 6.11 Practice: three pattern problems
7PIVOT and UNPIVOT4 pages
8Top-N and paging4 pages
9Changing data (DML)6 pages
10Tables and constraints (DDL)5 pages
11JSON and XML4 pages
12New in 26ai10 pages
- 12.0 What's new since 19c
- 12.1 SELECT without FROM, and real booleans
- 12.2 GROUP BY an alias, and QUALIFY
- 12.3 VALUES: rows made on the spot
- 12.4 Window functions, upgraded: WINDOW, GROUPS, EXCLUDE
- 12.5 MINUS ALL and INTERSECT ALL
- 12.6 Joins in UPDATE and DELETE, and RETURNING OLD and NEW
- 12.7 IF [NOT] EXISTS, and annotations
- 12.8 Dates: TIME_BUCKET, CEIL and FLOOR, and adding up intervals
- 12.9 VECTOR: who bats like Karthik Raman?
13Classic puzzles4 pages
Real questions, not EMP and DEPT
The examples run on small, realistic datasets you install in a minute. The first is the Coastal Cricket League: six teams, two seasons, every ball recorded. Never watched cricket? Cricket in 10 minutes has you covered.