Job ID | Phase | Owner | Public | Description | Quote (sec.) | Available until | Start time | Exec Time | Query | Error | Output format | End time | Language | Catalogue | Rows found | Maximum rows | Run | Actions |
---|

Query syntax **OK**.

The Astronomical Data Query Language (ADQL) is the language used by the International Virtual Observatory Alliance (IVOA) to represent astronomy queries posted to VO services.

ADQL is based on the Structured Query Language (SQL), especially on SQL 92. Only *SELECT* sentences are allowed,
to express the maximum rows to return SQL 92 specifies the keyword *TOP*
followed by a number after the initial SELECT keyword.

ADQL predefines a list of mathematics and geometric functions. Geometric functions that accepts figures as a string uses the string format specified at the TAP protocol
in section '6 Use of STC-S in TAP (informative)'. For the coordinate system only **Spherical** coordinates in the frames
**FK5**, **GALACTIC**, and **ICRS** are supported. Note: unlike ADQL specifies, empty string assumes ICRS as the default coordinate system.

Obtaining all records and all the columns from the table *qabc03*:

SELECT * FROM quiescentgalaxies.qabc03

Obtaining all sources identifier from the table *qabc03*:

SELECT galaxy_id FROM quiescentgalaxies.qabc03

Aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable.

Renaming a column as *sources_number*:

SELECT COUNT(galaxy_id) AS sources_number FROM quiescentgalaxies.qabc03

Use the **TOP** instruction to limit the number of records to display.

Obtaining the first 100 records of the query in the table *qabc03*:

SELECT TOP 100 * FROM quiescentgalaxies.qabc03

Use the **ORDER BY** instruction to sort records in ascending (ASC) or descending (DESC).

Obtaining the 100 images with higher Stellar mass from the table *qabc03*:

SELECT TOP 100 * FROM quiescentgalaxies.qabc03 ORDER BY mstar ASC

Use the **OFFSET** instruction to specify the number of records to skip before starting to return records from the query.

If the total number of records is less than the value specified by the OFFSET clause, then the result set is empty. If a query contains both an ORDER BY clause and an OFFSET clause, then the ORDER BY is applied before the specified number of records are dropped by the OFFSET clause.

This instruction can be very useful in big queries where the number of expected output records is bigger than the TAP maximum records limit or the limit indicated in the TOP clause. In that case, you can use this instruction to obtain your data in chucks by executing your query n times only changing the value of OFFSET until you get a result with a number of records less than the limit and then working with all the outputs files obtained.

Obtaining the next 100 records of the query in the table *qabc03*:

SELECT TOP 100 * FROM quiescentgalaxies.qabc03 OFFSET 100

As an extension to ADQL it is possible to use square brackets to access array elements of columns defined as arrays. First element of the array is at position 1. This allow, for example, store together all the fluxes or magnitudes measured in different filters for a position in the sky (see also section *6: Using enumerations*).

Is it possible also to access to a subset of an array (array slice) using square brackes and indicating inside the lower-bound and upper-bound (both included) separated by a colon.

Basic math: **+ - * /**, can be done between numerical arrays, and between numerical arrays and scalars.

There are special functions to deal with arrays (see *Functions* section) and, among them, there is a special one `arr_map(expr_over_x, arr)`

that computes a new array by binding each element of *arr* to *x* in
turn and then computing *expr_over_x* (ADQL *numeric_value_expression* which can use that can use column references as usual, except the
reserved name *x*).

An extension to ADQL is the definition of *Enumerations*, it is a form of assign a set of names to a range of numbers. Writing a enumeration in a query is done by
writing the enumeration name, the symbol *::* and the item name of the enumeration that we want to use.
That is equivalent to write the associated number of the item and it is useful when accessing an concrete element in a array column or using it as a constant.

Use constraints to filter records according to logical expressions. In an ADQL query, the constraints are gathered in the WHERE part of the query.

Use the different operators **=** (equal), **<>** (not equal), **<** (less than), **>** (greater than), **<=** (less and equal than) and **>=** (greater and equal than) to compare values.

Obtaining the objects whose Stellar mass is less than 1.5 solMass:

SELECT * FROM quiescentgalaxies.qabc03 WHERE mstar < 1.5

Use the **IN** operator to determine whether a value is within a given set. You can reverse the operation of the IN operator by adding to the **NOT** operator.

Obtaining the objects with the identifiers 81421100765, 81421101074 and 81421101630:

SELECT * FROM quiescentgalaxies.qabc03 WHERE galaxy_id IN (81421100765, 81421101074, 81421101630)

Use the **BETWEEN** operator to determine whether a value is within a given interval.

Obtaining the objects whose Stellar mass is between 1.5 and 1.6 solMass:

SELECT * FROM quiescentgalaxies.qabc03 WHERE mstar BETWEEN 1.5 AND 1.6

Use the **LIKE** operator to execute partial comparisons. The wild card % replaces any string of characters, including the empty string. The underscore replaces exactly one character. You can reverse the operation of the LIKE operator by adding to the **NOT** operator.
Use **ILIKE** for case insensitive comparations.

Use **AND** operator to obtain records if both the first condition AND the second condition are true.

Obtaining the objects whose Stellar mass is less than 16 1.6 solMass and photoredshift less than 0.3:

SELECT * FROM quiescentgalaxies.qabc03 WHERE mstar < 1.6 AND z < 0.3

Use **OR** operator to obtain records if the first condition is true, the second condition is true or both are true.

Obtaining the objects whose Stellar mass is less than 16 1.6 solMass or photoredshift is less than 0.3:

SELECT * FROM quiescentgalaxies.qabc03 WHERE mstar < 1.6 OR z < 0.3

Use mathematical operations: **+**, **-**, ***** and **/** to compute columns. Obtaining the objects with a difference of more than 1 magnitude between filters rSDSS and gSDSS in magnitude auto:

