Practice Using Aggregate Functions, Group By, and Order By
Write queries to answer the astronomy questions in Practice 7, 8, and 9.
Use the Schema
Browser when you need it. Many of these questions can be answered in multiple
ways; the Sample Solutions give just one way of solving it.
|
Practice 7. What are the northernmost and southernmost
objects with spectra measured by the SDSS?
Show Sample Solution
A query that accomplishes this task is:
SELECT
min(dec),max(dec)
FROM
specObj
In SDSS Data Release 13, the northernmost object (maximum declination) is at dec = 84.852144; the southernmost object
(minimum declination) at dec = -19.702714
Hide Sample Solution
|
|
Practice 8. What is the redshift of the nearest galaxy
whose spectrum was measured by the SDSS with high confidence (zWarning=0)?
Use the equation
cz = H0d
to find the distance to the galaxy in light-years. The speed of
light, c, is 1 light-year per year. The constant H0, called
the Hubble constant, is about 70 km/s/Mpc, or
7.11 x 10-11 / yr.
Compare the distance you found to the distances to the Andromeda
Galaxy (2 million light-years) and the Whirlpool Galaxy (37 million
light-years). Does the distance you found seem reasonable?
Show Sample Solution
To find the galaxy with the smallest redshift, use a query like the following:
SELECT top 1
p.objID, s.z as redshift, p.u, p.r, p.g, p.i, p.z
FROM photoObj p
JOIN specObj s ON s.bestObjID = p.objID
WHERE
s.class = 'galaxy'
and s.z > 0
and p.objID <> 0
and s.zWarning = 0
ORDER BY s.z
The query returns
ObjID = 1237662666425827520 at z = 4.44 x 10-7.
Using the equation cz = H0d, the galaxy is at a
distance of about 800,000 light-years. Comparing the distance
to this galaxy to the Andromeda and Whirlpool Galaxies,
the distance seems reasonable.
Hide Sample Solution
|
|
Practice 9. What field has galaxies with
the highest average redshifts in run=5112, camcol=1?
Be sure you are searching fields (as run-camcol-field) for
galaxies. Also look at how many spectrally measured galaxies
are in the field - make sure you don't pick a field with only one or two
galaxies! Also, note that this query will probably take a long time to
execute.
Show Sample Solution
SELECT
p.run, p.camcol, p.field, count(s.z) as count, avg(s.z) as average
FROM photoObj p
JOIN specObj s ON s.bestObjID = p.objID
WHERE
s.class = 'galaxy'
and p.run = 5112
and camcol = 1
GROUP BY
p.run,p.camcol,p.field
ORDER BY
avg(s.z) desc
The field in run 5112 camcol 1 with the highest average redshift
is field 5112-1-369. It includes three spectrally measured galaxies
with average redshifts of about 0.68.
Hide Sample Solution
|
Notice that these practice questions have gotten more difficult, and
are more like the questions that astronomers really ask in their
research. You're getting better in your searches!
|
