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 tanks (
id INT PRIMARY KEY,
model TEXT,
country TEXT,
weight INTEGER
);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 tanks (
id INT PRIMARY KEY,
model TEXT,
country TEXT,
weight INTEGER,
);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 tank_commanders
(name, age, country)
VALUES
('Scott', 21, '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 tank_commanders;Conditions can be added to to select more specific data, using the 'where' keyword.
SELECT * FROM tank_commanders
WHERE name = 'Scott';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 tanks
WHERE name IN ('M4 Sherman', 'T-34', 'Tiger');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
