Start here

This guide covers Oracle SQL from the first SELECT to MATCH_RECOGNIZE, for Oracle Database 19c and Oracle AI Database 26ai. Each page answers one real question. It gives you the exact result to expect, then solves the question two or three different ways and tells you when to pick each one.

You don't need to know anything about cricket. The next page explains the game in ten minutes, and the page after that shows how the data was made. No EMP, no DEPT, no purchase orders: just bats, balls and the occasional hat-trick.

How every page works

Every query on every page has been run on both versions, and every way of answering a test case returns exactly the rows in its expected result. If you find one that doesn't, tell us at support@kathuko.com.

Badges

• 19c+ works on Oracle Database 19c and everything newer, including 26ai.
• 26ai needs Oracle AI Database 26ai. The page always shows the 19c way next to it.

Not sure which one you have? Run this:

select banner_full from v$version;

If you get "table or view does not exist", try select version_full from product_component_version;. On 26ai the version number starts with 23 (for example 23.26.2). That isn't a mistake: 26ai is the release name of the version 23 code line.

Where to run SQL

Any of these works. Pick one:

Install the cricket data

1. Download cricket_install.sql (link in the downloads on this page).
2. Run it as a script:
   • apex.oracle.com: SQL Workshop > SQL Scripts > Upload, then Run.
   • Database Actions or SQL Developer: open it and press Run Script (F5).
   • SQLcl: @cricket_install.sql
3. The last query in the script counts the rows. You should see this:

select 'cr_venues' tab, count(*) n from cr_venues union all
select 'cr_teams', count(*) from cr_teams union all
select 'cr_players', count(*) from cr_players union all
select 'cr_matches', count(*) from cr_matches union all
select 'cr_deliveries', count(*) from cr_deliveries;

Dates in the expected results are shown as YYYY-MM-DD. To see them the same way, run this once per session:

alter session set nls_date_format = 'YYYY-MM-DD';

The script is safe to run again: it drops the CR_ tables first. To remove everything, run cricket_drop.sql.

One league, for now

Every page uses the cricket league, so by Part 14 you'll know its players better than some of their coaches do. A few pages build small tables of their own (the league office org chart in Part 13, scratch copies in Parts 9 and 10); each page's setup creates them and says so.

Coming next: the same SQL, other worlds. New lessons will teach many of the same statements again on four more datasets, each where it fits best:

• Airline flight operations: delays, connections, every route from A to B, and property graphs for the route network.
• Stock and crypto prices: V and W shapes and double bottoms with MATCH_RECOGNIZE, moving averages.
• SaaS subscriptions and billing: churn, upgrades, running monthly revenue, grouping app logins into sessions, upserts with MERGE.
• Smart-meter readings: gaps in the readings, time buckets, duplicates and outliers.

Each will install the same way as the cricket data, with its own table prefix (FL_, ST_, SB_, MT_), so they never collide with each other or with your own tables.