Table of Content
| Table of Contents |
|---|
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:
| Code Block | ||
|---|---|---|
| ||
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| Code Block | ||
|---|---|---|
| ||
INSERT INTO person (id, person_name) |
VALUES (1, 'Sally'); |
Insert into person
INSERT INTO person (id, person_name) |
VALUES (2, 'Bob'); |
Insert into person
INSERT INTO person (id, person_name) |
VALUES (3, 'Lucy'); |
Insert data into the 'country' table:
Insert into country| Code Block | ||
|---|---|---|
| ||
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:
| Code Block | ||
|---|---|---|
| ||
ALTER TABLE person ADD column country_id integer; |
Update the data:
update personset
| Code Block | ||
|---|---|---|
| ||
UPDATE person SET country_id = 2 |
where
WHERE person_name = 'Lucy'; |
|
| Code Block | ||
|---|---|---|
| ||
UPDATE person
SET |
country_id = 1 |
where
WHERE person_name in ('Sally', 'Bob'); |
Step 4 - Query Data
Basic Select Statement
| Code Block | ||
|---|---|---|
| ||
-- select all data from person table |
select * from person; |
Select with Filter
| Code Block | ||
|---|---|---|
| ||
-- filter data from person table by name |
select * from person where person_name = 'Lucy'; |
Select with Join Condition
| Code Block | ||
|---|---|---|
| ||
-- 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
| Code Block | ||
|---|---|---|
| ||
select country_name, count(*) from person |
inner join country on person.country_id = country.id |
group by country_name; |
| Code Block | ||
|---|---|---|
| ||
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
| Code Block | ||
|---|---|---|
| ||
-- 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/ |