What is SQL:
SQL (Structured Query Language) is the standard language for managing data in relational database 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:
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'); |
Step 4 - Query Data
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; |
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/ |