What
you'll learnis SQL:
SQL is (Structured Query Language) is the standard language for managing data in relational database management managements systems (DBMS). It can be used to Create, Read, Update, and Delete data within the databases.
What you'll learn:
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; |
Performance Tuning
There are ways to optimize performance of queries by reducing the amount of data being processed or by using SQL keywords that are optimized for certain processes. Here are a few examples:
| Description | Good | Bad |
|---|---|---|
| Select particular columns to speed up performance | select name, age from employee; | select * from employee; |
| Reduce amount of data being processed in join clauses | select * from (select employee_id, country_id from employee where join_date > '2020-01-01') t1 inner join (employee_id, salary from compensation where comp_date > '2020-01-01') t2 on t1.employee_id = t2.employee_id; | select * from employee t1 inner join compensation t2 on t1.employee_id = t2.employee_id where join_date > '2020-01-01' and comp_date > '2020-01-01'; |
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/ |