Classifying black holes with SQL

A conclusion to basic SQL Queries

Credit: NASA, ESA, S. Bianchi (Università degli Studi Roma Tre, Italy) and M. Chiaberge (ESA, STScI, and JHU)

“In a black hole, no one can see you disappear.” — Stephen Hawking

In the past month, I have written about basic SQL queries and used examples from astronomy. Here are the posts:

• Black holes, planets, and SQL (link here) showed how to use SELECT, WHERE, and LIMIT— the very basic commands of SQL.
• Touring the Solar System with SQL (link here) used aggregate functions, subqueries, and aliases to discover information about the 20 largest objects in the Solar System.
• Searching for Moons with SQL (link here) found some large moons in the solar system by querying a SQL table with the GROUP BY and HAVING clauses.

For a final tutorial on basic SQL queries, I will cover CASEstatements. This is like an if/then statement in other languages in that it determines a result from conditionals. Keep this in the back of your mind while I re-introduce you to the SQL table that I will use in this post. (if you’re curious, this table of information was used in Black holes, planets, and SQL.)

The table black_holes has characteristics of black holes, specifically, the name (which, for most entries, hints at the location of the object) and mass.

To read the mass in this table, use scientific notation: base_mass x 10^power. The unit of mass for these black holes is in solar masses, or “the mass of the Sun”. For example, the Sombrero galaxy is 1x10⁹ times the mass of the Sun.

We can see that, from the power column, the mass of the last black hole is very much less than the other three. Using a WHEREclause — something covered in Black holes, planets, and SQL — we can verify this.

SELECT name
FROM black_holes
WHERE power > 0;

Result:

Three massive black holes.

Indeed, the result is the name of three massive black holes in the table, specifically those with power larger than 0. The 4th black hole, Cygnus X-1, should appear by itself if we update the query’s WHERE clause.

SELECT name
FROM black_holes
WHERE power = 0;

Result:

The missing black hole.

And, we see that there is only one black hole where the power is equal to zero.

Let’s take this one step further. Suppose we need to create a SQL table view that classifies each entry. For this task, CASE statements are useful.

SELECT *,
CASE 
  WHEN power > 0 THEN 'supermassive'
  ELSE 'stellar mass'
END AS bh_description
FROM black_holes;

Remember I said thatCASE acts on conditionals? Here I am creating a conditional that tests the value ofpower.

• When power is greater than 0, classify the black hole as supermassive.
• When it is equal to 0, classify the black hole as stellar mass.

(This is in line with the definitions of a supermassive black hole — found at the center of galaxies — and a stellar-mass black hole which is often found orbiting another star.) By the way, these classifications are listed in a new column, bh_description.

Here is the result of the previous query:

Classifications for the black holes.

This CASE example wraps up my introduction to SQL. Next up? I will tackle more intermediate-level join statements! As always, let’s chat in the comments!