Classifying black holes with SQL
A conclusion to basic SQL Queries
“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
, andLIMIT
— 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
andHAVING
clauses.
For a final tutorial on basic SQL queries, I will cover CASE
statements. 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 WHERE
clause — something covered in Black holes, planets, and SQL — we can verify this.
SELECT name
FROM black_holes
WHERE power > 0;
Result:
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:
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 assupermassive
. - 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:
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!