Intro to SQL
Databases, Finally

Objectives
- Describe what a Relational Database is
- Describe what SQL is used for
- CRUD with SQL
- Define a Schema with SQL

You'll revisit these!
Relational Databases

What are they?
What makes them "relational"?

Relational Databases

Tables
ID | Name | Phone | Zip | |
---|---|---|---|---|
1 | Liz | liz@liz.com | 555-2323 | 94110 |
2 | Tyler | tyler@teb.com | 555-3232 | 94110 |
3 | Matt | matt@williams.com | 555-4455 | 94110 |
Relational Databases

Relationships
ID | Name | Phone | |
---|---|---|---|
1 | Liz | liz@liz.com | 555-2323 |
2 | Tyler | tyler@teb.com | 555-3232 |
3 | Matt | matt@williams.com | 555-4455 |
ID | User_ID | Zip |
---|---|---|
1 | 1 | 94110 |
2 | 1 | 94102 |
3 | 2 | 94110 |
4 | 2 | 94114 |
Objectives

- Describe what a Relational Database is
- Describe what SQL is used for
- CRUD with SQL
- Define a Schema with SQL
Structured Query Language
Create | INSERT |
---|---|
Read | SELECT |
Update | UPDATE |
Delete | DELETE |
Structured Query Language

Structured Query Language
SELECT id, name, email, phone FROM users;
SELECT [ a list of column names] FROM [a table name]
ID | Name | Phone | Zip | |
---|---|---|---|---|
1 | Liz | liz@liz.com | 555-2323 | 94110 |
2 | Tyler | tyler@teb.com | 555-3232 | 94110 |
3 | Matt | matt@williams.com | 555-4455 | 94110 |
Getting Data Out
Structured Query Language
SELECT id, name, email, phone FROM users
WHERE id = 1;
SELECT [ a list of column names] FROM [a table name]
WHERE [some condition is true]
ID | Name | Phone | Zip | |
---|---|---|---|---|
1 | Liz | liz@liz.com | 555-2323 | 94110 |
Specific Data Out
Structured Query Language
INSERT INTO users (id, name, email, phone)
VALUES (5, 'Aaron', 'aa@ron.com', '555-2156');
INSERT INTO [ a table name ] ([a list of column names])
VALUES ([comma-separated expressions in the same order as the column names])
Putting Data In
Objectives

- Describe what a Relational Database is
- Describe what SQL is used for
- CRUD with SQL
- Define a Schema with SQL
Reflect

What does CRUD stand for?
How do we filter queries?
How do we get data into the database?
Data Modeling
CREATE TABLE pokemon (
id SERIAL PRIMARY KEY,
name TEXT,
pokedex_number VARCHAR(10),
type1 TEXT,
type2 TEXT
);
Defining A Table
Data Modeling
Data Types
- Serial
- Integer
- Numeric
- Text, Varchar
- JSON
- BLOB
- Timestamp
- Boolean (True or False)
Think, Pair, Share
What are all the attributes a book could have?
What attributes are not important?
What attributes are important?
Hold on to your list!
Setup A Schema

CREATE DATABASE pokemon;
\c pokemon;
CREATE TABLE pokemon (
id SERIAL PRIMARY KEY,
name TEXT,
pokedex_number NUMERIC,
type1 TEXT,
type2 TEXT
);
CREATE TABLE moves (
id SERIAL PRIMARY KEY,
name TEXT,
type TEXT
);
$ psql -f schema.sql
$ psql
> \c pokemon
> \dt
> SELECT * FROM pokemon;
Delete A Schema

DROP TABLE pokemon;
DROP TABLE moves;
DROP DATABASE pokemon;
Objectives

- Describe what a Relational Database is
- Describe what SQL is used for
- CRUD with SQL
- Define a Schema with SQL
Pair:

20 Minuites
- Create a schema.sql file
- Create a database in your schema.sql file
- From your book attributes, create a table and use the book's attributes as columns
- Set the data types for your columns
- psql -f schema.sql
- Check to see that the tables were created in your database
Self-Guided Curriculum

Unit 1 - Complete by Thursday
Fork / Clone / Pull Request on:
Intro to SQL
By LizTheDeveloper
Intro to SQL
- 1,747