SQL

Structured Query Language

But

First,
Databases

PostgreSQL

  • Specific variety of Database (DB)
  • Specific syntax to get data

We use a database to store our data in tables and rows

Similar to an excel spreadsheet

Schema

A schema is a blueprint for our database.

CREATE TABLE <table name> (
  <column name> <data type> [<constraint>],
  <column name> <data type> [<constraint>]
);

We can name our table and columns whatever we want, but it should be descriptive

Common Column Data Types

  • NULL - an empty value
  • Numeric
    • INTEGER - whole number
    • DECIMAL - using decimals
    • FLOAT - can only have 15 decimal places
    • SERIAL - auto incrementing
  • Characters
    • TEXT - unlimited characters
    • VARCHAR(count) - characters limited to count

But
How,
Jonathan?

Can we actually get data

SQL has many different commands, or queries, that will narrow down our search for data in our DB

Think about searching for a name in a phone book, you wouldn't just search over all of data, you would look by last name first

Each query has at least three parts, sometimes more

  • What are we doing?
  • What are we selecting?
  • Where do we want to look?

select * from users;

What we are doing

what we are selecting

where we are searching

Queries should follow a few rules:

  • Always end in a semi-colon
  • Only use single quotes, double quotes searches for a column by that name
  • Be consistent. Doesn't matter if you capitalize keywords or not, just be consistent.

WHERE clause

Not looking at where something is but where something matches a specific clause or stipulation

WHERE clause

  • > - greater than
  • < - less than
  • >= - greater than or equal to
  • != - does not equal

Comparison Operators

WHERE clause

  • AND
  • OR

Logical Operators

select * from users
where first_name = 'Bryan'
and age = 29;

WHERE clause

  • IS NULL
  • IS NOT NULL

Null check Operators

select * from users
where favorite_color IS NOT NULL;

WHERE clause

Between Operators

select * from users
where age BETWEEN 18 AND 109;

LIMIT clause

Sometimes your table may have thousands and thousands of rows. We don't always need everything so we can limit it.

select * from users
limit 50;

ORDER BY clause

You can organize the data you get back from your query. Two of the most common ways are ASCending and DESCending

select * from users
order by age desc;

Aggregate Functions

  • min()

  • max()

  • sum()

  • avg()

  • count()

select avg(age) as average_user_age from users;

WHERE Partial Matching

select * from users
where name like '%rya%';
Made with Slides.com