📝 SQL Basics — CRUD

What is CRUD?

CRUD is an acronym that describes the four fundamental things you can do with data:

CRUD Mapped to SQL

We also need CREATE TABLE (to build the table in the first place) and ALTER/DROP TABLE (to change or destroy it). This lesson covers all of them.

CREATE TABLE — Building Your First Table

Before you can store any data, you need a table. A table is like a spreadsheet: it has columns (the headers) and rows (the actual data). The CREATE TABLE command defines the columns, what type of data each column holds, and any rules the data must follow.

Syntax

CREATE TABLE table_name (
    column1  DATA_TYPE  constraints,
    column2  DATA_TYPE  constraints,
    ...
);

Our Running Example: employees

Throughout this lesson, we'll work with an employees table. Here's how we create it:

CREATE TABLE employees (
    id          SERIAL        PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    department  VARCHAR(50),
    salary      DECIMAL(10,2),
    hire_date   DATE          DEFAULT CURRENT_DATE
);

Let's break every piece of this down:

Result: The Empty Table

After running the command above, you get a table with the structure defined but zero rows:

INSERT INTO — Adding Data

Now that we have our empty table, let's fill it with data. The INSERT INTO command adds new rows (records) to a table.

Single-Row INSERT

INSERT INTO employees (name, department, salary, hire_date)
VALUES ('Alice Johnson', 'Engineering', 85000.00, '2023-01-15');

Notice we did not provide a value for id. Because it's SERIAL, PostgreSQL auto-assigns 1.

Multi-Row INSERT

You can insert many rows at once by separating each set of values with a comma:

INSERT INTO employees (name, department, salary, hire_date) VALUES
    ('Bob Smith',       'Marketing',    62000.00,  '2023-03-20'),
    ('Carol Davis',     'Engineering',  91000.00,  '2022-11-01'),
    ('David Lee',       'Sales',        55000.00,  '2024-01-10'),
    ('Eva Martinez',    'Engineering',  97000.00,  '2021-07-22'),
    ('Frank Wilson',    'HR',           58000.00,  '2023-06-05'),
    ('Grace Chen',      'Marketing',    67000.00,  '2022-09-14'),
    ('Hank Brown',      'Sales',        52000.00,  '2024-02-28'),
    ('Irene Taylor',    'Engineering',  89000.00,  '2023-04-18'),
    ('Jake Nguyen',     'HR',           61000.00,  '2023-08-30');

After both INSERTs, the table now has 10 employees (Alice + 9 others).

The Table After INSERTs

What Happens If You Break a Rule?

Try inserting a row without the name column (which is NOT NULL):

INSERT INTO employees (department, salary)
VALUES ('Marketing', 70000);

-- ERROR: null value in column "name" violates not-null constraint

INSERT With DEFAULT Values

If you skip hire_date, the DEFAULT CURRENT_DATE kicks in:

INSERT INTO employees (name, department, salary)
VALUES ('Karen White', 'Sales', 59000);

-- hire_date will automatically be today's date

SELECT — Reading Data

SELECT is the most important SQL command. You'll use it far more than any other. It retrieves (reads) data from a table. You can fetch everything, fetch specific columns, filter rows, sort results, and much more.

SELECT * — Get Everything

The asterisk * means "all columns." This fetches every column and every row:

SELECT * FROM employees;

