iDR1: Simple Example Queries

Start here: very simple examples

1. List the recommended parameters

   SELECT * FROM RecommendedAstroAnalysis;
             

   This query lists all the physical parameters (effective surface temperature, log surface gravity etc) and element abundances for stars in the GES archive as recommended by working groups WG10 to WG13.

   As formulated, the query returns all the columns of recommended 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).

2. List all working group recommended and node parameters

   SELECT TOP 10 * FROM AstroAnalysis;
             

   Lists all the parameters and abundances determined by working groups WG10 to WG13, both each working group's recommended parameters and those determined by individual nodes.

3. 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 AstroAnalysis;
             

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 recommended parameters for a given star

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

   View 'RecommendedAstroAnalysis' contains two entries for 08091845-4653440. One is the recommended parameters determined by WG10 and the other those determined by WG12. The query can be modified to return just the recommended parameters determined by one of the working groups, for example WG12:

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

4. List the recommended and individual node parameters determined by a given working group for all the observations of a given star

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

   Both the recommended parameters and those determined by the various individual nodes of the working group are listed.

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

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

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

6. 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.

7. 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;