In this workshop we will be learning SQL by running commands in our terminal.

Getting Started

Make sure you have installed and set up PostgreSQL.

We'll be using psql, the Postgres command-line interface. This lets you run SQL queries and also provides some extra commands for working with the database. These extras start with a backslash character (e.g. \c) whereas SQL is usually uppercase (e.g. CREATE DATABASE).

Setting up the workshop database

Download the starter files and cd into the directory. Type psql in your terminal to enter the Postgres command-line interface. You can type ctrl + d to exit this at any time.

To create a database use the CREATE DATABASE command and give it whatever name you like:

You should now be able to use \list to list all the databases on your machine. Hopefully the new blog_workshop is there. You can type q to exit this view.

You can then connect to the new database using the \connect:

Now you need to populate the database with some data. The init.sql file contains a bunch of SQL commands. They create some tables and then insert data into them.

You can use \include to run some SQL directly from a file (which saves a lot of typing):

If you run \dt you should see all the database tables we just created (blog_posts, blog_comments and users).

The schema

A "schema" represents all the different things in a database. It says what type of data goes in each column, what columns are in each table, and how tables relate to each other. The schema is represented by the initial SQL used to create the tables (here inside the init.sql file).

Data types

SQL requires us to specify what type of data we're going to use for each entry in advance. Here's a small subset of available types:

SERIAL

An auto-incrementing number. Useful for IDs where each new entry needs a unique value. SQL will automatically create this when you inser an entry.

VARCHAR(255)

A variable-length string. The number in brackets specifies the maximum number of characters.

TEXT

A string of any length.

INTEGER

A whole number (like 20). No fractions allowed.

Constraints

A way to provide additional fine-tuning of a data type. Think of it like input validation. Here are a few useful constraints:

NOT NULL

This value is required and must always be set.

PRIMARY KEY

This value is the unique identifier for this entry into the table. Often a SERIAL so you don't have to worry about creating unique IDs yourself.

REFERENCES

This value must match one in another table, like users(id). Used to link tables together so you can find related information (e.g. which user wrote this blog post).

Our blog database

This specific database represents a blog site. It has users who can write blog posts, and blog posts that can contain comments.

A blog post has to have an author, so each entry in blog_posts has a user_id, which REFERENCES an id in the users table. This links the two together, so for any given post we can always find the author.

Comments are linked to both a user and a blog_post, so they have two REFERENCES: post_id and user_id.

Here is the example schema for the blog_post table:

Retrieving data

Here's a quick overview of some SQL commands used to retrieve data from a database.

SELECT

retrieves data from a table. You need to combine it with FROM to specify which table. For example:

would retrieve the first_name column for every row in the users table.

Note you can provide comma-separated lists of column names and table names if you want to select multiple things. You can also use the * character to select all columns.

WHERE

is a clause that qualifies a SELECT. It lets you filter which rows are retrieved based on the values in that row. For example:

would retrive the first name column for any users with an ID of 1.

AND, OR and NOT

are operators for expressing logic in your WHERE clauses. They let you apply multiple conditions. For example:

would retrieve the first name column for any users with an ID of 1 or 2.

IN

The operator lets you match against a list of values in your WHERE clause. For example:

would select the first name column for any users with an ID of 1 or 2.

This is similar to the OR operator we saw above.

Challenge 1: retrieving data

1. Select specific columns

   Using , retrieve a list of only usernames and locations from the users table

   Expected Result

Creating and updating data

Here's an overview of SQL commands used to add data to a database.

INSERT INTO

lets you add a new row into a table. You specify a table name and list of columns, then a list of values to insert. The values have to match positions with their respective columns (like function arguments in JS).

would create a new user row with a username of 'oliverjam' and first name of 'oli'.

UPDATE

lets you change existing data in a table. You provide the table name, then the name and new value of each column. You also need to provide a WHERE clause to select which rows to update, otherwise every row will be changed.

would update the first name of the user with username "oliverjam" to be "oliver".

RETURNING

You can access the created/changed rows with a RETURNING clause after your INSERT or UPDATE. This lets you specify which columns you want back. This saves you doing a whole extra SELECT after an insert just to get the new entry's ID.

Would return:

Creating and updating data challenges

1. Adding a new post

   Using and RETURNING, add a blog post with the text "Hello World" to the user with ID 1. Return the text content and user ID of the inserted post.

   Expected Result

Combining tables

We can use s to select columns from multiple tables at once, based on a relation they share. Joins effectively combine multiple tables into one temporary table for you to query.

There are different types of joins that determine exactly what data is returned. Since we're selecting from multiple tables we namespace our columns with the table name and a ., just like object access in JavaScript (e.g. SELECT users.username, blog_posts.text_content).

INNER JOIN

selects rows that have matching values in both tables being selected from. For example if we wanted to select all the users who have blogposts, then get their usernames and their blog posts' text content:

INNER JOIN returns only the the users that have blog posts.

LEFT JOIN

selects every entry in the first table you name, but only matched records from the second. For example if we wanted a list of every user, plus their blog posts' text content (if they have any):

LEFT JOIN selects one extra row here compared to INNER JOIN: the final user "Spont1935" who has no blog posts.

RIGHT JOIN

is like the opposite of LEFT JOIN. With our blog post data the result would be the same as an INNER JOIN, since every post must have an author.

Combining tables challenges

1. Selecting users and comments

   Using select every user's location, plus the content of any comments they've made.

   Expected Result

Bonus: Sub queries

You can nest SQL expressions. For example:

is the equivalent of:

if there's a human with ID 1 and name 'oli'. The nested query is resolved first, similar to using brackets in maths.

Add a comment to a post

Add a new comment to the post_comments table. It should have a user ID of 3 and text content 'Interesting post'. The comment should be linked to whichever post has text content of 'Peculiar trifling absolute and wandered vicinity property yet.' (i.e. its post_id should be the ID of that post).

You can then run SELECT text_content FROM post_comments WHERE post_id = 2; to test for the expected result.

Expected Result