/*

Example SQL queries from "The WFCAM Science Archive" 
(Hambly et al., 2007, MNRAS)

*/


-- Candidate Galactic cluster members - U3

SELECT zAperMag3-jAperMag3 AS zmj,
       zAperMag3           AS z
FROM   gcsPointSource
WHERE
/* Positional cuts for the Sigma Orionis in the
   Orion Nebula Cluster (in degrees for both): */
       ra  BETWEEN +84.00 AND +85.00 AND
       dec BETWEEN  -2.85 AND  -2.30 AND
/* Magnitude cuts to avoid saturated sources: */
       zAperMag3   > 11.3 AND
       yAperMag3   > 11.5 AND
       jAperMag3   > 11.0 AND
       hAperMag3   > 11.3 AND
       k_1AperMag3 >  9.9 AND
/* Magnitude/colour cuts to select out the member 
   sequence: */
       zAperMag3 < 5.0*(zAperMag3-jAperMag3) + 
                                       10.0 AND
       jAperMag3-hAperMag3 > 0.3
/* UKIDSS DR2 rows returned: 144
   Execution time:           00m 12s  */

-- Counts of objects that are unpaired between epochs - U4

SELECT COUNT(*)
FROM   reliableLasPointSource
WHERE  SQUARE(muRA) + SQUARE(muDec) > 5.0*SQRT(
        SQUARE(muRA*sigMuRA)+SQUARE(muDec*sigMuDec)
       )
/* UKIDSS DR2 rows returned: 1 (count=0)
   Execution time:           01m 00s  */


SELECT COUNT(*)
FROM   reliableLasPointSource
WHERE  
/* Specify detection at one epoch only:  */
       (j_1ObjID > 0 AND j_2ObjID < 0) OR
       (j_1ObjID < 0 AND j_2ObjID > 0)
/* UKIDSS DR2 rows returned: 1 (count=827968)
   Execution time:           01m 06s  */


-- Deep galaxy catalogues - U5

SELECT ra, dec, 
/* De-reddened Petrosian magnitude and 
   fixed aperture colour: */
       jPetroMag-aj as j, 
       (jAperMag3-aj)-(kAperMag3-ak) as jmk
FROM   reliableDxsSource
WHERE  
/* Classification cut to exclude all point 
   sources: */
       mergedClass NOT BETWEEN -1 AND 0 AND
/* Exclude any sources with poorly or undefined 
   Petrosian mags: */
       jPetroMagErr BETWEEN 0 AND 0.2 AND
       kPetroMagErr BETWEEN 0 AND 0.2
/* UKIDSS DR2 rows returned: 142,996
   Execution time:           00m 06s  */


SELECT MIN(ra),MAX(ra),MIN(dec),MAX(dec), (
        (MAX(ra)-MIN(ra))*COS(RADIANS(AVG(dec))))*
        (MAX(dec)-MIN(dec)
       ) AS area
FROM   udsSource
/* UKIDSS DR2 rows returned: 1
   Execution time:           00m 03s  */


SELECT CAST(ROUND(kab*2.0,0) AS INT)/2.0 AS K_AB, 
       LOG10(COUNT(*)/0.89) AS logN
FROM   (
       SELECT (kPetroMag-ak)+1.900 AS kab
       FROM   udsSource
       WHERE  mergedClass NOT BETWEEN -1 AND 0 AND
              jPetroMag > 0.0 AND
              kPetroMag > 0.0 
       ) AS T
GROUP BY CAST(ROUND(kab*2.0,0) AS INT)/2.0
ORDER BY CAST(ROUND(kab*2.0,0) AS INT)/2.0
/* UKIDSS DR2 rows returned: 42
   Execution time:           00m 03s  */


-- Star counts in cells in the UKIDSS GPS - U8

SELECT   CAST(ROUND(l*6.0,0) AS INT)/6.0 AS lon, 
         CAST(ROUND(b*6.0,0) AS INT)/6.0 AS lat,
         COUNT(*)                        AS num
FROM     gpsSource
WHERE    k_1Class BETWEEN -2 AND -1 AND 
         k_1ppErrBits < 256         AND
/* Make a seamless selection (i.e. exclude 
   duplicates) in any overlap regions: */
         (priOrSec=0 OR priOrSec=frameSetID)
/* Bin up in 10 arcmin x 10 arcmin cells: */
GROUP BY CAST(ROUND(l*6.0,0) AS INT)/6.0,
         CAST(ROUND(b*6.0,0) AS INT)/6.0
/* UKIDSS DR2 rows returned: 28,026
   Execution time:           72m 00s  */


-- Optical/infrared selection of QSO candidates - U2

SELECT psfMag_i-psfMag_z    AS imz,
       psfMag_z-j_1AperMag3 AS zmj,
       psfMag_i-yAperMag3   AS imy,
       ymj_1Pnt             AS ymj
FROM   lasPointSource        AS s,
       lasSourceXDR5PhotoObj AS x,
       BestDR5..PhotoObj     AS p
WHERE
/* Join predicates: */
       s.sourceID   = x.masterObjID AND
       x.slaveObjID = p.objID       AND
       x.distanceMins < 1.0/60.0    AND
/* Select only the nearest primary SDSS 
   point source crossmatch: */
       x.distanceMins IN (
         SELECT MIN(distanceMins)
         FROM   lasSourceXDR5PhotoObj 
         WHERE  masterObjID = x.masterObjID AND
                sdssPrimary = 1             AND
                sdssType    = 6
       ) AND
