SQL: A survey for non-developers

As a web developer, I often hear from non-developer friends that they wish they had a better understanding of SQL. SQL, which stands for Structured Query Language, is a language used to interact with the databases. In this post, I’ll give an overview of database tables and SQL queries by discussing the way we set up contractor certifications at RigUp.

Tables and columns

Learning about SQL databases and tables for the first time can seem daunting. You might think that you’ll end up wanting to do this:

But never fear! SQL tables are actually a lot like something that you’re probably already familiar with: Excel spreadsheets! In our SQL-Excel analogy, each row of the table or spreadsheet corresponds with one record of whatever we’re storing, whereas each column corresponds with an attribute of the object.

At RigUp, we keep track of which certifications contractors have completed. Each certification has a name, so we’ll have a name column in our certifications table. Also, each certification will have a unique ID number to represent it in the table. So our certifications table ends up looking something like:

╔════╦════════════════╗
║ id ║      name      ║
╠════╬════════════════╣
║  1 ║ Safeland Basic ║
║  2 ║ Safeland Core  ║
║  3 ║ RigPass        ║
╚════╩════════════════╝

Easy enough! We also have a table for users, with user names and emails.

╔════╦══════════════════╦═══════════════════════╗
║ id ║       name       ║         email         ║
╠════╬══════════════════╬═══════════════════════╣
║  1 ║ Harry Potter     ║ hpotter@hogwarts.org  ║
║  2 ║ Ron Weasley      ║ rweasley@hogwarts.org ║
║  3 ║ Hermione Granger ║ hgranger@hogwarts.org ║
╚════╩══════════════════╩═══════════════════════╝

In our RigUp application, our data modeling gets a bit more complex, but we’ll stick with these two tables in this example. Let’s call the tables certifications and users, respectively.

Querying

To retrieve records with SQL, we ask for the columns that we want and specify the table. For example, if we want the ID numbers and names of the users, we would write:

SELECT id, name FROM users;

This would return:

╔════╦══════════════════╗
║ id ║       name       ║
╠════╬══════════════════╣
║  1 ║ Harry Potter     ║
║  2 ║ Ron Weasley      ║
║  3 ║ Hermione Granger ║
╚════╩══════════════════╝

A quick way to ask for all columns from a table is to write SELECT *. So to retrieve all columns from the certifications table, we write:

SELECT * FROM certifications;

The semicolon at the end signifies the end of the SQL statement; don’t forget it!

QUERYING WITH CONDITIONS

We can also add conditions to our SQL queries. Let’s say we want to grab all of the columns for the user with the email address hgranger@hogwarts.org. We do this by adding a WHERE clause:

SELECT * FROM users WHERE email = 'hgranger@hogwarts.org';

The query above returns:

╔════╦══════════════════╦═══════════════════════╗
║ id ║       name       ║         email         ║
╠════╬══════════════════╬═══════════════════════╣
║  3 ║ Hermione Granger ║ hgranger@hogwarts.org ║
╚════╩══════════════════╩═══════════════════════╝

We can also query for patterns using the LIKE operator. When we use LIKE, the % character represents zero or more characters. So if we want to search for any certifications with "Safeland" in the name, we would search for any names LIKE '%Safeland%':

SELECT * FROM certifications WHERE name LIKE '%Safeland%';

That returns:

╔════╦════════════════╗
║ id ║      name      ║
╠════╬════════════════╣
║  1 ║ Safeland Basic ║
║  2 ║ Safeland Core  ║
╚════╩════════════════╝

We can also add more than one condition with the AND operator:

SELECT * FROM certifications WHERE name LIKE '%Safeland%' AND id > 1;

╔════╦═══════════════╗
║ id ║     name      ║
╠════╬═══════════════╣
║  2 ║ Safeland Core ║
╚════╩═══════════════╝

Join Tables

One of the most useful aspects of SQL is its ability to associate separate tables. In our example, a list of certifications becomes much more valuable once we are able to keep track of which users have completed which certifications. In order to link together two tables, we can use join tables.

Since the tables we want to link are called certifications and users, the columns on our join table will be certification_id and user_id.

Say we want to store the fact that Harry Potter has completed the RigPass certification. If we look at our users table, we'll notice that Harry Potter's unique ID number in the userstable is 1. Now look at the certifications table. RigPass's unique ID number is 3.

So, our record in the join table will have a certification_id of 3 and a user_id of 1:

╔════╦══════════════════╦═════════╗
║ id ║ certification_id ║ user_id ║
╠════╬══════════════════╬═════════╣
║  1 ║                3 ║       1 ║
╚════╩══════════════════╩═════════╝

Likewise, we can store the fact that Hermione also has completed the RigPass certification by adding a record where the certification_id is 3 and the user_id is 3.

