Introduction to SQL

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;

Join Types

There are 3 types of joins that are available in SQL.  The following is a good diagram that explains the results from each join type:  The diagram and more information can be found here: https://www.w3schools.com/sql/sql_join.asp

Inner Join

select * from person inner join country on person.country_id = country.id;

Return records that are matching in both employee and country table.

Left Join

select * from person left join country on person.country_id = country.id;

Returns all records from the employee table and only the matching records from the country table.

Right Join

# modify some data to get some results
UPDATE person
SET country_id = 4  # note that this country ID doesn't exist in the country table.
WHERE person_name = 'Bob';

# now do a right join
select * from person right join country on person.country_id = country.id;

Returns only matching records from the employee table and all records from the country table.

Full Outer Join

select * from person outer join country on person.country_id = country.id;

Returns all records from employee and country table.

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:

select name, age from employee;

select * from employee;

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:

Contacts:

• adeel.ansari@camh.ca