SQL Tutorial: More Sample Queries
 
SQL Tutorial
SQL Tutorial
 Back to Help
 1. Introduction
 2. A Simple Query
 Practice
 3. Common Searches
 4. More Samples
 Practice
 5. Multiple Tables
 Practice
 6. Aggregate Fcns.
 7. Group By
 8. Order By
 Practice
 9. Views
 10. Functions
 Practice
 11. Conclusion

More Sample Queries

Look at the following query:

select
z, ra, dec, bestObjID

from
specObj

where
specClass = 2 AND
z > 0.3 AND
zConf > 0.35

The select and from blocks tell the database to look in the SpecObj table (which contains spectroscopic information) and send you the redshift (z), the right ascension, the declination, and the (final, or "best") object ID. The where block tells the database to look only at galaxies (which have a specClass value of 2) with redshifts above 0.3 and a high confidence level in the redshift measurements (zConf > .35).

Logical Operators

In this query, the word AND appears between all the characteristics in the where block. The query includes three characteristics - matching objects must be galaxies, they must have redshift > 0.3, and the redshift confidence must be > 0.35. All three of these characteristics must be met by a given record for the search to return that record.

AND is just one of three logical operators used by SQL; the others are OR and NOT. The meanings of the three logical operators are given in the table below:

Logical Operator

Meaning

AND

all characteristics met

OR

at least one characteristic met

NOT

characteristic not met

You can combine the logical operators using parentheses. For example, for the characteristics A, B, and C, A AND (B OR C) means that either characteristics B or C, as well as characteristic A, must be met for records to match. What does A AND (NOT B) mean?

The query below is like the first query except that it will return both galaxies (specClass = 2) and quasars (specClass = 3 or 4). The OR statement in the where block causes the query to return both.

select
z, ra, dec, bestObjID

from
specObj

where
(specClass = 2 OR specClass = 3 OR specClass = 4) AND
z > 0.3 AND
zConf > 0.95

Mathematical and Conditional Operators

SQL includes a variety of mathematical operators that perform math functions in a query. You can use multiplication, division, addition and subtraction. SQL uses the same symbols for these operators that most other computer languages use: + for addition, - for subtraction, * for multiplication, and / for division. Other mathematical operators include COS(x), which returns the cosine of column x, or SQRT(x), which returns the square root of column x. Here is a complete list of SQL's mathematical operators.

You can also use the conditional operators summarized below.

Conditional

Meaning

=

equal to

>

greater than

<

less than

>=

greater than or equal to

<=

less than or equal to

<>

not equal to

Let's say you wanted to find a list of very blue stars. In astronomy, color is defined by the differences in magnitudes (see the Color project for more information). In the SDSS's system of measurement, blue stars have u-g < 0.5. So to find blue stars, you might write a query like the following:

select
ra, dec, modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z, objID

from
PhotoObj

where
type = 6 AND
modelMag_u - modelMag_g < 0.5

This query searches through all photometric objects (in the PhotoObj table) for stars (denoted by type = 6) where u - g < 0.5. You could further refine your search by searching for a certain ra and dec. Or you could limit yourself to bright stars by specifying a magnitude limit such as modelMag_r < 17.

Try It!

Try pressing the buttons "Query 1," "Query 2," and "Query 3" below. These buttons will make the three queries above (in the purple boxes) appear in the query window. Press Submit to execute the queries. Write down a few of the ObjIDs, then use the Object Explorer to examine the objects. Do they have the characteristics you requested in the where block? What kinds of astronomy questions do you think you could answer by studying these objects?

Click Next to get some more practice in writing SQL queries.


Format HTML XML CSV



Enter your SQL query in the text box. The query is limited to 90 seconds and 100,000 rows.