/* Remove any default SDSS mags: */
       psfMag_i > 0.0 AND
/* Colour cuts for high-z QSOs from 
   Hewett et al. (2006) and Venemans
   et al. (2007): */
       psfMag_i-yAperMag3 > 4.0 AND
       ymj_1Pnt           < 0.8 AND
       psfMagErr_u > 0.3 AND
       psfMagErr_g > 0.3 AND
       psfMagErr_r > 0.3 
/* UKIDSS DR2 rows returned: 12
   Execution time:           19m 56s  */


-- A wide-area, shallow galaxy catalogue - U6

SELECT s.ra as alpha, s.dec as delta,
/* Remove Galactic foreground reddening: */
       (petroMag_u-extinction_u)     AS u, 
       (petroMag_g-extinction_g)     AS g, 
       (petroMag_r-extinction_r)     AS r, 
       (petroMag_i-extinction_i)     AS i, 
       (petroMag_z-extinction_z)     AS z,
       (yPetroMag-ay)                AS y, 
       (j_1PetroMag-aj)              AS j,
       (hPetroMag-ah)                AS h,
       (kPetroMag-ak)                AS k,
       z.z                           AS redshift,
       (modelMag_g-extinction_g) -
        (modelMag_r-extinction_r)    AS gmr,
       (yAperMag3-ay)-(kAperMag3-ak) AS ymk,
       (modelMag_u-extinction_u) - 
        (modelMag_g-extinction_g)    AS umg,       
/* Einstein-de Sitter cosmology distance modulus
   (note no K-correction, no evolution correction, 
    and no internal extinction):  */
       (kPetroMag-ak) - 25 - 5*(
        LOG10(2*2.998e5*(1+z.z-SQRT(1+z.z))/75)
       ) AS M_K
FROM   lasExtendedSource AS s, 
       lasSourceXDR5PhotoObj AS x, 
       BestDR5..PhotoObj AS p, 
       BestDR5..SpecObj AS z
WHERE
/* Join criteria:   */
       z.specObjID=p.specObjID    AND
       s.sourceID = x.masterObjID AND
       p.objID = x.slaveObjID     AND
       x.distanceMins IN (
       SELECT MIN(distanceMins)
       FROM   lasSourceXDR5PhotoObj
       WHERE  masterObjID = x.masterObjID AND
              distanceMins < 2.0/60.0
       ) AND
/* Dereddened magnitude cut as specified:  */
       (kPetroMag-ak) BETWEEN 0.0 AND 18.4 AND
       yPetroMag > 0  AND
       modelMag_u > 0 AND
       modelMag_g > 0 AND
       modelMag_r > 0 AND
/* Exclude any non spectroscopic redshift 
   objects for a clean sample: */
       z.z BETWEEN 0.01 AND 0.15 AND
       z.zWarning=0
/* UKIDSS DR2 rows returned: 8,086
   Execution time:           05m 13s  */


SELECT ra,dec
FROM   lasMergeLog
WHERE  j_1mfID > 0 AND
       hmfID   > 0 AND
       kmfID   > 0
/* UKIDSS DR2 rows returned: 6,242
   Execution time:           00m 04s  */


-- Infrared colour-selected sources that are optical drop-outs - U1

SELECT COUNT(*)
FROM   lasSource
WHERE
/* Colour cuts for mid-T & later: */
       ymj_1Pnt > 0.5 AND
       j_1mhPnt < 0.0 AND
/* Source not detected above 2sigma within  
   1" in SDSS-DR5 i' or z':                */
       sourceID NOT IN (
       SELECT masterObjID
       FROM   lasSourceXDR5PhotoObj AS x,
              BestDR5..PhotoObj     AS p
       WHERE  p.objID = x.slaveObjID   AND
              (psfMagErr_i < 0.5       OR 
               psfMagErr_z < 0.5)      AND
              x.distanceMins < 1.0/60.0
        ) AND
/* Use only frame sets overlapping with 
   SDSS-DR5:                            */
       frameSetID IN (
       SELECT DISTINCT(frameSetID)
       FROM   lasSource             AS s,
              lasSourceXDR5PhotoObj AS x
       WHERE  s.sourceID = x.masterObjID
       ) AND
/* Unduplicated or primary duplicates only: */
        (priOrSec = 0 OR priOrSec = frameSetID) AND
/* Generally good quality: */
        yppErrBits   < 256 AND
        j_1ppErrBits < 256 AND
        hppErrBits   < 256 AND
/* Point-like morphological classification: */
        mergedClass=-1 AND
        mergedClassStat BETWEEN -3.0 AND +3.0 AND
/* Reasonably circular images in YJH: */
        yEll   < 0.35 AND 
        j_1Ell < 0.35 AND 
        hEll   < 0.35 AND
/* IR pairs within 0.5 arcsec: */
        j_1Xi  BETWEEN -0.5 AND +0.5 AND
        j_1Eta BETWEEN -0.5 AND +0.5 AND
        hXi    BETWEEN -0.5 AND +0.5 AND
        hEta   BETWEEN -0.5 AND +0.5 AND
/* YJ measured to 5 sigma and H to 4sigma: */
        yAperMag3Err   < 0.20 AND
        j_1AperMag3Err < 0.20 AND
        hAperMag3Err   < 0.25
/* UKIDSS DR2 rows returned: 1 (count=25)
   Execution time:           00m 48s  */

