SQL Tutorial

Important announcements

The database for AKARI CAS was restructured into multiple databases. The 'cas' database is available as a new database instead of 'DR1'.
Due to the reconfiguration of the database, the name of some tables and views had changed. If you keep the previously created SQL and want to use again, please do substitution of the name. And also, the usage of some functions had changed.
The correspondence between the new name and the old name is written in Tables and Views. In Functions, please specify the catalogue names ('akari_fis_bsc_1' or 'akari_irc_psc_1') instead of the instrument ('Fis' or 'Irc'). We appreciate in your inconvenience.

The summary is below:

Index:
IntroductionSearch Tools handling user SQL statementsA Simple QueryTables and ViewsMore SamplesOrder ByAKARI-CAS built-in Functions


Introduction

The AKARI Catalogue Archive Server (AKARI-CAS) provides interface to access AKARI FIS and IRC all-sky survey catalog data. The data are stored in the RDBMS (Relational Data Base Management System) dedicated for AKARI-CAS. In CAS tools, you can use SQL (Structured Query Language) statements to request necessary information from the database. This process is called "SQL query", and it enables you to effectively search data with complex criteria. To use SQL query, you must prepare an SQL statement to give the RDBMS your own search criteria or search keys. Once the RDBMS receives the SQL statement, the RDBMS searches the records stored in it, and returns the information which meets your criteria. We prepared this tutorial page so that you can write your own SQL statements.


Search Tools handling user SQL statements

When you want to access AKARI-CAS using SQL statements, you can use the following Search Tools;

When you get into SQL Search, you can write your own SQL statements in the textarea. And, if you want to use Cross Identifications , you may modify each default SQL statement in the textarea.


Simple Query

Simple Query may include the SELECT block, the FROM block, and the WHERE block. The SELECT block is used to select columns of the database tables. An example of Simple Query is shown below;

-- Example of A Simple Query 
 
SELECT objID, objName, ra, dec
FROM akari_fis_bsc_1
WHERE fQual_90 = 3 AND fQual_140 = 3 
LIMIT 10

The SELECT block is used to specify the column names in a table. In the example shown below, objId, objName, ra, and dec are the column names of a table in the AKARI-CAS RDBMS. You can use "*" character as a wild card indicating all the column names in the table.

-- Example of the SELECT Block 

SELECT objID, objName, ra, dec

-- Example of a Wild Card Usage in the SELECT Block 

SELECT *

The FROM block refers to a table name for the SQL search. You must write the table name after "FROM". A usage of the FROM block is shown in the example below. In that example, akari_fis_bsc_1 is the specified table name for the SQL query;

-- Example of the FROM Block

FROM akari_fis_bsc_1

You may write your own search conditions in the WHERE block. For example, you can set some parameter constraints after "WHERE", as your own search conditions. The equality (e.g. fQual_90 = 3) and the inequality (e.g. 270 < ra) can be written in the WHERE block. To specify multiple search conditions, each search condition should be connected with "AND" or "OR".

-- Example of the WHERE Block 

WHERE fQual_90 = 3 AND fQual_140 = 3 

If you want to fetch only the first n rows, append "LIMIT n" to your SQL statement, otherwise you will get all of the result.
-- Example of LIMIT : fetch first 10 rows

LIMIT 10

You can define any temporary names for tables, views, or function references. That temporary name is called alias. The alias must be created in the FROM block. You must write "FROM reference_(table, view, or function name) AS alias" or "FROM reference_(table, view, or function name) alias" to create an alias. Once an alias is created, each alias can be connected with column name by ".", and used in the SQL statement.

The following example, the alias o means akari_fis_bsc_1.

-- Example of Alias : Table Alias

SELECT o.objID, o.objName, o.ra, o.dec
FROM akari_fis_bsc_1 o
WHERE o.fQual_90 = 3 AND o.fQual_140 = 3 
LIMIT 10

