Your first SELECT

Every query you'll ever write is a variation on four words: select these columns, from this table, where this is true, order by that. This page uses the first, second and fourth; the next page adds WHERE.

You write SELECT first, but Oracle reads FROM first: it has to know where the rows come from before it can pick columns. Keep that order in mind and half of SQL's quirks make sense.

The test case

List the Tampa Tides squad: each player's name, role and date of birth, and their age (in whole years) on the first day of the 2026 season, April 4. Oldest first.

Expected result

Way 1: MONTHS_BETWEEN

select player_name,
       role,
       to_char(born, 'YYYY-MM-DD')                               as born,
       trunc(months_between(date '2026-04-04', born) / 12)       as age_at_2026_start
from   cr_players
where  team_id = 1
order  by born, player_name;

• Columns are separated by commas. A column can be a plain column (role), a function of one (to_char(born, ...)), or any expression.
• AS age_at_2026_start names the output column (an alias). AS is optional; using it makes the query easier to read.
• DATE '2026-04-04' is a date literal: always year-month-day, whatever the session settings.
• MONTHS_BETWEEN(later, earlier) counts months, with a fraction for part-months. Divide by 12 and cut off the decimals with TRUNC, and you have completed years.
• ORDER BY born, player_name: oldest first, and the name breaks any tie.

Way 2: compare month-day

select player_name,
       role,
       to_char(born, 'YYYY-MM-DD') as born,
       2026 - extract(year from born)
            - case when to_char(born, 'MMDD') > '0404' then 1 else 0 end as age_at_2026_start
from   cr_players
where  team_id = 1
order  by born, player_name;

How you'd work out an age on paper: the difference in years, minus one if the birthday hasn't happened yet that year. TO_CHAR(born, 'MMDD') gives text like '0509', which compares correctly with '0404'.

Devon Price was born on April 3, 2001, so he turned 25 the day before the season started. Both ways agree on that; a naive 2026 - extract(year from born) would also say 25 for him, but would make Tyler Vance (born May 9) 27 instead of 26.

19c and 26ai

The same on both. On 26ai you can test a calculation with no table at all: select trunc(months_between(date '2026-04-04', date '2001-04-03') / 12); (page 12.1).

Gotchas

• SELECT * is for exploring, not for code you keep: it breaks when someone adds a column.
• Aliases can't be used in WHERE, because WHERE runs before SELECT (page 12.2 explains why).
• Without ORDER BY the order is not guaranteed. It may look sorted today and not tomorrow.