Intro to SQL

Databases, Finally

Objectives

  1. Describe what a Relational Database is
  2. Describe what SQL is used for
  3. CRUD with SQL
  4. Define a Schema with SQL

You'll revisit these!

Relational Databases

What are they?

What makes them "relational"?

Relational Databases

Tables

ID Name Email 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 Email 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

  1. Describe what a Relational Database is
  2. Describe what SQL is used for
  3. CRUD with SQL
  4. 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 Email 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 Email 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

  1. Describe what a Relational Database is
  2. Describe what SQL is used for
  3. CRUD with SQL
  4. 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

  1. Describe what a Relational Database is
  2. Describe what SQL is used for
  3. CRUD with SQL
  4. Define a Schema with SQL

Pair:

20 Minuites

  1. Create a schema.sql file
  2. Create a database in your schema.sql file
  3. From your book attributes, create a table and use the book's attributes as columns
  4. Set the data types for your columns
  5. psql -f schema.sql
  6. 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

Loading comments...

More from LizTheDeveloper