SQL Tutorial

A Quick Introduction

Before you begin searching SkyServer, you should get a quick introduction what it means to search for data. SkyServer's information on all sky objects - stars, galaxies, and others - is stored in a database, a storehouse for data. A request to a database to return some information is called a query. When you ask a database for information, you must write your query in some computer language that the database can understand. SkyServer, like many other databases, uses Structured Query Language (SQL). When you write a query with SQL, the database searches for all records that meet your search criteria; in the case of SkyServer, each record is the information on a single observation of a single object in the sky.

But you have to be careful when you write a query: since the database contains hundreds of pieces of information for each of millions of objects, a poorly written query could return Gigabytes of information that won't help you at all. The best possible query will return all the information that you need, and no information that you don't need. Writing queries is an art - before you start writing, you need to think carefully about what information you need.

The Database Structure

To know what information you need, you need to know exactly what information the database contains. The SkyServer database is divided into a series of tables. Each table contains data of a different type. For example, all data related to an object's spectrum are stored in a table called specObjAll. All data related to an object's image properties are stored in a table called photoObjAll. SkyServer contains many tables, although most of the commonly accessed data are stored in just three or four of them.

Each table contains a series of data columns. A column contains only one type of data - for example, magnitude or sky position. Each record consists of a series of columns, although some columns may be empty for some records. Some tables contain only two or three columns; some contain hundreds! Knowing exactly which table and which column contains the data you want can be a challenge.

A very, very, very, very small part of the photoPrimary table. The full table contains more than 300 data columns and hundreds of millions of records.

The Schema Browser

A tool called the Schema Browser allows you to study all the tables and data columns, to find where the data you want are located. The Schema Browser can be intimidating, but it is easy to use. In the left-hand column, you can either browse through various tables to find a specific column, or you can search for keywords in all the column descriptions.

To browse through the tables, click on "Tables" in the left-hand column. (Note: some tables, like "specObj," are under "Views" instead...more on that later!) A list of all tables will appear under the Tables link. Click on the name of the one that you are interested in, and a list of that table's columns will appear on the right.

To search through all the tables to find a keyword, enter the keyword in the Search box and hit Go. A list of matching table columns and descriptions will appear on the right.

Try it now. Open the Schema Browser . In which table and data columns would you find the SDSS run-camcol-field classification numbers for the image of a single object? What about the object's type (star, galaxy, or other) as seen in its image? What about the redshift of a spectrum?   Show Answer

Although you have looked up data in the photoObjAll and specObjAll tables, for most queries you write you will not use those tables. Instead, you can use their associated views, which contain only the best SDSS observation for each object. Views you might use include:

• photoObj : contains photometric (imaging) data for only the best observation of each sky object
• star : contains best SDSS observations for stars only
• galaxy : contains best SDSS observations for galaxies only
• specObj : contains spectroscopic data for only the best observation of each sky object observed by the SDSS spectrograph

Now that you know how to find the information you need, you're ready to start querying the database for that information. Click Next to learn how to write a simple query.