Introduction

Writing custom SQL queries is not something I have to do often, especially when most my database querying can be easily written with Rail’s Active Record library. However, every now and then, whether for performance or just utility, I need to write some custom SQL. Since those times are few and far in-between, I’m going to write a helpful four-part series with the basics: setting up tables, simple queries, aggregate functions, and joining tables. Here we go!

Database Table Basics

A database table looks very similar to an excel spreadsheet. You have rows and columns. Columns contain information about your entries and rows contain each individual entry. Each row should start with a unique ID that we use to identify the entry, called the Primary Key. The primary key is always an integer. There are many other data types available to us, aside from integers, some basic ones are: text, booleans, and dates.

Creating a table

If I wanted to create a table for all the pets my friends and I have, we’d want to have a couple tables. First and owners table that would just have an id, and a name for each person. Second we’d want to have a pets table with the name, age, adoption_date, and friendly columns. Here’s the SQL to create those tables:

The owner’s table:

1
2
3
4

CREATE TABLE owners (
id INTEGER PRIMARY KEY,
name TEXT
);

The pet’s table:

1
2
3
4
5
6
7

CREATE TABLE pets (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
adoption_date DATE,
friendly BOOLEAN
);

Quick thing to notice is that each command in SQL is concluded with a semi-colon. Let’s add some pets and owners:

The owner’s table:

1

INSERT INTO owners (name) VALUES ('Sammy'), ('Sally'), ('Sarah');

The pet’s table:

1

INSERT INTO pets (name, age, adoption_date, friendly) VALUES ('Spot', 3, 2015-01-15, 'true'), ('Scruffy', 7, 2012-06-01, 'true'), ('Slappy', 2, 2017-01-01, 'false');

Now you’ll notice we haven’t stored any indication of who owns which pet. I could edit pets table to include an owners column like this:

1

ALTER TABLE pets ADD owner_id INTEGER

However, what happens when I want to share a pet with Sally? We can’t have an array of owner ids in the owners column on the pets table. The solution is to create a new table that stores those relationships. Each entry will represent a pet to owner relationship with a primary key, a owner_id column, and a pet_id column. That means that if Sally and I share a pet, there will be an entry for her relationship with the pet and an entry with my relationship with the pet. This is called a join table.

Join table:

1
2
3
4
5

CREATE TABLE owners_pets (
id INTEGER PRIMARY KEY,
owner_id INTEGER,
pet_id INTEGER
);

Now let’s add some relationships:

1

INSERT INTO owners_pets (owner_id, pet_id) VALUES (1, 1)(2, 1)(2, 2)(3, 3);

Conclusion

We’ve built three tables and populated them with some info, all in bespoke, handwritten SQL! Great job. Our next post will deal with querying those tables to get back some information.