LogoLogo
  • FAC Curriculum
  • archive
    • Node & npm introduction
    • developer
      • Programming Paradigms
    • handbook
      • Asking for help
      • Coaching groups
      • Code review
      • Course rules
      • Debugging
      • Employment schedule and material delivered in FAC20
      • GitHub Workflow
      • Glossary or terms
      • Presentation Guidance
      • Equality, Diversity and Inclusion
      • Installation guide
      • Learning circles
      • Mentoring guidance
      • What to expect from your mentors
      • One-day projects
      • Pair programming
      • Portfolio
      • Questions for problem solving
      • Progress Logs
      • Final project report
      • Managing software projects
      • Project Presentations
      • Project roles
      • Projects
      • Retrospectives
      • Role Circles
      • Safeguarding policy
      • Technical Spikes
      • System requirements
      • Tech for Better
      • User Manuals
      • Wellbeing Support
      • project-docs
        • What makes a mentor?
        • Product Handover
        • Sprint Planning
        • Tech for Better Presentations
        • User Research & Usability Testing
    • foundation
      • full-stack
        • Learning Outcomes
        • project
      • testing
        • project
        • spikes
  • docs
    • Contributing to the curriculum
    • Curriculum intent
    • Curriculum process
  • src
    • About our curriculum
    • course
      • Code of Conduct
      • Docker
      • .NET and Umbraco
      • Getting started
      • Founders and Coders coursebook
      • KSB's
      • Mini projects
      • Revision checklist
      • Svelte
      • TypeScript
      • handbook
        • Software Developer Handbook
        • Software Foundation Handbook
      • precourse
        • Before you start the course
        • Installation List
      • syllabus
        • developer
          • app
            • learning-outcomes
            • project
            • schedule
            • spikes
          • introduction
            • learning-outcomes
            • project
            • resources
            • schedule
          • week00-pre-course
            • We'd like you to spend some time before starting the course working on useful fundamentals.
            • spikes
          • week01-project01-basics
            • Employability introduction
            • Homework
            • learning-outcomes
            • Week of September 9th
            • project
            • resources
            • schedule
            • spikes
          • week02-project02-chatbot
            • employability
            • Homework
            • learning-outcomes
            • Week of September 16th
            • project
            • resources
            • schedule
            • spikes
          • week03-project03-server
            • Learning Outcomes
            • Week of September 23th
            • The Amazin' Quizzer API Backend
            • resources
            • schedule
          • week04-project03-frontend
            • learning-outcomes
            • Week of September 30th
            • UI for Quizzer App
            • resources
            • schedule
          • week05-project03-test-deploy
            • Testing and deployment
            • Week of October 7th
            • project
            • resources
            • schedule
          • week06-project04-databases
            • learning-outcomes
            • Week of October 14th
            • project
            • Databases
            • schedule
          • week07-project04-authentication
            • Learning Outcomes
            • Week of October 21st
            • project
            • resources
            • schedule
          • week08-project04-test-deploy
            • Learning Outcomes
            • Week of October 28th
            • project
            • resources
            • schedule
          • week09-reading-week
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
          • week10-project05-DOTNET-intro
            • Learning Outcomes
            • overview
            • project
            • Resources
            • schedule
          • week11-project05-DOTNET-testing
            • Testing and deployment
            • Week of November 18th
            • project
            • Resources
            • schedule
          • week12-project05-DOTNET-deploy
            • Learning Outcomes
            • Week of November 25th
            • project
            • Resources
            • schedule
            • Spikes
          • week13-TFB-design
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
            • Design Week Spikes
          • week14-TFB-build
            • Learning Outcomes
            • overview
            • Project
            • DevOps Resources
            • schedule
            • Spikes
          • week15-TFB-build
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
            • Spikes
          • projects
            • in-house-design
              • Learning Outcomes
              • Project
              • Resources
              • schedule
              • Design Week Spikes
        • foundation
          • Obsolete-full-stack
            • project
          • post-course
            • Homework
            • schedule
        • portfolio
          • fruit-shop
            • learning-outcomes
            • project
            • resources
          • game
            • learning-outcomes
            • project
            • resources
          • hobby-page
            • learning-outcomes
            • project
            • resources
          • movie-data
            • learning-outcomes
            • project
            • resources
          • project-gallery
            • learning-outcomes
            • project
            • resources
          • website
            • learning-outcomes
            • project
            • JavaScript
        • tfb
          • week 1
            • Introduction (45 minutes)
            • Further reading
          • week 10
            • content
            • resources
          • week 11
            • What will we be doing this week?
            • resources
          • week 12
            • What will we be doing this week?
            • Further reading
          • week 2
            • Discover (90 minutes)
            • resources
          • week 3
            • content
            • resources
          • week 4
            • Mapping the user journey (90 minutes)
            • resources
          • week 5
            • Figma Workshop 1 (90 minutes)
            • Further reading
          • week 6
            • Figma Workshop 2 (90 minutes)
            • resources
          • week 7
            • Product pitches & Selection (90 minutes)
            • resources
          • week 8
            • content
            • resources
          • week 9
            • content
            • resources
    • learn
      • DOTNET
        • Introduction to .NET
      • auth
        • Authenticating web apps
      • database
        • Persisting data with SQLite and Node
      • dotnet-two
        • Dependency injections and interfaces in .NET
      • form-validation
        • Form validation
      • react
        • Building client-side apps with React
      • server
        • HTTP servers with Node & Express
      • typescript
        • TypeScript
    • mentoring
      • design-week
        • Analysis Workshop
        • Code planning
        • Definition Workshop
        • Discovery Workshop
        • Figma introduction
        • Usability testing
        • User Research
    • resources
      • http
        • introduction
    • workshops
      • cookie-auth
        • index
      • creating-promises
        • index
      • css-layout
        • index
      • cypress-testing
        • index
      • database-testing
        • index
      • dev-tooling
        • Developer tooling
      • dom-challenge
        • index
      • dom-rendering
        • index
      • es-modules
        • index
      • express-middleware
        • Express middleware
      • first-class-functions
        • index
      • form-validation
        • index
      • functions-callbacks-async
        • Functions, callbacks, & async JavaScript
      • git-intro
        • Introduction to Git
      • git-terminal
        • Using Git in the terminal
      • git-workflow
        • Git workflow
      • github-projects
        • GitHub Projects Workflow Workshop
      • heroku-sql-challenge
        • index
      • html-forms
        • index
      • learn-a11y
        • index
        • starter-files
          • solution
            • Accessibility solution explanation
      • learn-fetch
        • index
      • learn-integration-testing
        • index
      • learn-testing
        • Learn testing in JavaScript
      • learn-unit-testing
        • index
      • node-error-handling
        • Node error-handling
      • node-express-server
        • Node and Express HTTP server
      • node-npm-intro
        • Node & npm introduction
      • node-postgres
        • Learn Postgres with Node
      • node-scripting-challenge
        • index
      • password-security
        • index
      • promise-practice
        • index
      • react-components
        • React components
      • react-fetch
        • index
      • react-forms
        • React forms
      • react-refactor-classes
        • index
      • react-state-effects
        • React state & effects
      • real-world-fetch
        • index
      • scope-challenge
        • Scope debugging challenge
      • semantic-html
        • index
      • server-side-forms
        • Server-side forms
      • session-auth
        • Session authentication
      • sql-intro
        • index
      • tdd-array-methods
        • index