╔════╦══════════════════╦═════════╗
║ id ║ certification_id ║ user_id ║
╠════╬══════════════════╬═════════╣
║  1 ║                3 ║       1 ║
║  2 ║                3 ║       3 ║
╚════╩══════════════════╩═════════╝

Let’s say our full list of user-certification pairs is as follows:

• Harry Potter: Safeland Basic, RigPass
• Ron Weasley: Safeland Core
• Hermione Granger: Safeland Core, RigPass

Our join table looks like this:

╔════╦══════════════════╦═════════╗
║ id ║ certification_id ║ user_id ║
╠════╬══════════════════╬═════════╣
║  1 ║                1 ║       1 ║
║  2 ║                3 ║       1 ║
║  3 ║                2 ║       2 ║
║  4 ║                2 ║       3 ║
║  5 ║                3 ║       3 ║
╚════╩══════════════════╩═════════╝

Notice that the number of records in our join table is the name as the number of user-certification combinations!

USING SQL INNER JOINS

However, join tables on their own aren’t very readable — they’re 3 columns of ID numbers, and most of the time we want to look at parsable text. That’s where SQL comes in!

If we had to list all of Harry Potter’s certifications, how would we go about figuring out that information? Probably through a series of steps:

• Look up Harry Potter’s unique ID (which is 1) in the users table.
• Find all user_certifications records with the user_id of 1.
• For each user_certifications record, find the certification with the unique ID that matches the certification_id.

We will use SQL’s INNER JOIN keyword to link the columns in our join table to the corresponding IDs in the other tables:

SELECT * FROM users
  INNER JOIN user_certifications ON 
    users.id = user_certifications.user_id
  INNER JOIN certifications ON 
    user_certifications.certification_id = certifications.id;

The query above returns the following table:

We can see that we’ve still returned one row for each user_certifications record, but this time we get all of the data from the users and certifications tables as well.

But we might not need all of the ID numbers; let’s just grab the user ID’s, the user names, and the certification names:

SELECT users.id, users.name, certifications.name FROM users
  INNER JOIN user_certifications ON 
    users.id = user_certifications.user_id
  INNER JOIN certifications ON 
    user_certifications.certification_id = certifications.id;
╔════╦══════════════════╦════════════════╗
║ id ║       name       ║      name      ║
╠════╬══════════════════╬════════════════╣
║  1 ║ Harry Potter     ║ Safeland Basic ║
║  1 ║ Harry Potter     ║ RigPass        ║
║  2 ║ Ron Weasley      ║ Safeland Core  ║
║  3 ║ Hermione Granger ║ Safeland Core  ║
║  3 ║ Hermione Granger ║ RigPass        ║
╚════╩══════════════════╩════════════════╝

QUERYING WITH JOINS

You can also use a SQL query to filter a results set after a join. If we go back to our question of which certifications Harry Potter has completed, we only have to slightly modify our last SQL statement:

SELECT users.id, users.name, certifications.name FROM users
  INNER JOIN user_certifications ON 
    users.id = user_certifications.user_id
  INNER JOIN certifications ON 
    user_certifications.certification_id = certifications.id
  WHERE users.name = 'Harry Potter';
╔════╦══════════════╦════════════════╗
║ id ║     name     ║      name      ║
╠════╬══════════════╬════════════════╣
║  1 ║ Harry Potter ║ Safeland Basic ║
║  1 ║ Harry Potter ║ RigPass        ║
╚════╩══════════════╩════════════════╝

Wow! It’s like magic!

Practicing on your computer

If you want to play with SQL on your Mac, complete the following steps:

1. Install and run the Postgres application. Click Start to make sure a server is running.
2. Open Terminal.
3. Copy and paste the following into Terminal:

psql
CREATE DATABASE blog;
\connect blog;
CREATE TABLE certifications (
  id serial primary key,
  name varchar(250)
);
CREATE TABLE users (
  id serial primary key,
  name varchar(250),
  email varchar(250)
);
CREATE TABLE user_certifications (
  id serial primary key,
  certification_id integer,
  user_id integer
);
INSERT INTO certifications (name)
  VALUES ('Safeland Basic'),
  ('Safeland Core'),
  ('RigPass');
INSERT INTO users (name, email)
  VALUES ('Harry Potter', 'hpotter@hogwarts.org'),
  ('Ron Weasley', 'rweasley@hogwarts.org'),
  ('Hermione Granger', 'hgranger@hogwarts.org');
INSERT INTO user_certifications (certification_id, user_id)
  VALUES (1, 1),
  (3, 1),
  (2, 2),
  (2, 3),
  (3, 3);

Now you can run any of the SQL commands in this tutorial in your Terminal! To quit Postgres, run \q. To re-enter your blog database, run psql blog.

Soon, you’ll be a pro at fetching information with SQL!