# 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](https://www.postgresql.org/).

#### 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**

```sql
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

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

List all databases

```sh
psql -l
```

If you want to create a new database

```sh
createdb learn_sql
```

Connect to the server and database

```sh
psql learn_sql
```

Delete a database

```sh
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

```sql
CREATE TABLE foo ( name VARCHAR(20) );
```

See your new table

```sql
\dt
\d foo
```

Delete the table

```sql
DROP TABLE foo;
```

Create a better table

```sql
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: **C**reate, **R**ead, **U**pdate, **D**elete.

| 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

```sql
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*)

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

You can also insert multiple rows in one statement

```sql
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.

```sql
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

```sql
 -- 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

```sql
-- 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

```sql
-- 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**