Examples based on the 6df catalogue

The 6df (six-degree field galaxy redshift survey) catalogue contains a number of separate tables of supporting data.

Joining 3 tables using the targetid parameter

• Crossmatch the TARGET, SPECTRA and TWOMASS tables using parameters extracted from each of three tables.

SELECT top 100
  ta.TARGETNAME, ta.BMAG, ta.RMAG, ta.BMAG-ta.RMAG AS colour, sp.Z, tw.K
FROM target as ta, spectra as sp, twomass as tw
WHERE (ta.TARGETID = sp.TARGETID )
AND (ta.TARGETID = tw.TARGETID )
AND (ta.BMAG - ta.RMAG > 1.2)
AND (sp.QUALITY >= 3)
AND (tw.K > 12)

Exclude unwanted objects

• Find objects without the word 'star' in the redshift comment
• Return properties from two tables

SELECT top 100 s.specid,s.targetname,s.z_comm,t.progid
FROM spectra as s , target as t
WHERE s.targetid = t.targetid and s.z_comm NOT like '%star%'

Create a new column and sort the output

• Find objects that appear in the 2MASS input catalogue with various properties and sort the output selection:
  • J magnitude < 12, a J-K colour > 1.5
  • where the quality of the measured redshift is >= 3.
• Sort output such that brightest K mag appears first.
• Parameters to be returned are 2MASS RA and DEC, J, K, J-K colour and the observed redshift (z) and quality.
  • Use the SQL AS keyword to return the J-K colour as a new parameter called colour

SELECT t.ra,t.dec, t.J,t.K,t.J-t.K as colour, s.z,s.quality,s.specid
FROM twomass as t, spectra as s
WHERE t.targetid = s.targetid and t.J < 12 and t.J-t.K > 1.5 and s.quality >=3
ORDER BY t.K asc

Find an extreme object

• Find the object with the highest good quality redshift

SELECT s.obsra, s.obsdec, s.z, s.specid, s.targetname from spectra as s
WHERE s.z in (SELECT max(b.z) FROM spectra as b WHERE b.quality >= 3 )