Simple Example Queries

Start here:

Very Simple Examples

1. List the recommended, homogenised parameters

   SELECT * FROM RecommendedAstroAnalysis;
             

   This query lists all the recommended, homogenised physical parameters (effective surface temperature, log surface gravity etc) and element abundances for stars in the GES archive as determined by working group 15 ('WG15'). It is Consortium policy that these WG15 parameters and abundances must be used in all GES publications and calculations leading to such publications.

   As formulated, the query returns all the columns of recommended, homogenised parameters and abundances for the all survey stars for which they are tabulated. However, it is usually neither efficient nor useful to query the entire contents of a table or view. For the purposes of these first, introductory examples the queries will be changed to return just a few rows for example:

   SELECT TOP 10 * FROM RecommendedAstroAnalysis;
             

   which returns just ten rows. This form will be used in the remaining examples in this section (the next section gives some more practical examples of selecting the rows to be listed).

Aside on sets of physical parameters and abundances in the GES Archive

Though the recommended physical parameters and abundances, as homogenised by WG15, are the set that you are most likely to use, the archive contains several other sets produced by the GES Consortium, which you may need for some purposes. Like the WG15 homogenised set each of these sets is available as a view in the database. The following sets are available:

View	Description of set of parameters
RecommendedAstroAnalysis	Overall, recommended, homogenised WG15 parameters and abundances.
WgRecommendedAstroAnalysis	Recommended parameters and abundances produced by individual working groups (for working groups WG10 to WG14).
WpNaAstroAnalysis	Abundances produced by individual nodes within each of the working groups using their parent working group's recommended astrophysical parameters (for working groups WG10 to WG13).
NpNaAstroAnalysis	Physical parameters and abundances determined independently by the individual nodes within each of the working groups (again for working groups WG10 to WG13).

Please remember that it is Consortium policy that only WG15 recommended, homogenised parameters are used in publications.

2. List the working group recommended parameters

   SELECT TOP 10 * FROM WgRecommendedAstroAnalysis;
             

   Lists the recommended physical parameters and abundances produced by working groups 10 - 14.

3. List node abundances determined with working group parameters

   SELECT TOP 10 * FROM WpNaAstroAnalysis;
             

   Lists the abundances determined by individual nodes by adopting their parent working group's physical parameters. Note that these values are only available for some nodes of some working groups.

4. List node parameters and abundances

   SELECT TOP 10 * FROM NpNaAstroAnalysis;
             

   Lists the physical parameters and abundances determined independently by individual nodes within each of the working groups. Note that these values are available for all nodes within working groups WG10 to WG13.

5. List the number of rows in each of the sets of parameters

   It is possible to modify the above queries to list the number of rows in each of the various sets of parameters and abundances:

   SELECT COUNT(*) FROM RecommendedAstroAnalysis;
   
   SELECT COUNT(*) FROM WgRecommendedAstroAnalysis;
   
   SELECT COUNT(*) FROM WpNaAstroAnalysis;
   
   SELECT COUNT(*) FROM NpNaAstroAnalysis;
             

Simple Queries to Show the Data Available for a Star

The following examples present some simple queries to show the data available in various tables for the star whose 'cName' is '08091845-4653440'.

1. Check whether a given star is in the database

   SELECT * FROM Target
   WHERE cName='08091845-4653440';
             

   The star is listed in the 'Target' table, so there are data for it in the GES archive.

2. List all the spectra for a given star

   SELECT * FROM Spectrum
   WHERE cName='08091845-4653440';
             

   The star has only one entry in the 'Spectrum' table, so only a single spectrum of it has been obtained.

3. List the homogenised, recommended parameters for a given star

   SELECT * FROM RecommendedAstroAnalysis
   WHERE cName='08091845-4653440';
             

   View 'RecommendedAstroAnalysis' contains the WG15 homogenised parameters.

4. List the working group recommended parameters for all the observations of a given star

   SELECT * FROM WgRecommendedAstroAnalysis
   WHERE cName='08091845-4653440';
             

   View 'WgRecommendedAstroAnalysis' contains more than one entry for 08091845-4653440. iDR4 has entries for WG12 and WG14 and iDR5 has entries for WG12, WG14 and WG15. Subsequent releases may contain more entries (or fewer entries, though this is less likely). The query can be modified to return just the recommended parameters determined by one of the working groups, for example WG12:

   SELECT * FROM WgRecommendedAstroAnalysis
   WHERE cName='08091845-4653440'
     AND wg='WG12';
             

5. List the parameters determined by all the individual nodes of a given working group for all the observations of a given star

   SELECT * FROM NpNaAstroAnalysis
   WHERE cName='08091845-4653440'
     AND wg='WG12';
             

   The parameters determined by the various individual nodes of the working group are listed. In this case view 'NpNaAstroAnalysis' is being searched to list the values found when the nodes were determining both the stellar parameters (effective temperature, surface gravity etc.) as well as elemental abundances. These values will always be available. In some cases the nodes also determined a second set of abundances by adopting the recommended effective temperature etc. of their working group. These values are available in view 'WpNaAstroAnalysis' which can be queried in the same way:

   SELECT * FROM WpNaAstroAnalysis
   WHERE cName='08091845-4653440'
     AND wg='WG12';
             

   (In this case WG12 did not record any 'WpNaAstroAnalysis' values.)

6. List the parameters determined by a given node for all the observations of a given star

   SELECT * FROM NpNaAstroAnalysis
   WHERE cName='08091845-4653440'
     AND wg='WG12'
     AND nodeName='Arcetri';
             

   Only the parameters determined by the WG12 Arcetri node are listed.

7. List selected parameters for a given star

   All the previous examples have listed all the columns available in each of the tables or views. It is possible (and usually more useful) to list just selected columns:

   SELECT 
     cName, teff, logg
   FROM 
     RecommendedAstroAnalysis
   WHERE cName='08091845-4653440';
             

   Here just the 'cName', effective temperature and surface gravity are being listed.

8. List non-default values for a chosen column

   Finally, though not an example of listing parameters for a given star, another useful technique is to just list stars with non-default values for some column. Recall that not all physical parameters and abundances were determined for all the stars in the survey. Where no value is available the archive stores a so-called default value, usually -9.999995e+08, chosen to be well outside the range of feasible values for the quantity being tabulated. To list only stars with actual determinations of some quantity (that is, non-default values for it) a query is constructed which includes a selection to exclude the default values. For example, to list stars with a non-default effective temperature:

   SELECT 
     TOP 20 cName, teff, logg
   FROM 
     RecommendedAstroAnalysis
   WHERE teff > -9.9e8;
             

   Here the query has been arbitrarily restricted to list just twenty stars. Similarly it is possible to list just the twenty stars with the hottest or coolest effective temperature. Hottest:

   SELECT 
     TOP 20 cName, teff, logg
   FROM 
     RecommendedAstroAnalysis
   WHERE teff > -9.9e8
   ORDER BY teff DESC;
             

   …and coolest:

   SELECT 
     TOP 20 cName, teff, logg
   FROM 
     RecommendedAstroAnalysis
   WHERE teff > -9.9e8
   ORDER BY teff ASC;