Table of Content

Purpose

Step 0 - Project Setup

We will use the following site to test SQL commands: https://sqliteonline.com/

Connect to the 'PostgreSQL' database. Note that this site has a 15 minute idle limit.

Step 1 - Create Tables for Tutorial

Create the following two tables for this demo:

CREATE TABLE person ( id INTEGER PRIMARY KEY, person_name VARCHAR(100) );
CREATE TABLE country ( id INTEGER PRIMARY KEY, country_name VARCHAR(100) );

For more information on creating tables: https://www.postgresql.org/docs/9.2/sql-createtable.html

Step 2 - Insert Data into the Tables

Insert data into the 'person' table:

INSERT INTO person (id, person_name) VALUES (1, 'Sally');
INSERT INTO person (id, person_name) VALUES (2, 'Bob');
INSERT INTO person (id, person_name) VALUES (3, 'Lucy');

Insert data into the 'country' table:

INSERT INTO country (id, country_name) VALUES (1, 'Canada'), (2, 'USA'), (3, 'Mexico');

Step 3 - Update

Update the person table to have a column for the person's country:

ALTER TABLE person ADD column country_id integer;

Update the data:

UPDATE person
SET country_id = 2
WHERE person_name = 'Lucy';

UPDATE person
SET country_id = 1
WHERE person_name in ('Sally', 'Bob');

Step 4 - Query Data

Basic Select Statement

-- select all data from person table

select * from person;

Select with Filter

-- filter data from person table by name

select * from person where person_name = 'Lucy';

Select with Join Condition

-- join data between person and country tables

select person_name, country_name from person
inner join country on person.country_id = country.id;

Select with Group By clause

select country_name, count(*) from person
inner join country on person.country_id = country.id
group by country_name;

select country_name, count(*) from person
inner join country on person.country_id = country.id
group by country_name
having count(*) > 1;

Step 5 - Delete Data

-- delete row from country table where the ID of the country = 3

delete from country where id = 3;

Additional References

Name	Reference
Postgres cheatsheet with common commands	https://www.postgresqltutorial.com/postgresql-cheat-sheet/
Postgres tutorial	https://www.postgresqltutorial.com/
Postgres documentation	https://www.postgresql.org/docs/9.4/

Page tree

Introduction to SQL