sql_cheatsheet.md

Database and Table Setup/Maintenance

Creating a database

• CREATE DATABASE db_name;

Deleting a database

• DROP DATABASE db_name;

Creating a table:

CREATE TABLE employees (
  id serial PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INTEGER NOT NULL,
  title VARCHAR(100) NOT NULL,
  badge INTEGER UNIQUE NOT NULL
);

Adding a column to a table:

ALTER TABLE table_name ADD COLUMN new_column_name DATATYPE CONSTRAINT(S);

Removing a column from a table:

ALTER TABLE table_name DROP COLUMN column_to_be_dropped;

Removing a table in its entirety:

DROP TABLE table_name;

CRUD (Create, Read, Update, Destroy)

Create:

INSERT INTO table_name (attr1, attr2, attr3) VALUES ('travis', 27, 'instructor');

Read:

SELECT attr3, attr_7 FROM table_name;

Update:

UPDATE table_name SET attr1='a new Value', attr2='another new value' WHERE attr3='true';

Destroy:

DELETE FROM table_name WHERE attribute='something unforgivable';

NOTE: Update and Destroy both take a WHERE clause, as you will generally be targeting a certain row (or several) as opposed to all rows at once.