SQL One

Databases and PostgreSQL

What is SQL?

SQL stands for Structured Query Language, and is a language for interacting with databases.

Here at DevMountain, you will be learning about the PostgreSQL flavor of SQL.

Databases

Databases are an organized way to create and store data. SQL works with relational databases. A relational database is built in a table layout.

Database Schema

A database schema is a blueprint for how a database table is structured. It includes the name of the table, column names, and the data types of those columns:

CREATE TABLE racers (
    id INT PRIMARY KEY,
    name TEXT,
    age INTEGER,
    country TEXT
);

table name

column names

column data types

Datatypes

Much like other languages, PostgreSQL has data types. Common data types you will use are:

null - empty value

integer - round number

decimal - unlimited decimal values

float - up to 15 decimal places

serial - incrementing integer

text - unlimited characters in a string

varchar(n) - defined number of characters in a string

boolean - true or false

SQL Statements

SQL statements are queries that we make to our database.

These can include creating tables, selecting data from tables, adding data, updating data, deleting data, and more. Some things to note about SQL statement syntax:

1. Use a semicolon to end a statement.

2. Do not have any trailing comma's

3. Use single quotes, not double

4. It's common to use all caps, but not required. Whatever you choose, BE CONSISTENT.

Create Statements

Create statements are for creating database tables.

CREATE TABLE racers (
    id INT PRIMARY KEY,
    name TEXT,
    age INTEGER,
    country TEXT
);

column names

table name

column data types

Insert Statements

Insert statements are for adding data to a database table. Their syntax is as follows:

INSERT INTO users
(name, age, country)
VALUES
('Matt', 27, 'US');

columns to add data to

values being added to selected columns

table name

Select Statements

Select statements are queries for retrieving data. Specific columns from a table can be selected by referencing the column name, or all columns can be selected by using the splat(*).

SELECT * FROM users;

Conditions can be added to to select more specific data, using the 'where' keyword.

SELECT * FROM users
WHERE name = 'Matt';

Select Statement Operators

When using conditions to select more specific data, we can use different operators to help the statement. Some common operators are:

> - Greater Than

< - Less Than

>= - Greater Than or Equal to

<= - Less Than or Equal to

!= - Does Not Equate

AND - check for multiple conditions

OR - check for only one condition to be true

IS NULL - Checks for null value

IS NOT NULL - Checks for not null value

BETWEEN - Checks a range

ASC - Sorts in ascending order

DESC - Sorts in descending order

Select In Statements

The Select In statement is a more concise way to check for multiple conditions:

SELECT *
FROM racers
WHERE name IN ('Tayte', 'Samual', 'Kevin');

SQL Functions

SQL functions are able to handle some logic in the query, such as how many rows match a condition, the sum of the values in specific rows, and so on. Some common functions are:

count() - returns the count of rows that match a query

max() - returns the maximum value from a query

sum() - returns the sum of values from a query

avg() - returns the average value from a query

Update Statements

Update statements are for updating existing data in a database table. Update statements syntax are as follows:

UPDATE users
SET name = 'Matias'
WHERE id = 1;

table name

New value using 'set'

condition to specify row

Delete Statements

Delete Statements are for deleting existing rows. Delete statement syntax is as follows:

DELETE FROM users
WHERE id = 1;

table name

condition to specify row

Dropping Tables

Drop statements are for deleting, or 'dropping', database tables. Drop table syntax is as follows:

DROP TABLE users;

table name

NOTE: Dropping a table is irreversible, so make sure you actually want to drop the table before performing the operation.

Scott's SQL One

By Scott Sutherland