SQL Crash Course: Understanding the Basics

Hey everyone 👋

SQL is one of the top skills that every data analyst should know.

Learning to code can be challenging, but the good news is a handful of functions will get you plenty of mileage in your day-to-day work as an analyst.

I want to try something different this week: a SQL crash course.

Below are 6 of the most essential functions to know.

What is SQL?

SQL is the language of databases. It actually stands for “Structured Query Language.” It’s used to interact with data stored in databases and retrieve information efficiently. The core of SQL is querying, which means asking questions to the database and getting meaningful answers.

1 | SELECT * FROM your_Data:

The most basic SQL query is the "SELECT" statement. It's like telling the database, "Show me everything." For instance, if you have a table named "Customers," you can retrieve all data from it with:

This query will return all rows and columns from the "Customers" table. However, you can SELECT individual columns too. It doesn’t have to be everything.

Btw, you query should always end with a ;

2 | Filtering Data with WHERE

To make your query more specific, you can use the "WHERE" clause. Let's say you want to find customers in a specific city, like New York:

This query filters the results to only include rows where the "City" column matches 'New York.'

3 | Sorting Results with ORDER BY

You can also sort your results using the "ORDER BY" clause. If you want to see those New York customers in alphabetical order:

This query will order the results by the "LastName" column.

4 | Aggregating Data:

SQL isn't just for retrieving data; you can perform calculations too. For instance, finding the average age of your customers:

Here, we use the "AVG" function to calculate the average age of customers.

Notice we’re also utilizing the AS statement to create an alias for this column.

5 | Grouping Data with GROUP BY:

The "GROUP BY" clause is used to group rows that have the same values in specified columns. Let's say you want to know how many customers you have in each city:

This query groups the data by the "City" column and counts the number of customers in each city.

6 | Joining Tables with JOINs:

In real-world scenarios, data is often distributed across multiple tables. You can combine data from different tables using "JOIN" statements. For example, if you have an "Orders" table and a "Customers" table, you can find which customers placed orders like this:

This query combines data from both tables based on the "CustomerID" column. The ON statement ties the tables together with a column shared between both columns; in this case, “CustomerID.” We also prefix the table names to each mention of CustomerID: “Customers.CustomerID”

Putting It All Together:

Now, let's create a more complex query that utilizes all the functions we've discussed. Imagine you want to find the average order value for each city for customers in New York. You also want to list these cities in alphabetical order. Here's how you can do it:

This query combines filtering, aggregation, grouping, and ordering to provide a specific, insightful result.

Conclusion

Getting started with SQL may seem intimidating at first, but it’s really quite fun. Start with the basics, gradually build your knowledge, and practice combining these functions to tackle real-world data challenges.

These are beginner-level functions, but you can do a surprising amount of work with them.

Maybe I’ll turn this into a carousel for a future LI post 😉 

Btw I made the code snippets with Carbon.

What did you think of this article? Would you like to see more content like this? Reply to this email to let me know!

That’s it for this week.

See you next time

Matt ✌️ 

Whenever you’re ready, there are 2 ways I can help you:

1 | The Data Portfolio Guidebook

If you’re looking to create a data portfolio but aren’t sure where to start, I’d recommend this ebook: Learn how to think like an analyst, develop a portfolio and LinkedIn profile, and tackle the job hunt.

Join 500+ learners here

2 | 1:1 Coaching Call

For help navigating the data job hunt, consider booking a 1:1 career guidance session with me. There are a few options available to help you get to your ideal data job faster.

Schedule a 1:1 here