Julia Ridgely

Date: Nov. 24, 2009

It’s early November, but desperate merchants, fearing another recession-tainted holiday season, are already breaking out the Christmas gear. That also means a fresh crop of holiday-themed albums featuring artists ranging from Sting to David Archuleta to Bob Dylan. All of which makes it the perfect time to don your mittens and stocking cap and go a-caroling through the MusicBrainz database in search of the weirdest, most unexpected, and most popular Christmas cover songs. When SQL users hear the word "search,” they think of the SELECT…WHERE statement. This selects (or returns) rows in a table where certain conditions are true. If we were searching for all versions of “The Christmas Song” using SQL, we might write this statement:

SELECT * FROM track WHERE name=’The Christmas Song’

The “=” means the value in the name column of the track table has to exactly match the title. The syntax isn’t much different in HTSQL:

http://musicdemo.htsql.com/track?name=’The Christmas Song’

Wow, 875 versions and counting! And we still like Nat King Cole’s the best.

Let’s use the same techniques we used in the previous article to clean up the results and bring in some interesting information from other tables, including the artist name and the length of the track, with the longest first:

http://musicdemo.htsql.com/track{artist.name,name,length-}?name='The Christmas Song'

So the title for longest version of “The Christmas Song” goes to Japanese jazz pianist Makoto Ozone with 493626. (What’s that? You don’t know how long 493626 is? Neither do I. MusicBrainz stores the track lengths in milliseconds. I wasn’t going to cover arithmetic operators in this article, but I’m too curious, so here it goes:

http://musicdemo.htsql.com/track{artist.name,name,div(div(trunc(length,2),1000),60)-}?name='The Christmas Song'

That column name looks kind of sloppy, so let’s alias it with the name "length":

http://musicdemo.htsql.com/track{artist.name,name,as(div(div(trunc(length,2),1000),60),length)-}?name='The Christmas Song'

OK, back to our main topic. That’s a great technique if you know exactly what you’re looking for. But what if you're not sure about the song title or artist name? That’s where the “like” operator comes in handy. For instance, let's say I remember that an artist named “Cole” recorded the song but I don’t remember his or her first name. This query will match the word “cole” anywhere in the artist name, regardless of case:

http://musicdemo.htsql.com/track{artist.name,name}?name='The Christmas Song'&artist.name~'cole'

Be careful with “like” operators because the query can take a very long time to execute. For performance, it’s always best to narrow your query as much as possible. Sometimes it’s not what you’re looking for, it’s what you’re not looking for that’s important. Let’s go back to our original query and see how many versions of “The Christmas Song” were not recorded by Nat King Cole. This query uses a “compound condition” (the "&", equivalent to AND in SQL) and an “operator negation” (!=, which means “does not equal”). The result:

http://musicdemo.htsql.com/track{artist.name ,name}?name='The Christmas Song'&artist.name!='Nat King Cole'

Taking away the late, great Nat (who was responsible for 67 of the tracks in the database) that leaves 777 versions. Using the examples above, you can probably figure out how to use HTSQL’s additional comparison operators (<,>,<=,>=) and the compound operator for “or,” represented by the "|" character.

Ready to test your skills? Try to figure out the following and post your URLs and questions in the comment section.

Have the Flaming Lips ever recorded a song with “Christmas” in the title?

Are there any songs that have both Hanukkah and Christmas in the title? (This one takes a while—see the warning about the "like" operator above.)

Did Sonny & Cher OR Strawberry Shortcake ever record “Jingle Bells”? (Try this one the hard way, using the or operator.)