Intro to SQL & PostgreSQL

Relational Databases

A relational database stores data in a row-based table structure (much like a spreadsheet) as opposed to a document-oriented database like MongoDB. Data from one table can relate to another, for example a table of users, and a table of cities, where you link a city from the cities table to each user in the users table.

Examples of Relational DataBase Management Systems (RDBMS):

• PostgreSQL

• MySQL

• SQLite

• Oracle DB

For this class, we will be using PostgreSQL.

Structured Query Language (SQL)

SQL is the language you use to talk to a relational database. Most RDBMSes will include language extensions specific to them but they all implement the same standard "base" language which is what we'll focus on.

Syntax

UPDATE dogs SET pats = pats + 1 WHERE id = 42;

The snippet above is a complete SQL statement. Statements are terminated with a semicolon ; and are comprised of:

Keywords	Words defined in the language	UPDATE, SET, WHERE
Identifiers	Names of tables, columns, etc.	dogs, pats, id
Clauses	Constituent components of a full statement	UPDATE dogs, SET pats = pats, WHERE id = 42
Predicates	Specifies conditions that evaluate to a Boolean	id = 42
Expressions	Results in a value	pats + 1, 42

NOTE Even though keywords in SQL are not case sensitive, the convention is to capitalize them.

• Comments start with a --

• Use single quotes for string values

Connect to PostgreSQL

Make sure your PostgreSQL service is running

brew services start postgresql
# or
sudo systemctl start postgresql.service

List all databases

psql -l

If you want to create a new database

createdb learn_sql

Connect to the server and database

psql learn_sql

Delete a database

dropdb learn_sql

Create and Delete a Database from inside the psql shell

Create a new database

CREATE DATABASE test;

Connect to the new database

\c test

Delete database (NOTE: you can't drop a database you're currently connected to)

DROP DATABASE test;

PostgreSQL Data Types

The most common data types are

int	whole numbers
decimal	floats
bool	boolean
varchar(n)	small text
text	big text
timestamptz	date

Data types restrict what kind of data is allowed in a table.

Create and Delete Tables

Create a new table foo with a column name which will store text data with a maximum of 20 characters

CREATE TABLE foo ( name VARCHAR(20) );

See your new table

\dt
\d foo

Delete the table

DROP TABLE foo;

Create a better table

CREATE TABLE actors (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20),
    height DECIMAL,
    weight DECIMAL,
    sings BOOL,
    dances BOOL DEFAULT false
);

\d actors

CRUD

CRUD is an acronym that describes the four essential database operations: Create, Read, Update, Delete.

Operation	SQL
Create	INSERT
Read	SELECT
Update	UPDATE
Delete	DELETE

Insert Data

NOTE You don't have to remember the order of the columns that you created, but you do have to match the order in the insert statement.

Create a row in the actors table

INSERT INTO
    actors ( height, weight, first_name, sings, last_name, dances )
VALUES
    ( 162 , 57, 'Florence' , false, 'Pugh', true );

You also don't have to enter all the fields (only the ones required NOT NULL)

INSERT INTO actors (first_name) VALUES ('Sting');

You can also insert multiple rows in one statement

INSERT INTO
    actors (first_name, last_name, height, weight, sings, dances)
VALUES
    ('Melissa', 'Benoist', 173, 55, true, false),
    ('Nicole', 'Maines', 170, 54, true, true);

Moar Data

Let's copy paste a few more actors so we can play around with more data.

INSERT INTO actors (first_name, last_name, height, weight, sings, dances) VALUES
('Brandon', 'Routh', 189, 98, false, false) 
('Dominic', 'Purcell', null, 100, null, null)
('Victor', 'Garbor', null, null, true, null)
('Nick', 'Zano', 183, 80, null, null)
('Amy Louise', 'Pemberton', 160, 50, null, null),
('Maisie', 'Richardson-Sellers', null, null, null, null),
('Franz', 'Drameh', 180, 60, null, null),
('Tala', 'Ashe', 168, 47, null, null),
('Arthur', 'Darvill', null, null, null, null),
('Jess', 'Macallan', 175, 80, false, true),
('Matt', 'Ryan', 180, 92, true, true),
('Adam', 'Tsekhman', null, null, null, null),
('Courtney', 'Ford', 165, 42, null, null),
('Neil', 'McDonough', null, 39, true, true),
('Ramona', 'Young', null, null, null, null),
('Melissa', 'McCarthy', 157, 90, true, true),
('Prince', null, null, 48, true, true),
('Jenny', 'McCarthy', null, null, false, false);

Select Data

 -- select all rows from the actors table. display all columns
SELECT * FROM actors;

-- select all rows from the actors table. display only the first_name column
SELECT first_name FROM actors;

-- select all rows from the actors table. display the first_name and last_name columns
SELECT first_name, last_name FROM actors;

-- select all rows from the actors table where the name column is set to 'Tala'
SELECT * FROM actors WHERE first_name = 'Tala';

-- select all rows from the actors table where the height column is set to 180
SELECT * FROM actors WHERE height = 180;

-- select all rows from the actors table where the sings column is set to true
SELECT * FROM actors WHERE sings = true;

-- select all rows from the actors table where the height column is greater than 165
SELECT * FROM actors WHERE height > 165;

-- select all rows from the actors table where the height column has no value
SELECT * FROM actors WHERE height IS NULL;

-- select all rows from the actors table where the height column has any value
SELECT * FROM actors WHERE height IS NOT NULL;

-- select all rows from the actors table where the name column contains 'Ma'
SELECT * FROM actors WHERE first_name LIKE '%Ma%';

Update Data

-- set the height column to 181 for every row that has the id column set to 2
UPDATE actors SET height = 181 WHERE id = 2;

-- increment Florence Pugh's height by 1
UPDATE actors SET height = height + 1 WHERE first_name = 'Florence';

-- set the height column to 170 for every row where the height column is less than 170
UPDATE actors SET height = 170 WHERE height < 170;

Delete Data

-- delete all rows from the actors table that have the id column set to 13
DELETE FROM actors WHERE id = 13;

BE CAREFUL! DON'T WRITE A DELETE STATEMENT WITHOUT A WHERE CLAUSE