SELECT beginner FROM all;
Tags: postgres, beginners, sql, programming
Published: 2020-01-10 20:36:24

---
title: SELECT beginner FROM all;
published: true
date: 2019-08-03 18:57:17 UTC
tags: postgres,beginner,sql,programming
canonical_url: https://medium.com/@mindyzwanziger/select-beginner-from-all-32ffd589470
---

#### 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.

![](https://cdn-images-1.medium.com/max/1024/1*p5j6BcCiUO3-efdUu1HFBQ.jpeg)<figcaption>Index cards. Photo Credit: Pixabay</figcaption>

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_](https://learn.codingisforlosers.com/the-google-sheets-query-function) _(or if you’d rather not spend $49, just go to his_ [_blog post_](https://codingisforlosers.com/google-sheets-query-function/) _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](https://www.postgresql.org/docs/).

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

`\q`