SQL aggregate functions return a single value, calculated from values in a column.

Use **AVG(column_name)** function to obtain the average value in a column for a group of data lines. This function applies only to numeric data.

Obtaining the average value of the Stellar mass of all the objects:

SELECT AVG(mstar) FROM quiescentgalaxies.qabc03

Use **COUNT(column_name)** function to obtain a count of rows from a reference column values if it is not NULL.

Obtaining the total number of sources:

SELECT COUNT(galaxy_id) FROM quiescentgalaxies.qabc03

Use **SUM(column_name)** function to obtain the sum of values in a column for a group of data lines. This function applies only to numeric data.

Use **MAX(column_name)** function to obtain the largest value of a column for a group of data lines.

Obtaining the object with a largest stellar mass:

SELECT MAX(mstar) FROM quiescentgalaxies.qabc03

Use **MIN(column_name)** function to obtain the smallest value of a column for a group of data lines.

Obtaining the object with a smallest stellar mass:

SELECT MIN(mstar) FROM quiescentgalaxies.qabc03

Use **GROUP BY** statement in conjunction with the aggregate functions to group the result-set by one or more columns.

Obtaining the number of objects per stellar mass (rounding the stellar mass):

SELECT ROUND(mstar, 0) AS MAG, COUNT(*) FROM quiescentgalaxies.qabc03 GROUP BY mstar

Obtaining all objects using both methods qabc03 and qabasti:

SELECT qabc03.galaxy_id as galaxy_id, qabc03.*, qabasti.* FROM quiescentgalaxies.qabc03 AS qabc03, quiescentgalaxies.qabasti AS qabasti WHERE qabc03.galaxy_id = qabasti.galaxy_id

You can combine the results of two queries with the same number of columns and compatible types for each column. The operators are
**UNION**, **EXCEPT** and **INTERSECT**.

The UNION operator combines the results of two queries, accepting rows from both the first and second set of results, removing duplicate rows unless UNION ALL is used.

Obtaining all objects in both methods qabc03 and qabasti:

SELECT GALAXY_ID, ALPHA_J2000, DELTA_J2000 FROM quiescentgalaxies.qabc03 UNION SELECT GALAXY_ID, ALPHA_J2000, DELTA_J2000 FROM quiescentgalaxies.qabasti

The EXCEPT operator combines the results of two queries, accepting rows that are in the first set of results but are not in the second, removing duplicate rows unless EXCEPT ALL is used.

The INTERSECT operator combines the results of two queries, accepting rows that are strictly in both the first and second set of results, removing duplicate rows unless INTERSECT ALL is used.

The **WITH** operator creates a temporary named result set that can be referred to elsewhere in the main query. Using a common table expression can make complex queries easier to understand by factoring subqueries out of the main SQL statement.

The CAST operator returns the value of the first argument converted into the datatype specified by the second argument.
The allowed conversion types are: INTEGER, SMALLINT, BIGINT, REAL, DOUBLE PRECISION, CHAR(number), VARCHAR(number) and TIMESTAMP.
Basic usage: `CAST('2012-01-15 9:00' AS TIMESTAMP)`

Other functions like arithmetical functions, trigonometric functions, geometrical functions, conversion functions, ... are also available. Please, see *Functions* section to know more
about them.

*Note:* at the moment the geometric functions are partially supported, 'contains', 'distance' and 'area' are only implemented for basic figures.
*This is a work in progress!*.

The following bitwise operators are available to be used with integer numbers:

**&**Bit 'and'. Example:`flag & 2048`

**|**Bit 'or'. Example:`flag | 2048`

**#**Bit 'xor'. Example:`flag # 2048`

**~**Bit 'not'. Example:`~flag`

Tables: Most used

Please, click on the table, you want to obtain more information.

Tables: All scientific

Please, click on the table, you want to obtain more information.

Functions

- ABS
- ACOS
- AREA
- arr_agg
- ASIN
- ATAN
- ATAN2
- BOX
- CEILING
- CIRCLE
- CONTAINS
- COORD1
- COORD2
- COORDSYS
- COS
- DEGREES
- DISTANCE
- EXP
- FLOOR
- INTERSECTS
- ivo_bit_and_agg
- ivo_bit_or_agg
- ivo_corr
- ivo_covar_samp
- ivo_dms_to_degrees
- ivo_hms_to_degrees
- ivo_REGR_INTERCEPT
- ivo_REGR_R2
- ivo_REGR_SLOPE
- ivo_STDDEV
- ivo_string_agg
- ivo_to_jd
- ivo_to_mjd
- ivo_VARIANCE
- LOG
- LOG10
- LOWER
- MOD
- PI
- POINT
- POLYGON
- POWER
- RADIANS
- RAND
- ROUND
- SIN
- SQRT
- TAN
- TRUNCATE
- UPPER

Please, click on the function, you want to obtain more information.

Enumerations

Please, click on the enumeration, you want to obtain more information.