Welcome to the Intermediate SQL Tutorial! If you skipped the Basic SQL Tutorial, you should take a quick peek at this page to get an idea of how to use Mode’s SQL editor to get the most out of this tutorial. For convenience, here’s the gist:
- Open another window to Mode. Sign up for an account if you don’t have one.
- For each lesson, start by running
SELECT * on the relevant dataset so you get a sense of what the raw data looks like. Do this in that window you just opened to Mode.
- Run all of the code blocks in the lesson in Mode in the other window. You’ll learn more if you really examine the results and understand what the code is doing.
In the previous tutorial, many of the practice problems could only be solved in one or two ways with the skills you learned. As you progress and problems get harder, there will be many ways of producing the correct results. Keep in mind that the answers to practice problems should be used as a reference, but are by no means the only ways of answering the questions.
The Apple stock prices dataset
For the first few lessons, you’ll be working with Apple stock price data. The data was pulled from Google Finance in January 2014. There’s one row for each day (indicated in the
close are the opening and closing prices of the stock on that day.
low are the high and low prices for that day.
volume is the number of shares traded on that day. Some data has been intentionally removed for the sake of this lesson. Check it out for yourself:
SELECT * FROM tutorial.aapl_historical_stock_price
Aggregate functions in SQL
As the Basic SQL Tutorial points out, SQL is excellent at aggregating data the way you might in a pivot table in Excel. You will use aggregate functions all the time, so it’s important to get comfortable with them. The functions themselves are the same ones you will find in Excel or any other analytics program. We’ll cover them individually in the next few lessons. Here’s a quick preview:
COUNT counts how many rows are in a particular column.
SUM adds together all the values in a particular column.
MAX return the lowest and highest values in a particular column, respectively.
AVG calculates the average of a group of selected values.
The Basic SQL Tutorial also pointed out that arithmetic operators only perform operations across rows. Aggregate functions are used to perform operations across entire columns (which could include millions of rows of data or more).