Powered by GitBook
On this page
  • Getting Started
  • Setting up the workshop database
  • The schema
  • Data types
  • Constraints
  • Our blog database
  • Retrieving data
  • SELECT
  • WHERE
  • AND, OR and NOT
  • IN
  • Challenge 1: retrieving data
  • Creating and updating data
  • INSERT INTO
  • UPDATE
  • RETURNING
  • Creating and updating data challenges
  • Combining tables
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • Combining tables challenges
  • Bonus: Sub queries
  • Add a comment to a post
Export as PDF
  1. src
  2. workshops
  3. sql-intro

index

Learn the fundamentals of using SQL to query a database

Previoussql-introNexttdd-array-methods

Last updated 3 years ago

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

Getting Started

Make sure you have .

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:

CREATE DATABASE blog_workshop;

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:

\connect blog_workshop

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):

\include init.sql

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:

Column

Type

Constraints

id

SERIAL

PRIMARY KEY

user_id

INTEGER

REFERENCES users(id)

text_content

TEXT

Retrieving data

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

SELECT

SELECT first_name FROM users;

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

first_name

Alisha

Chelsea

...

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

SELECT first_name FROM users WHERE id = 1;

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

first_name

Alisha

AND, OR and NOT

SELECT first_name FROM users WHERE id = 1 OR id = 2;

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

first_name

Alisha

Chelsea

IN