An alias is also assigned to a selected column reference. That alias must be defined in the SELECT block. You can write like "SELECT a AS value, b + c AS sum FROM ..." to create a column alias. In that SELECT block, value is the alias of the selected column reference a, and sum is the alias of the selected column reference b + c. In the following example, The lon and lat are actual names of the columns which contain the outputs of ra and dec, respectively.

-- Example of Alias : Column Alias

SELECT ra as lon, dec as lat
FROM fis_digest
LIMIT 10


Tables and Views

The AKARI-CAS provides several tables and Views for users. Tables include akari_fis_bsc_1 and akari_irc_psc_1. A comprehensive FIS/IRC source catalog records are stored in them. You can obtain FIS BSC 1.0 or IRC PSC 1.0 data from them, using SQL statements.

Views are virtual tables with column data selected from the original Tables. The AKARI-CAS has two Views, called akari_fis_bsc_1_digest and akari_irc_psc_1_digest. akari_fis_bsc_1_digest is created from the original table akari_fis_bsc_1. akari_ircpsc__1_digest is created from the original table akari_irc_psc_1. akari_fis_bsc_1_digest has column data selected as the representative columns from akari_fis_bsc_1. akari_irc_psc_1_digest also has column data selected as the representative columns from akari_irc_psc_1. So, you can understand the organization from AKARI FIS BSC 1.0 or AKARI IRC PSC 1.0 catalog records easily, when you see akari_fis_bsc_1_digest or akari_irc_psc_1_digest. You can use CAS tools through the procedures described as follows in order to display the results from View instead of Table:

On Radial Search and Rectangular Search, click the radio button digest instead of all to use a View akari_fis_bsc_1_digest or akari_irc_psc_1_digest.

You can find three types of SQL calles, on SQL Search. They are Basic Calls, Radial Search, and Rectangular Search. If you click the All button in Basic Calls, the Table akari_fis_bsc_1 (or akari_irc_psc_1) is set to FROM block:

SELECT *
FROM akari_fis_bsc_1
WHERE
    fQual_65 = 3 AND fQual_90 = 3 AND
    fQual_140 = 3 AND fQual_160 = 3
LIMIT 10

If you click the Digest button in Basic Calls, the View akari_fis_bsc_1_digest (or akari_irc_psc_1_digest) is set to FROM block:

SELECT *
FROM akari_fis_bsc_1_digest
WHERE
    fQual_65 = 3 AND fQual_90 = 3 AND
    fQual_140 = 3 AND fQual_160 = 3
LIMIT 10

Thus, you can set View name or Table name after FROM keyword like above examples.


More Samples

More samples of the SQL statement are presented below. They give you some instructions to write an SQL statement.

This first sample shows how to use the equality and inequality signs in an SQL statement. This SQL statement is a query to return the first 10 rows of FIS BSC 1.0 using a flux cut. This sample also includes some equality and inequality signs such as ">" and "<=".

SELECT *
FROM akari_fis_bsc_1_digest
WHERE
    19.5 < flux_90 AND flux_90 <= 20.0
LIMIT 10

In the second sample, you can find how to use the logical operators in an SQL statement. This SQL statement is a query to return the first 10 rows of FIS BSC 1.0 using 'AND' and 'OR' logical operators in the WHERE block.

SELECT objID,objName,ra,dec,
       flux_65,flux_90,flux_140,flux_160,
       fQual_65,fQual_90,fQual_140,fQual_160
FROM akari_fis_bsc_1
WHERE
    ( 19.5 < flux_90 AND flux_90 <= 20.0 ) AND
    ( fQual_90 = 3 OR fQual_140 = 3 )
LIMIT 10

To obtain simple statistical result of a table, Aggregate Functions are prepared. This example gets minimum, maximum, average values of flux_90 column and number of rows in akari_fis_bsc_1 table:

