8. SELECT: what?

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
  source_id,
  SQRT(POWER(pmdec_error,2)+POWER(pmra_error,2)) AS pm_errTot
FROM gaiadr1.tgas_source

The value literals are as usual:

• Only decimal integers are supported (no hex or such)
• Floating point values are written like 4.5e-8
• Strings use single quotes (’abc’). Double quotes mean something completely different for ADQL (they are ” delimited identifiers“).

The usual arithmetic, comparison, and logical operators work as expected:

• +, -, *, /; as in C, there is no power operator in ADQL. Use the POWER function instead.
• = (not ==), <, >, <=, >=
• AND, OR, NOT
• String concatenation is done using the || operator. Strings also support LIKE that supports patterns. % is “zero or more arbitrary characters”, _ “exactly one arbitrary character” (like * and ? in shell patterns).

Here’s a list of ADQL functions:

• Trigonometric functions, arguments/results in rad: ACOS, ASIN, ATAN, ATAN2, COS, SIN, TAN; atan2(y,x) returns the inverse tangent in the right quadrant and thus avoids the degeneracy of atan(y∕x).
• Exponentiation and logarithms: EXP, LOG (natural logarithm), LOG10
• Truncating and rounding: FLOOR(x) (largest integer smaller than x), CEILING(x) (smallest integer larger than x), ROUND(x) (commercial rounding to the next integer), ROUND(x, n) (like the one-argument round, but round to n decimal places), TRUNCATE(x), TRUNCATE(x,n) (like ROUND, but just discard unwanted digits).
• Angle conversion: DEGREES(rads), RADIANS(degs) (turn radians to degrees and vice versa)
• Random numbers: RAND() (return a random number between 0 and 1), RAND(seed) (as without arguments, but seed the the random number generator with an integer)
• Operator-like functions: MOD(x,y) (the remainder of x∕y, i.e., x%y in C), POWER(x,y)
• Power shortcuts: SQRT(x) (shortcut for POWER(x, 0.5)), SQUARE(x) (shortcut for POWER(x, 2))
• Misc: ABS(x) (absolute value), PI()

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
  source_id,
  SQRT(POWER(pmdec_error,2)+POWER(pmra_error,2)) AS pm_errTot
FROM gaiadr1.tgas_source
ORDER BY pm_errTot

To select all columns, use *

SELECT TOP 10 * FROM gaiadr1.tgas_source

Use COUNT(*) to figure out how many items there are.

SELECT count(*) AS numEntries
FROM gaiadr1.tgas_source

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):

• MAX, MIN
• SUM
• AVG (arithmetic mean)