Module 02 // SQL Baseline

SQL Basics

> The foundational verbs of SQL — SELECT, INSERT, UPDATE, DELETE — and the predicates that filter, sort, and shape your result sets.

What is SQL?

SQL (Structured Query Language) is a declarative language for managing data in relational databases. You describe what you want, and the database engine figures out how to retrieve it.

Creating a Table

Every table needs a PRIMARY KEY — a column that uniquely identifies each row.

schema.sql

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

SELECT — Reading Data

The most common operation. Retrieve columns from one or more tables.

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT name, salary FROM employees;

-- With aliases
SELECT name AS employee_name, salary AS monthly_pay FROM employees;

WHERE — Filtering Rows

Use predicates to narrow results. Combine conditions with AND, OR, and NOT.

SELECT name, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 80000
ORDER BY salary DESC
LIMIT 10;

INSERT — Adding Rows

INSERT INTO employees (name, department, salary)
VALUES ('Ada Lovelace', 'Engineering', 95000),
       ('Alan Turing', 'Research', 110000);

UPDATE & DELETE

Always include a WHERE clause — a missing one will affect every row.

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

DELETE FROM employees
WHERE hired_at < '2010-01-01';

Aggregations

Summarize data with aggregate functions and GROUP BY.

SELECT department,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary,
       MAX(salary) AS top_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Next steps

Once SELECT and aggregations feel natural, move on to Joins to combine multiple tables, then Subqueries for nested logic.