🦸 Become a Super Hero by Learning SQL!

Knowing SQL frequently makes me feel like a superhero. And I wouldn't even say I'm an expert at it. I'm intermediate perhaps.

The thing is, everyone uses SQL, but most people remain at a beginner level forever! Most SQL users can do some simple SELECT and INSERT statements and that's about it. When they need to do something more complicated? Well, that's your chance to swoop in and save the day like freaking Batman or something, because you took the time to learn about CTEs, JOINs, subqueries, transactions, isolation levels, indexes, aggregation functions - the things that they need to solve their problem.

My impetus for this article was helping someone improve the performance of calculating a leaderboard for their game. They were querying the entire scores table and calculating ranks in their application code. This took more than ten seconds and was getting slower by the day.

Their leaderboard calculation logic was actually more complex than just ordering the players by score. We had to use a CASE statement, two CTEs, and a window function. But we still figured it out in half an hour and now the scoreboard is displayed almost instantly, with all of the calculations implemented in SQL and happening in the database server so only a small set of rows needs to be returned across the wire to application code.

And for this, I, the superhero, was promised lunch and a drink. I've had other similar experiences too. This doesn't really happen to me with, say JavaScript, because the JavaScript users around me know how to use JavaScript. But for some reason, the SQL users around me often don't know how to use SQL! I think this is probably because there's a common tendency to abstract away the database behind ORMs and such and treat it as a simple object store, rather than the fast data processing/transforming beast that it can be (if you know your SQL!).

Knowing SQL so that you can use it for your own projects is great of course, but being able to help other people like this and be appreciated for it feels even better.

Are you one of those SQL users who only knows the basics? I want to encourage you to take the time to go deeper! It really pays off, not just for you, but sometimes for the people around you too (and that's especially good for your career 😉).

Learning SQLite

This article is not going to teach SQL. There are others than can do it better. But I will try to at least provide a bit of direction.

I suggest starting with SQLite. You do not have to run a database server, and you can easily mess with different data sets.

First, install DB Browser for SQLite. It's a great little program that can be used to view SQLite databases and run SQL queries.

Next, grab a SQLite data set or two from these SQLite Data Starter Packs.

You should notice that each data set is just a single *.sqlite file. That one file is an entire database and you can open it in DB Browser for SQLite. Super convenient!

If you download the SimpleFolks for Simple SQL data set and open it in the DB browser, you will see a list of the tables that are in the database:

And in the Browse Data tab, you can use the Table dropdown to see the contents of the different tables:

In the Execute SQL tab, you can run a SQL query and see the results. Here's a query to calculate the number of homes that each person owns:

Looking at different data sets and writing more and more complex queries is a great way to learn how to use SQL.

But we might not be quite there yet, so you may consider putting this aside for now, going through some tutorials, and using these tools to experiment freely after you have the basics down.

Also check out the SQL section on HackerRank. It has a lot of great questions and really gets you used to subqueries and grouping in particular.

The other SQLs

SQLite is great for learning and for small projects, but significant projects tend to use full-fledged database server software such as MySQL, PostgreSQL, Oracle, Microsoft SQL Server, or others. Fortunately, though they all have their quirks and special features, they all use SQL and share core syntax and features. So most knowledge you gain from working with SQLite will transfer. (Just note that NoSQL databases such as MongoDB are a different beast, though some knowledge such as indexing does transfer well)

Conclusion

SQL is widely used in the software industry and open source, but many developers only know how to use it to save some data and load some data. Their shallow SQL skills can quickly get them into a bind. Their city needs a SQL superhero. Can you be that superhero?

Invest just a few hours into learning SQL and you can come a long way. It's a skill that many developers need a lot more than they think they do!