Versions Compared

Old Version 14

changes.mady.by.user Unknown User (_nikola_bogetic)

Saved on

compared with

New Version 15

changes.mady.by.user Unknown User (_nikola_bogetic)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Info
titleContact Information

For questions regarding the tutorial, please contact Adeel Ansari


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
languagesql
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
languagesql
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
languagesql
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
languagesql
ALTER TABLE person ADD column country_id integer;


Update the data:

Code Block
languagesql
UPDATE person
SET country_id = 2
WHERE person_name = 'Lucy';


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


Step 4 - Query Data

Basic Select Statement

Code Block
languagesql
-- select all data from person table

select * from person;

Select with Filter

Code Block
languagesql
-- filter data from person table by name

select * from person where person_name = 'Lucy';

Select with Join Condition

Code Block
languagesql
-- 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
languagesql
select country_name, count(*) from person
inner join country on person.country_id = country.id
group by country_name;


Code Block
languagesql
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
languagesql
-- delete row from country table where the ID of the country = 3

delete from country where id = 3;



Additional References

NameReference
Postgres cheatsheet with common commandshttps://www.postgresqltutorial.com/postgresql-cheat-sheet/
Postgres tutorialhttps://www.postgresqltutorial.com/
Postgres documentationhttps://www.postgresql.org/docs/9.4/