SELECT first_name FROM users WHERE id IN (1, 2);

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

first_name

Alisha

Chelsea

This is similar to the OR operator we saw above.

Challenge 1: retrieving data

  1. Select specific columns

    Expected Result

    username

    location

    Sery1976

    Middlehill, UK

    Notne1991

    Sunipol, UK

    Moull1990

    Wanlip, UK

    Spont1935

    Saxilby, UK

  2. Select users conditionally

    Expected Result

    id

    username

    age

    first_name

    last_name

    location

    3

    Moull1990

    41

    Skye

    Hobbs

    Wanlip, UK

    4

    Spont1935

    72

    Matthew

    Griffin

    Saxilby, UK

  3. Select users using multiple conditions

    Using SELECT and WHERE, retrieve the first, last name and location of the user who lives in Saxilby, UK and is older than 40.

    Expected Result

    first_name

    last_name

    location

    Matthew

    Griffin

    Saxilby, UK

  4. Select posts using multiple conditions

    Expected Result

    user_id

    text_content

    2

    Peculiar trifling absolute and wandered vicinity property yet. decay.

    3

    Far stairs now coming bed oppose hunted become his.

Creating and updating data

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

INSERT INTO

INSERT INTO users (username, first_name) VALUES ('oliverjam', 'oli');

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

UPDATE

UPDATE users SET first_name = 'oliver' WHERE username = 'oliverjam';

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.

INSERT INTO users (username, first_name) VALUES ('oliverjam', 'oli')
  RETURNING id, username;

Would return:

id

username

1

oliverjam

Creating and updating data challenges

  1. Adding a new post

    Expected Result

    text_content

    user_id

    Hello World

    1

  2. Updating an existing post

    You can then run SELECT user_id FROM blog_posts WHERE text_content='Hello World'; to test for the expected result.

    Expected Result

    user_id

    2

Combining tables

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

SELECT users.username, blog_posts.text_content
  FROM users INNER JOIN blog_posts
    ON users.id = blog_posts.user_id;

username

text_content

Sery1976

Announcing of invitation principles in.

Notne1991

Peculiar trifling absolute and wandered vicinity property yet. son.

Moull1990

Far stairs now coming bed oppose hunted become his.

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

LEFT JOIN

SELECT users.username, blog_posts.text_content
  FROM users LEFT JOIN blog_posts
    ON users.id = blog_posts.user_id;

username

text_content

Sery1976

Announcing of invitation principles in.

Notne1991

Peculiar trifling absolute and wandered vicinity property yet.son.

Moull1990

Far stairs now coming bed oppose hunted become his.

Spont1935

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

RIGHT JOIN

Combining tables challenges

  1. Selecting users and comments

    Expected Result

    location

    text_content

    Middlehill, UK

    Sunipol, UK

    Great blog post!

    Wanlip, UK

    Saxilby, UK

  2. Selecting blog posts and comments

    Expected Result

    text_content

    text_content

    Far stairs now coming bed oppose hunted become his.

    Great blog post!

  3. Bonus: select the user who made a comment

    Expand your previous solution to also include the username of the user who made each comment.

    Expected Result

    text_content

    text_content

    username

    Far stairs now coming bed oppose hunted become his.

    Great blog post!

    Notne1991

Bonus: Sub queries

You can nest SQL expressions. For example:

SELECT * FROM dogs WHERE owner = (SELECT name FROM humans WHERE id = 1)

is the equivalent of:

SELECT * FROM dogs WHERE owner = 'oli';

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

text_content

Interesting post

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

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

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

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

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

Using SELECT and , retrieve every column for all users who are older than 40.

Using WHERE and , retrieve the user ID and text content columns for posts created by users with IDs of 2 or 3.

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).

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.

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.

Using , update the blog post from the previous question to change the author to the user with ID 2. Make sure you don't change any other posts.

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.

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:

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):

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.

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

Using select only blog posts with comments, returning the text_content of the blog posts and the text_content of the comments.

installed and set up PostgreSQL
SELECT
WHERE
AND, OR and NOT
IN
SELECT
WHERE
IN
INSERT INTO
UPDATE
INSERT INTO
UPDATE
JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN
LEFT JOIN
INNER JOIN
Venn diagram of an inner join—only the overlap of two circles is highlighted
Venn diagram of a left join—the left circle and overlap with the right circle is highlighted
Venn diagram of a left join—the right circle and overlap with the left circle is highlighted