What you'll learn:
SQL is the standard language for relational database management systems. You will learn the basic syntax and practice writing SQL queries.
Prerequisites:
None
Tutorial:
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:
| Code Block | ||
|---|---|---|
| ||
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:
| 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:
| Code Block | ||
|---|---|---|
| ||
UPDATE person SET country_id = 2 WHERE person_name = 'Lucy'; |
| Code Block | ||
|---|---|---|
| ||
UPDATE person
SET country_id = 1
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 online resources:
| 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/ |