SELECT min(flux_90), max(flux_90), avg(flux_90), count(*)
FROM akari_fis_bsc_1

You can use Mathematical Operators in your SQL statements. Next example uses "%" and "&" mathematical operators. Each operator gives remainder or binary AND. In addition, "objID % 2" can be replaced with "mod(objID, 2)". The mod() function is one of the built-in functions of SQL. SQL has a number of functions similar to C language. See also Mathematical Function and Trigonometric Function pages, for further reference.

SELECT *
FROM akari_fis_bsc_1
WHERE
    objID % 2 = 0 AND
    (flags_90 & INT4(b'00000001')) != 0
LIMIT 10

The final sample presents you an instructive SQL statement how to use AKARI-CAS built-in functions. In the sample below, fLonStr2Deg() is one of general functions in the AKARI-CAS, and fGetNearbyObjCel() is one of AKARI-specific functions. This SQL statement is for a query for Radial Search of FIS BSC 1.0 using equatorial J2000 coordinate, with the conditions (ra, dec) = ('12:02:00.00', '+02:30:00.0') and radius = 300 arcmins.

SELECT o.*
FROM fGetNearbyObjCel('akari_fis_bsc_1', 'j2000',
      fLonStr2Deg('12:02:00.00'),
      fLatStr2Deg('+02:30:00.0'),
      300.0
     ) n,
     akari_fis_bsc_1_digest o
WHERE n.objID = o.objID
ORDER BY n.distance
LIMIT 10


Order By

The ORDER BY block can be written in an SQL statement, when you need a table data with a sorted column. You must write a column name in a table after the "ORDER BY" in order to obtain the sorted table. In the following example, the requested records will be sorted by flux_90 values.

-- Example of ORDER BY
 
SELECT objID,objName,ra,dec,
       flux_65,flux_90,flux_140,flux_160
FROM akari_fis_bsc_1
WHERE
    19.5 < flux_90 AND flux_90 <= 20.0
ORDER BY flux_90
 

If you need random selection of objects, use ORDER BY random() with LIMIT keyword. Following example randomly selects 20 objects with a flux cut.

-- Example of random selection
 
SELECT objID,objName,ra,dec,
       flux_65,flux_90,flux_140,flux_160,
       fQual_65,fQual_90,fQual_140,fQual_160
FROM akari_fis_bsc_1
WHERE
    18.0 < flux_90 AND flux_90 <= 20.0
ORDER BY random()
LIMIT 20
 

AKARI-CAS built-in Functions

You can use a variety of AKARI-CAS built-in Functions in your SQL statement. Two categories of the functions, the general functions and the AKARI-specific functions, can be used in the SQL statement. When you need converted values (such as converted angles, or converted units), you can use general functions. An example SQL statement using a general function is shown below;

-- Example of the General Function 

-- function fDeg2LonStr() and fDeg2LatStr() convert
-- a numerical value in degree into string of `xx:xx:xx.xx' style.

SELECT ra, dec, fDeg2LonStr(ra) as ra_hms, fDeg2LatStr(dec) as dec_dms
FROM akari_fis_bsc_1_digest
LIMIT 10
 

When you need a query under some constraints, you should use AKARI-specific functions in your SQL statement. An example SQL statement is shown below;

-- Example of the AKARI-specific functions 

-- Given a point (@ra,@dec) of Ecliptic coordinate,         
-- this function fGetNearbyObjCel returns a table holding a record of
-- FIS BSC 1.0 or IRC PSC 1.0 object nearest from the point within  
-- @r arcmins of the point. 
 
SELECT o.*, n.distance 
FROM fGetNearbyObjCel('akari_fis_bsc_1', 'ecl', 195.5, 2.5, 300) n, akari_fis_bsc_1_digest o 
WHERE n.objID = o.objID

Functions described above are all listed on Functions page, where you can obtain detailed information about the general functions and the AKARI-specific functions.

Last Modified: 09 January 2019