Table of Content

Purpose

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 limit until the database is refreshed.

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');

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;