The select list has column names or expressions involving columns.
SQL expressions are not very different from those of other programming languages.
SELECT TOP 10 POWER(10, phot_g_mean_mag) AS rel_flux, SQRT(POWER(ra_error, 2)+POWER(dec_error, 2)) AS errTot FROM gaia.dr3lite
The value literals are as usual:
The usual arithmetic, comparison, and logical operators work as expected:
Here’s a list of ADQL functions:
Note that all names in SQL (column names, table names, etc) are case-insensitive (i.e., VAR and var denote the same thing). You can force case-sensitivity (and use SQL reserved words as identifiers) by putting the identifiers in double quotes (that’s called delimited identifiers). Don’t do that if you can help it, since the full rules for how delimited identifiers interact with normal ones are difficult and confusing.
Also note how I used AS to rename a column. You can use the names assigned in this way in, e.g., ORDER BY:
SELECT TOP 10 gaia_edr3_id, SQRT(POWER(pmra, 2)+POWER(pmra, 2)) AS pmTot FROM cns5.main ORDER BY pmTot
Don’t do that on large catalogues without a very good reason – even with the TOP 10, the database will have to compute pmTots for all items in the table and then sort by that, which will take a long time with, for instance, Gaia DR3’s 1.8 billion rows.
To select all columns, use *
SELECT TOP 10 * FROM rave.main
In general, try to only select the columns you actually need; there is no point retrieving a hundered columns when five would do, and carrying all these superfluous columns around has a very real cost in terms of ease-of-use and resources (in particular when it comes to uploads).
TOPCAT makes picking the columns really easy: Control-click the columns you want in the Columns tab, and then use the “Cols” button above the the query input to insert their names.
Use COUNT(*) to figure out how many items there are.
SELECT count(*) AS numEntries FROM rave.main
COUNT is what’s called an aggregate function in SQL: A function taking a set of values and returning a single value. The other aggregate functions in ADQL are (all these take an expression as argument; count is special with its asterisk):
Note that on most services, COUNT(*) is an expensive operation. If you just want to get an estimate of how many rows a table has, on many services a peek into the Table pane in TOPCAT when you have selected a table will tell you.
(1)
Select the absolute magnitude and the common name for the 20 stars with the greatest visual magnitude in the table fk6.part1 (in case you don’t remember: The absolute magnitude is M= 5 + 5log π+ m with the parallax in arcsec π and the apparent magnitude m (check the units!).