SELECT beginner FROM all;

A collection of beginner commands for navigating SQL & PostgreSQL

For folk who are beginning their journey in using SQL, this can be a point of reference while you practice.

Replace anything in < >'s with your actual table name/column name/constraint/data/etc…

NOTE: Never spent much time in Google Sheets or Excel? Start there! I recommend spending 1–3 hours playing around with the QUERY function in Google Sheets — a.) it’s a ton of fun and b.) it may help you visualize what’s going on with some of these concepts. Check out David Krevitt’s course on the QUERY function (or if you’d rather not spend $49, just go to his blog post about it).

Terminal Commands

These are used when you’re in your terminal, before opening the psql console.

createdb <table name> : Creates a table

dropdb <table name>: Drops (deletes) a table

psql <table name>: Opens a terminal-based console to interact with PostgreSQL and the databases within — similar to irb for Ruby.

postgres is a default database that is created when you install PostgreSQL. You can always use psql postgres to open the console. Then, you can move to other databases from within the psql console.

psql <database name> < <file name>.sql: Imports a file into a database.

Meta-Commands

Always start with a backslash (). These are used when you’re in the psql console.

\q: Quits

\c <database name>: Connects to a different database

\l: Lists all of the databases

\dt: Describes the tables in the current database

\d <table name>: Describes the table

SQL Commands — DDL

Always end with a semicolon (;). These are used when you’re in the psql console.

CREATE/DROP

CREATE DATABASE <database name>;

Creates a new database.

DROP DATABASE <database name>;

Deletes the database. Careful with this one — irreversible!

CREATE TABLE <table name>(<column setup>);

Creates a new table within the current database. Column setup information goes in the parenthesis.

Column setup includes three items: the name of the column, the data type, and the constraints. The below example creates a table with three new columns, each with a data type and a constraint.

CREATE TABLE a_new_table(
 name data_type constraint,
 name2 data_type2 constraint,
 name3 data_type3 constraint
 );

DROP TABLE <table name>;

Deletes the table. Careful with this one — irreversible!

ALTER: TABLE

ALTER TABLE <table name> RENAME TO <new name>;

Renames a table.

ALTER: COLUMN

ALTER TABLE <table name> RENAME COLUMN <column name> TO <new name>;

Renames a column.

ALTER TABLE <table name> ALTER COLUMN <column name> TYPE <new type>;

Changes data type of a column.

ALTER TABLE <table name> ADD COLUMN <column setup>;

Creates a new column, <column setup> is the same as it is in creating a new table: <column name> <data type> <constraints>.

ALTER TABLE <table name> DROP COLUMN <column name>;
Deletes a column. Careful with this one — irreversible!

ALTER: CONSTRAINTS

ALTER TABLE <table name> ADD CONSTRAINT <constraint name> <constraint>;

Add a table constraint, you make up the constraint name.

ALTER TABLE <table name> DROP CONSTRAINT <constraint name>;

Delete a table constraint.

ALTER TABLE <table name> ADD UNIQUE (<column name>);

Adds a unique constraint to the given column.

ALTER TABLE <table name> ADD CHECK (<expression>);

Adds a check constraint to the table. Can use multiple columns in expression. SQL functions (explained below) are useful tools.

ALTER TABLE <table name> ALTER COLUMN <column name> SET <constraint>;

Add a column constraint.

ALTER TABLE <table name> ALTER COLUMN <column name> DROP CONSTRAINT <constraint>;

Delete a column constraint.

ALTER TABLE <table name> ALTER COLUMN <column name> DROP DEFAULT;

Remove a default clause.

SQL Commands — DML

Always end with a semicolon (;). These are used when you’re in the psql console.

INSERT

INSERT INTO <table name> (<column name>) VALUES (<data>);

Inserts data into the table. Can add more columns using commas and can add multiple rows of data by adding more sets of values in parenthesis after the first.

INSERT INTO <table name> (<col 1>, <col 2>)
 VALUES (<data-1a>, <data-1b>),
 (<data-2a>, <data-2b>),
 (<data-3a>, <data-3b>);

UPDATE

UPDATE <table name> SET <column name> = <new value> WHERE <expression>;

Updates data to the <new value> in the column given where the expression is true. The WHERE clause is optional — if you leave it off, the command will update every row in the table with the <new value>.

DELETE

DELETE FROM <table name> WHERE <expression>;

Deletes data where the expression is true. The WHERE clause is optional — if you leave it off, the command will delete every row in the table.

SELECT

SELECT * FROM <table name>;

Displays all of the data from the listed table. Note: * is a wild card character — grabs all of the columns in a given table.

SELECT <column name>, <column name> FROM <table name>;

Display specific columns by listing them after SELECT. Can display one or multiple by separating names with a comma.

SELECT Clauses

• ... WHERE <expression>; Specify which rows of data you’d like by adding an expression. See SQL operators below.
• ... ORDER BY <column name>; Sorts the data according to the column. Can add ASC or DESC to specify whether it is in ascending or descending order. Can also sort using functions.
• ... LIMIT <number>; Limits the results to the number given.
• ... OFFSET <number>; Leaves off some of the first results, depending on the number given.
• ... GROUP BY <column name>; Combines data by common values in the given column. Often need this in the SELECT command to use aggregate functions (see SQL functions below).

SQL Operators/Comparisons

=: Equal to

<> or !=: Not equal to

>, <: Greater than, less than

>=, <=: Greater than or equal to, less than or equal to

BETWEEN / NOT BETWEEN : Between or not between two values

IS NULL / IS NOT NULL : Is or is not NULL

AND, OR: Logical operators to add multiple clauses

LIKE / NOT LIKE: Use with % (multi-character wild card) or _ (single character wild card) to match substrings (e.g. ...LIKE %Smith; would match to “Harry Smith” and “Lane Smith”)

SQL Functions

String

length(): Returns the length of the given string argument.

Date/Time

now(): Returns the current date and time.

date_part(): Takes two arguments, the part of the date (e.g. ‘year’) and where that data is coming from (e.g. a column name).

age(): Takes a timestamp argument, calculates time elapsed between the timestamp and the current time.

Aggregate

count(): Returns the number of values in the data specified.

sum(): Returns the sum of the values in the data specified.

min(): Returns the minimum value in the data specified.

max(): Returns the maximum value in the data specified.

avg(): Returns the average of the values in the data specified.

There are so many other commands/functions/uses out there than what is described above, so be adventurous and scour the internet for more information when you need it. A great place to turn to first? The PostgreSQL Docs: postgresql.org/docs.

That’s all for now — code well, my friends!

\q