Cannonball Into SQL Success: A Roadmap

Photo by Drew Farwell on Unsplash

One of the reasons that SQL is such a popular language for entry-level data analysts to learn is because, from the outside, it looks pretty basic.

SELECT column_name

FROM table_name

WHERE condition;

Presto! Your work is complete, right?

Not quite, as it turns out.

Once you look under the surface, you can do some really cool, complex things with SQL. But often, there’s no clear roadmap for when or how to learn the more advanced concepts in SQL.

Here’s a quick breakdown of one way you could map out your own learning plan.

Quick overview

1. Setting expectations
   — Introducing the tables
2. Dipping your toe in
   — Create, alter, delete, insert into
   — Basic statements (SELECT, FROM)
   — Filter and sort (WHERE, ORDER BY)
   — Single table data retrieval and manipulation (MIN, MAX, AVG, LIKE, CONCAT)
3. Wading in the water
   — Aggregating and grouping (GROUP BY, HAVING)
   — Conditional logic (CASE statements)
   — Join tables (LEFT, INNER, UNION)
   — Combine logic and aggregations (COUNT CASE, SUM CASE)
   — Subqueries, CTEs, temporary tables
4. Diving into the deep end
   — Stored procedures
   — Window functions
   — Optimization

Setting expectations

I’m going to be using MySQL syntax in my roadmap. I’ll also be using 2 basic tables to demonstrate each of the concepts I’m focusing on.

Those tables are the products table and the product_category table.

The products table has: product_id (primary key), product name, description, brand, category_id (foreign key), unit price, and quantity in stock.

The category table has: category_id (primary key) and category name.

First, dip your toe in

Create, alter, delete, insert

It’s always good to have an understanding of how to create, alter, update, and delete, even if you don’t plan on becoming a database administrator. This means learning statements like CREATE, ALTER, DELETE, and INSERT INTO.

CREATE TABLE statement for the product_category table.

INSERT INTO statement for new category and product.

There was a duplicate product, orange juice, with product ID 31. We used an UPDATE statement to remove the duplicate and add peanut butter into our products table. Because who doesn’t love peanut butter?

Basic statements

Learn how to write basic statements in whichever “flavor” of SQL you choose. Don’t worry too much about which one you focus on first; there are way more similarities than differences when it comes to SQL variants.

A basic query to start understanding what the database contains.

Filter and sort

Work on filtering and sorting data. You can use the WHERE clause, logical operators, and ORDER BY to better understand what’s going on within your data.

Returning results where there are 30 or less of the product in stock, ordering by unit price (highest to lowest).

Single table data retrieval and manipulation

Use single tables to explore simple data retrieval and manipulation. This might look like using aggregate functions, like COUNT, SUM, AVG, MIN, and MAX, using basic math and date/time functions, or understanding string functions like LIKE, CONCAT, LENGTH, and SUBSTRING.

Using MIN, MAX, and AVG to explore quantity in stock.

Using LIKE to find all products with “fresh” in the description.

Next, wade in the water

Aggregate and group

Dig into aggregating and grouping data more deeply. You can use GROUP BY and HAVING to really find insights in your data. The GROUP BY clause is used to group rows and perform aggregations at the group level. The HAVING clause is then used to filter the groups created by your GROUP BY based on certain conditions.

Using GROUP BY to count the number of products in each category and HAVING to return only the categories with 2 or more products in them.

Conditional logic

Deepen your understanding of conditional logic with case statements. Using CASE statements increases your flexibility. It lets you manipulate and transform data within your query, customize query results, create new columns, derive calculated values, and categorize data based on specific conditions.

Using a CASE statement in both the SELECT clause and the ORDER BY clause to categorize products by price.

Join tables

Understand different types of joins and when to use them. This is a big step; it involves not only understanding joins like INNER, LEFT, and RIGHT JOINs, but also combining multiple tables. This also means understanding primary and foreign key relationships and how to join tables based on common columns. You might also start to look at UNIONs during this time.

LEFT JOIN to find the category name of each product, using the shared column, “category_id”.

Increasing complexity of query and using an INNER JOIN.

Combine logic and aggregations

Combine CASE statements with COUNT, SUM, MAX, MIN, AVG. Take CASE statements to the next level by using them in conjunction with aggregations. This lets you perform those aggregations based on specified conditions.

Using COUNT CASE and SUM CASE to find the number of different items within each category, along with the total number in stock of each category, with a UNION ALL to combine the queries.

Use subqueries, CTEs, and temp tables

Discover subqueries, CTEs, and temporary tables. While similar, subqueries, CTEs, and temporary tables all have their uses. Many times you can use whichever you’re most comfortable with and come up with the same results.

Using a CTE (common table expression) to find total quantity of items in each category.

Using a subquery to find total quantity of items in each category.

Using a temporary table to find total quantity of items in each category.

Finally, dive into the deep end

Stored procedures

Understand the purpose of stored procedures. A stored procedure is a routine or program that can be executed multiple times. Stored procedures help to streamline database operations, increase efficiency in writing queries, and improve performance.

Stored procedure will return the products in the category_id in CALL statement, along with the total quantity in stock, which is stored in the variable @total_quantity

Window functions

Learn about window functions. Window functions allow you to perform calculations and analysis on a subset of rows within a window, without altering the overall result set. They can aggregate data at a granular level. With window functions, you can compute moving averages, running totals, rank data based on specific criteria, and calculate other metrics.

Using this window function will return the information about each product as well as the total quantity per category. Notice the final column remains the same over each category.

Optimization

Optimize, optimize, optimize. Once you have a good understanding of SQL and the various concepts, it’s time to optimize your queries. You can do this by learning about indexes, understanding the SQL order of execution, and simplifying where you can.

Wrapping Up

I’ll be the first to say that I haven’t mastered all of these concepts! That’s one part of SQL that I love; I’m learning more every day.

One other note here. Just because I’ve laid out this roadmap doesn’t mean that…

1. …it’s the be all, end all. This is simply a rough idea of when and how I approached my own learning.
2. …you have to master all of these things before applying to jobs! I certainly didn’t know how to do window functions or stored procedures before getting my first job. Focus on becoming great at the basics and the rest will follow.
3. …once you’ve learned all of these concepts, you’re done learning. SQL is ever-changing and growing so don’t expect to ever be “done” with learning.

SQL can be incredibly powerful once you start to understand its full capabilities. It’s one of the tools that I tell aspiring data analysts that I would definitely recommend learning.

So there you have it! A roadmap to learning SQL. I’d love to hear what you think. What concepts did you learn first? Does your path align with my own?

Photo by Subtle Cinematics on Unsplash

If you liked this, we’d love for you to subscribe to our publication.

And drop us a comment letting us know what you’d like to see next. You can also submit your own writing for us to promote, so don’t be shy if you would like to become a contributor. Check out submission instructions here.

Happy learning!