📋 SQL Basics

What Is SQL?

SQL (often pronounced "sequel") is the standard language for interacting with relational databases. You write a declarative statement describing what data you want, and the database figures out how to get it. That's a different mindset from the JavaScript you've written so far: instead of looping over records yourself, you describe the result and let the database do the work.

The mental model is a conversation with a very literal librarian. You say, "Bring me every mystery novel published after 2020, sorted by author." SQL is the precise phrasing of that request:

SELECT title, author
FROM books
WHERE genre = 'Mystery' AND published_year > 2020
ORDER BY author;

SQL matters far beyond this course. It's the lingua franca of data analysis, powers business-intelligence tools, and turns up in nearly every backend job description. As a full-stack developer, fluent SQL is what lets you build features that are fast instead of accidentally slow.

DDL vs DML: SQL's Two Halves

SQL statements fall into a few groups. Two of them cover almost everything you'll do day to day:

• DDL — Data Definition Language defines structure: CREATE, ALTER, DROP. Think "designing the shape of the filing cabinet."
• DML — Data Manipulation Language works with the data inside: SELECT, INSERT, UPDATE, DELETE. Think "putting papers in and taking them out."

You'll also meet DCL (GRANT/REVOKE for permissions) and TCL (COMMIT/ROLLBACK for transactions) later, but DDL and DML are the workhorses.

Creating Tables

A table is where data lives — a grid of rows and columns, like a spreadsheet with strict rules. Before storing data you declare the table's structure: each column's name, its data type, and any constraints that keep the data valid.

The shape of a CREATE TABLE

CREATE TABLE table_name (
    column_name  data_type  constraints,
    column_name  data_type  constraints
    -- ...
);

Common data types

Constraints keep data honest

• PRIMARY KEY — uniquely identifies each row (implies UNIQUE + NOT NULL)
• FOREIGN KEY — the value must exist in another table's key, enforcing valid relationships
• UNIQUE — no two rows may share this value
• NOT NULL — the column must always have a value
• CHECK — the value must satisfy a condition
• DEFAULT — a fallback value when none is supplied

A real table

CREATE TABLE users (
    id            SERIAL       PRIMARY KEY,           -- auto-incrementing id
    username      VARCHAR(50)  UNIQUE NOT NULL,       -- required, no duplicates
    email         VARCHAR(100) UNIQUE NOT NULL,
    date_of_birth DATE,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    is_active     BOOLEAN      DEFAULT TRUE,
    CHECK (date_of_birth > '1900-01-01')              -- sanity guard
);

INSERT: Adding Data

INSERT adds new rows. You list the columns you're filling and the matching values.

-- One row
INSERT INTO users (username, email, date_of_birth)
VALUES ('ada', 'ada@example.com', '1990-03-20');

-- Several rows at once
INSERT INTO users (username, email, date_of_birth)
VALUES
    ('grace', 'grace@example.com', '1988-11-07'),
    ('linus', 'linus@example.com', '1985-07-15');

Notice we never set id, created_at, or is_active — the SERIAL and DEFAULT definitions fill those automatically. Let the database do that work; it's more reliable than doing it by hand.

INSERT 0 1
INSERT 0 2   -- Postgres reports how many rows it added

SELECT & WHERE: Reading Data

SELECT is the statement you'll write most. It reads rows from a table; the WHERE clause narrows down which rows.

-- Every column, every row (fine for exploring, avoid in real code)
SELECT * FROM users;

-- Only the columns you need — faster and clearer
SELECT username, email FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE is_active = TRUE;

-- Combine conditions, then sort and limit
SELECT username, created_at
FROM users
WHERE is_active = TRUE AND date_of_birth > '1988-01-01'
ORDER BY created_at DESC
LIMIT 10;

WHERE operators

UPDATE & DELETE

UPDATE changes existing rows; DELETE removes them. Both take a WHERE clause that decides which rows are affected — and forgetting it is one of the most dangerous mistakes in all of SQL.

UPDATE

