Introduction to Using the GES Science Archive
=============================================

Workshop given on 11 November 2014 as part of 'GES 2014', the Gaia-ESO
Survey Second Science Meeting' held in Porto, Portugal.

Crib-sheet of example queries used in the tutorial.


SQL: Querying the Archive
-------------------------
SELECT COUNT(*) FROM Target;

SELECT * FROM Target;

SELECT TOP 10 * FROM Target;

SELECT TOP 10 cname, ra, dec, bmag FROM Target;

SELECT TOP 10 cname, ra, dec, bmag FROM Target
WHERE bmag > 18.0 AND bmag < 18.5;

SELECT TOP 10 cname, ra, dec, bmag FROM Target
WHERE bmag > 18.0 AND bmag < 18.5
ORDER BY bmag;

Queries on Target Stars
-----------------------
SELECT * FROM Target WHERE cName ='11053303-7700120';

SELECT * FROM Target WHERE cName IN ('11034945-7700101',
'11044460-7706240');

SELECT * FROM Target WHERE (ra BETWEEN 70 AND 80) AND
(dec BETWEEN -45 AND -30);

Queries on Spectra
------------------
SELECT * FROM Spectrum WHERE cName ='11053303-7700120';

SELECT * FROM Spectrum WHERE cName IN ('11034945-7700101',
'11044460-7706240');

SELECT cName, count(cName) AS no_of_spectra FROM Spectrum
WHERE cName IN ('11034945-7700101',
'11044460-7706240')
GROUP BY cName ORDER BY cName;

Identifiers in Archive Tables
-----------------------------
SELECT TOP 10 cName, targetID,  ra, dec FROM Target;

SELECT TOP 10 specID FROM Spectrum;

SELECT TOP 10 sp.specID, tg.cName, tg.targetID
FROM Spectrum sp, Target tg
WHERE sp.targetId = tg.targetId;

Queries on Spectra
------------------
SELECT TOP 10 spec.cName, frame.instrument
FROM Spectrum spec, SpecFrame frame
WHERE spec.specFrameID = frame.specFrameID;

Retrieve Copies of  Spectra
---------------------------
SELECT DISTINCT TOP 10
spec.cName, spec.specID, spg.specID, spg.fileName
FROM Spectrum spec, SpectrumGroup spg
WHERE spec.specID = spg.specID;

SELECT DISTINCT TOP 10
spec.cName, spec.specID, spg.specID, spg.fileName,
dbo.fWgetCmd(spg.fileName)
FROM Spectrum spec, SpectrumGroup spg
WHERE spec.specID = spg.specID;

Queries on Recommended Analyses
-------------------------------
SELECT TOP 10 cName, wg, teff, logg, feh, li1, c1
FROM RecommendedAstroAnalysis;

SELECT cName, wg, teff, logg, feh, li1, c1
FROM RecommendedAstroAnalysis
WHERE  cName ='11053303-7700120';

Queries on Recommended Analyses
-------------------------------
SELECT TOP 10 * from SpectrumGroup;

Queries on Recommended Analyses
-------------------------------
SELECT
  racc.cName, racc.teff, racc.logg, racc.feh,
  sp.expTime, sp.snr,
  spg.fileName
FROM
  RecommendedAstroAnalysis racc,
  SpectrumGroup spg,
  Spectrum sp
WHERE  racc.specGroupId = spg.specGroupId
  AND spg.specId = sp.specId
  AND racc.cName ='11053303-7700120';

Queries WG Recommended and Node Analyses
----------------------------------------
SELECT cName, wg, nodeName, teff, logg, feh, li1, c1
FROM NpNaAstroAnalysis
WHERE  cName ='11053303-7700120' and wg='WG11'
ORDER BY nodeName;

Queries on Line Lists
---------------------
SELECT * FROM LineAtomHfs
WHERE lambda BETWEEN 4500 AND 4800
ORDER BY lambda ASC;

SELECT * FROM LineAtomHfs
WHERE name1 = 'Al' and ion = 1
ORDER BY lambda ASC;


Contact for queries about using the GES archive: ges-support@roe.ac.uk


Clive Davenhall (acd@roe.ac.uk)
Original: 7/11/14,
revised:  14/11/14.