This tutorial will help you make use of SQL to make the most of bit.io's Query Editor.

Getting Started

This tutorial makes use of this public bit.io repository. You may want to switch to the full-screen query editor layout when following along.

The SELECT Statement

SQL, which stands for Structured Query Language, a way of telling a database like bit.io how you want it to present your data. The SELECT statement is a type of SQL instruction that allows you to retrieve the specific data you want.

Selecting a column

Here we have a simple table describing the guests at our birthday party. In general, a table can have many columns. Let's say we are only interested in the names of our attendants. We can use SQL to select the "name" column only.

SELECT "name" FROM "ellie-bitio/SQL Guide"."Birthday Party";

SELECT with expressions

Let's step things up a notch. SQL doesn't just let you select columns in your table, it also lets you perform computations on those columns.

Let's say we want to know whether we should bring alcohol to our party. We have the ages of all our participants, but what we really want to know is whether they're over 21 (this is an American party). We can use SQL to find this out!

SELECT "name", "age" >= 21 AS "can drink"
FROM "ellie-bitio/SQL Guide"."Birthday Party";

Our table hasn't been modified. If we hit "Clear Query Result" we can see it's still there. Instead, our query is showing us a computed view of our data.

There are three new concepts in this example - did you catch them all?

1. We SELECT an expression, "age" > 21, instead of a column
2. We give that expression a name using AS, and
3. We select multiple columns by separating them with a comma.

SQL has a ton of useful functions! You can read more about them at SQL Functions.

Selecting only some rows

Our previous examples have selected every row from our table. But sometimes, we're only interested in a subset of our rows. This is where the SQL WHERE statement comes in. Say we're only interested in people aged 5-10 on our list. We can select them using this query:

SELECT * FROM "ellie-bitio/SQL Guide"."Birthday Party"
WHERE "age" >= 5 AND "age" <= 10;

This example also introduces the wildcard * character. By using SELECT *, we tell SQL we are interested in all the columns of our table without having to list them out manually.

Joining Tables

To fully use the power of SQL, we will bring in a table of childrens' allergies.

This is great, but we would really like to have all of our information in one place. We can do that with the JOIN statement!

SELECT * FROM
    "ellie-bitio/SQL Guide"."Birthday Party" AS party
  JOIN
  "ellie-bitio/SQL Guide"."allergies" AS allergies
  ON party."name" = allergies."name";

The ON statement tells SQL which columns to match together.

Voila! True to the name, we have joined the Birthday Party and allergies tables together. But wait! Where did Marcus go? And what about Tracy from the allergies table? This type of join is called an Inner Join, because on a Venn Diagram of the two tables, we only keep the entries that are in the middle.

👍

Types of Joins

Try out this same query with LEFT JOIN, RIGHT JOIN, and OUTER JOIN. Which would be appropriate in what circumstance?

Aggregation

We want to make sure we avoid the most severe allergens at our party. Let's start with a query to get the information we need:

SELECT party."name", "food", "severity" 
FROM 
    "ellie-bitio/SQL Guide"."Birthday Party" as party 
    INNER JOIN
  "ellie-bitio/SQL Guide"."allergies" as allergies
ON party."name" = allergies."name";

We have the information we need here, but it's a little difficult to work with. Wouldn't it be nice if we had a sorted list of allergens, ordered by the number of children with that allergy? We can do that with aggregation!

SELECT "food", COUNT("food") as "count"
FROM 
    "ellie-bitio/SQL Guide"."Birthday Party" as party 
    INNER JOIN
  "ellie-bitio/SQL Guide"."allergies" as allergies
ON party."name" = allergies."name"
GROUP BY "food"
ORDER BY "count" DESC;

This query is getting long, so focus on what's new:

1. The GROUP BY statement that tells SQL we're aggregating over foods
2. The COUNT("food") selector that tells SQL how to report repeats, and
3. The ORDER BY instruction, that tells SQL to present our allergens in descending order.

👍

Exercise

Instead of just getting the number of children with a particular allergy, get the sum of the severity of the allergies and order by that instead. You will need the SUM aggregation function!

Now we can rest easy knowing our birthday party is safe!