-- Deactivate one user
UPDATE users
SET is_active = FALSE
WHERE id = 5;

-- Change several columns at once
UPDATE users
SET email = 'new@example.com', username = 'ada_l'
WHERE id = 1;

-- You can compute new values from old ones
UPDATE products
SET price = price * 1.10          -- a 10% price rise
WHERE category = 'Electronics';

DELETE

-- Remove one specific row
DELETE FROM users WHERE id = 7;

-- Remove rows matching a condition
DELETE FROM users
WHERE is_active = FALSE AND created_at < '2022-01-01';

UPDATE users SET is_active = FALSE;   -- 😱 EVERY user, no WHERE
DELETE FROM users;                    -- 😱 deletes the entire table's rows

Aggregates & GROUP BY

Aggregate functions collapse many rows into a single summary value. They answer "how many," "what's the total," "what's the average."

SELECT COUNT(*) FROM users WHERE is_active = TRUE;   -- one number
SELECT AVG(price) FROM products WHERE category = 'Electronics';

GROUP BY: one summary per group

GROUP BY splits rows into buckets and runs the aggregate on each. "Average price per category" instead of one grand average:

SELECT category,
       COUNT(*)   AS product_count,
       AVG(price) AS avg_price,
       MAX(price) AS most_expensive
FROM products
GROUP BY category
ORDER BY avg_price DESC;

category    | product_count | avg_price | most_expensive
------------+---------------+-----------+---------------
 Laptops    |            12 |    899.00 |        1999.00
 Phones     |            20 |    649.50 |        1299.00
 Cables     |            45 |     14.20 |          39.00

HAVING: filter the groups

WHERE filters individual rows before grouping; HAVING filters the groups after aggregation.

-- Only categories whose average price tops $100
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100
ORDER BY avg_price DESC;

Joining Tables

The real power of relational databases is combining related tables. Remember our users and their orders? A JOIN stitches them back together on a shared key.

INNER JOIN — only matching rows

Returns rows that have a match in both tables. Orders that belong to a real user, users who have at least one order.

SELECT users.username, orders.id AS order_id, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id
ORDER BY orders.order_date DESC;

LEFT JOIN — keep everything on the left

Returns all rows from the first (left) table, filling in NULL where the right table has no match. Perfect for "users and their orders, including users who've never ordered."

SELECT users.username, orders.id AS order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- A common trick: find users with NO orders
SELECT users.username
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;   -- no matching order = NULL

Practice & Quiz

-- Your CREATE TABLE, INSERTs, and SELECT here

CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('USB Cable', 9.99),
    ('Keyboard', 49.00),
    ('Monitor', 199.00);

SELECT name, price
FROM products
WHERE price < 50
ORDER BY price ASC;
-- Returns USB Cable (9.99) then Keyboard (49.00)

SELECT u.username,
       COUNT(o.id)              AS order_count,
       COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_spent DESC;
-- COALESCE turns the NULL sum of zero-order users into 0

Best Practices & Pitfalls

✅ Do

• Always run a SELECT with your WHERE condition before an UPDATE or DELETE
• Name the columns you want instead of SELECT * — clearer and faster
• Use parameterized queries in your app code (never string-concatenate user input)
• Wrap multi-step changes in a transaction so you can roll back on error
• Write SQL keywords in uppercase for readability (a widely followed convention)

❌ Don't

• Run UPDATE/DELETE without a WHERE unless you truly mean "all rows"
• Compare to NULL with = — use IS NULL / IS NOT NULL
• Loop in your app issuing one query per record when a single JOIN would do (the "N+1" problem)
• Store money in floating-point columns — use DECIMAL

Summary

📚 Additional Resources

• PostgreSQL — The SQL Language tutorial
• PostgreSQL — Queries reference
• MDN — SQL (glossary)

🚀 What's Next?

You can now create tables and run CRUD queries — but how you structure those tables makes the difference between a database that scales and one that fights you. Next: Database Design Principles — entity-relationship modeling and normalization (1NF, 2NF, 3NF).