Result: the full 10-row table we saw above. While SELECT * is great for quick exploration, in production code you should always name the columns you need (we'll see why in a moment).

SELECT Specific Columns

Instead of all columns, pick only the ones you need:

SELECT name, department, salary
FROM employees;

Column Aliases with AS

You can rename columns in the output using AS:

SELECT
    name        AS employee_name,
    department  AS dept,
    salary      AS annual_pay
FROM employees;

Aliases don't change the actual table — they only change the column names in your query output. They're great for making reports more readable.

ORDER BY — Sorting Results

Want to see the highest-paid employees first?

SELECT name, salary
FROM employees
ORDER BY salary DESC;

DESC = descending (biggest first). ASC = ascending (smallest first, which is the default).

LIMIT — Just a Few Rows

When you have millions of rows, you don't want to fetch them all. LIMIT restricts the number of rows returned:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

WHERE — Filtering Data

The WHERE clause filters rows based on conditions. Only rows that match the condition are returned. Without WHERE, you get everything. With WHERE, you get exactly what you need.

Basic Comparisons

Example: Filter by Department

SELECT name, department, salary
FROM employees
WHERE department = 'Engineering';

Example: Filter by Salary

SELECT name, salary
FROM employees
WHERE salary > 80000;

AND & OR — Combining Conditions

AND means both conditions must be true. OR means at least one must be true.

-- Engineers earning more than 90k
SELECT name, department, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 90000;

-- People in Sales OR HR
SELECT name, department
FROM employees
WHERE department = 'Sales'
   OR department = 'HR';

BETWEEN, IN, LIKE

PostgreSQL gives you handy shortcuts for common filtering patterns:

-- Salaries between 60k and 80k (inclusive)
SELECT name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 80000;

-- Same as: WHERE salary >= 60000 AND salary <= 80000

-- People in specific departments (cleaner than multiple ORs)
SELECT name, department
FROM employees
WHERE department IN ('Engineering', 'Marketing');

-- Names starting with 'A' (pattern matching)
SELECT name
FROM employees
WHERE name LIKE 'A%';

-- % means "any characters after". 'A%' matches Alice, Andrew, Angela...

UPDATE — Changing Existing Data

When data changes in the real world, you need to reflect that in the database. Got a raise? New department? Changed your name? The UPDATE command modifies existing rows.

Syntax

UPDATE table_name
SET column1 = new_value1,
    column2 = new_value2
WHERE condition;

Example: Give Alice a Raise

UPDATE employees
SET salary = 92000.00
WHERE name = 'Alice Johnson';

Before:

After:

Example: Move Bob to a New Department

UPDATE employees
SET department = 'Sales',
    salary = 65000.00
WHERE name = 'Bob Smith';

You can update multiple columns at once by separating them with commas in the SET clause.

Example: Give Everyone a 10% Raise

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

This multiplies the current salary by 1.10 (a 10% increase) for every engineer. The salary on the right side of = refers to the current value.

-- THIS IS DANGEROUS! Every employee's salary becomes 50000!
UPDATE employees
SET salary = 50000;

-- Step 1: Preview (safe)
SELECT * FROM employees WHERE department = 'Engineering';

-- Step 2: If the rows look right, update
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';

DELETE — Removing Data

DELETE removes rows from a table. This is the "scary" command because once data is deleted, it's gone (unless you have backups or use transactions).

Syntax

DELETE FROM table_name
WHERE condition;

Example: Remove One Employee

DELETE FROM employees
WHERE name = 'Hank Brown';

This removes only Hank Brown's row. Everyone else stays untouched.

Example: Remove All Employees in a Department

DELETE FROM employees
WHERE department = 'Sales';

This deletes every row where department is 'Sales'. David Lee and Hank Brown (if he hadn't been deleted already) would be removed.

-- THIS DELETES EVERY SINGLE ROW in the table!
DELETE FROM employees;

DELETE vs TRUNCATE

If you genuinely want to wipe all rows, PostgreSQL offers TRUNCATE which is faster:

ALTER TABLE & DROP TABLE

After a table exists, you might realize you forgot a column, or need to rename something, or want to destroy the entire table. That's where ALTER TABLE and DROP TABLE come in.

ALTER TABLE — Add a Column

Oops, we forgot to track email addresses!

ALTER TABLE employees
ADD COLUMN email VARCHAR(255);

This adds a new email column. Existing rows will have NULL for this column until you update them.

ALTER TABLE — Drop a Column

Don't need a column anymore? Remove it:

ALTER TABLE employees
DROP COLUMN email;

ALTER TABLE — Rename a Column

ALTER TABLE employees
RENAME COLUMN name TO full_name;

ALTER TABLE — Rename the Table

ALTER TABLE employees
RENAME TO staff;

ALTER TABLE — Change a Column's Type

ALTER TABLE employees
ALTER COLUMN salary TYPE NUMERIC(12,2);

DROP TABLE — The Nuclear Option

DROP TABLE doesn't just delete the data — it destroys the entire table structure. The table ceases to exist. Gone. Columns, rows, constraints, indexes — everything.

-- Destroys the table completely
DROP TABLE employees;

-- Safer version: only drops if the table exists (no error if it doesn't)
DROP TABLE IF EXISTS employees;

Quick Reference: ALTER TABLE Commands

The Complete CRUD Cheat Sheet

Let's put it all together. Here's every command from this lesson in one place:

Practice Exercises

Time to practice! Try these exercises on your own PostgreSQL database. We recommend opening psql or pgAdmin and typing these out manually — don't just copy-paste. Muscle memory matters.

Solution Hints

Try each exercise on your own first! If you're stuck, here are the first two solutions:

-- Exercise 1: Create products table
CREATE TABLE products (
    product_id    SERIAL        PRIMARY KEY,
    product_name  VARCHAR(150)  NOT NULL,
    category      VARCHAR(50),
    price         DECIMAL(10,2),
    in_stock      BOOLEAN       DEFAULT true
);

-- Exercise 2: Insert 5 products
INSERT INTO products (product_name, category, price) VALUES
    ('Laptop',     'Electronics',  999.99),
    ('Notebook',   'Stationery',   4.50),
    ('Coffee Mug', 'Kitchen',      12.99),
    ('USB Cable',  'Electronics',  8.75),
    ('Desk Lamp',  'Furniture',    34.00);

Knowledge Check — Quiz Time!

Test what you've learned! Click the answer you think is correct.

What's Next?

What You Learned Today

You now know the fundamental building blocks of SQL. Every application — from tiny personal projects to billion-dollar platforms like Netflix and Amazon — uses these exact same commands under the hood. The difference is just scale.