Relational Foundations
& the SELECT statement

Meet Maya. She just started as an analyst at a small company, and on day one her manager drops a question on her desk: "Who are our highest-paid people, and which departments are they in?" The answer lives in a database, and the only way to ask is SQL. That is the pattern in almost every role that touches data, from analytics to backend to operations: sooner or later the real answer sits in a table, and SQL is how you get it out. It also reveals how you reason about data, not just whether you can memorize syntax. A clean SQL answer shows you can think in sets, handle edge cases like missing values, and turn a vague business question into precise logic.

The roadmap target for this track is L3, proficient: you can write joins, group-and-aggregate, window functions, reason about indexes, and explain why a query is slow. We get there in six modules. This first one is deceptively important. Most SQL bugs people hit later trace back to a shaky grasp of these basics, especially NULL and the order operations actually run in. Maya will hit a few of those bugs herself as we go.

Before Maya can query anything, she needs to know what she is querying. A relational database stores data in tables (formally, relations). A table is a set of rows (records), each with the same set of columns (fields). That is the whole idea. A few properties make it powerful, though, and they are worth saying out loud because reviewers probe them.

Each column has a data type (INTEGER, TEXT, NUMERIC, DATE, BOOLEAN) that constrains what it can hold. Types matter: comparing a number to text, or sorting dates stored as text, is a classic source of wrong answers. The relational model's discipline of typed columns, keys, and constraints is what lets the database guarantee your data stays sane.

Every module in this course uses the same three tables, and they happen to model Maya's company. Learn them once here and you can focus on the SQL technique, not on re-reading a new schema each time. It is a tiny outfit: people work in departments, some manage others, and they place orders.

CREATE TABLE departments (
  dept_id     INTEGER PRIMARY KEY,
  dept_name   TEXT NOT NULL,
  location    TEXT
);

CREATE TABLE employees (
  emp_id      INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  dept_id     INTEGER REFERENCES departments(dept_id),  -- foreign key
  manager_id  INTEGER REFERENCES employees(emp_id),    -- self-reference!
  salary      NUMERIC(10,2),
  hire_date   DATE
);

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  emp_id      INTEGER REFERENCES employees(emp_id),
  customer    TEXT,
  amount      NUMERIC(10,2),
  order_date  DATE,
  status      TEXT  -- 'paid', 'pending', 'cancelled'
);

Here are a few rows of employees so the queries below have something to bite on. Maya notices two oddities right away. Row 1 (Alice) has a NULL manager, because Alice is the boss, and Frank has a NULL department. Those NULLs are deliberate; they will teach her something important in section 5.

Back to Maya's first task: the list of names and salaries. The smallest useful query has two parts: which columns you want (SELECT) and which table they come from (FROM). Everything else is a refinement on top of this.

Maya reads it as a sentence: "select the name and salary columns, from the employees table." The result is itself a new table. Every SQL query returns a table, even if it is one row and one column. That closure property (tables in, tables out) is what lets you nest queries later.

SELECT name, salary FROM employees;

-- result
 name  | salary
-------+---------
 Alice | 185000
 Bob   | 120000
 ...   | ...

SELECT *, and why to avoid it in real code

The star * means "every column." It is fine for quick exploration, but in production queries and code it is a smell: it returns columns you may not need (wasting I/O), it breaks silently when the table schema changes, and it hides intent from the next person reading the query. Name the columns you actually want.

Expressions and aliases

Next, Maya's manager wants each person's bonus shown alongside their salary. A SELECT list is not limited to bare columns; you can compute. And AS renames a column in the output, which is essential once expressions produce ugly auto-generated names.

SELECT name,
       salary,
       salary * 0.10 AS bonus,        -- computed column, named
       salary / 12 AS monthly_pay
FROM employees;

Now the question gets sharper: "only the people earning over 100,000." Maya does not want the whole table, just a slice of it. WHERE keeps only the rows where a condition is true. Conceptually the database walks every row, tests your condition, and discards the ones that fail. This is your primary tool for narrowing a table down to the rows that matter.

SELECT name, salary FROM employees
WHERE salary > 100000;                 -- comparison

SELECT name FROM employees
WHERE dept_id = 10 AND salary > 100000;   -- combine with AND

SELECT name FROM employees
WHERE dept_id = 20 OR dept_id = 30;       -- OR

A handful of operators cover most filtering. Know these cold.

This is the moment Maya hits her first real bug. She tries to list everyone with no department by writing WHERE dept_id = NULL, and gets back zero rows, even though Frank clearly has no department. This is the single most important section in the module, because NULL is where confident beginners write wrong queries that look right. NULL does not mean zero, and it does not mean empty string. It means "unknown / no value," and that changes the rules of logic.

Because NULL is "unknown," any comparison with it produces not true or false but a third value: unknown. SQL uses three-valued logic: true, false, unknown. And WHERE only keeps rows where the condition is true, so "unknown" rows like Frank's are dropped.

-- WRONG: this never matches Frank, even though his dept_id IS null
SELECT name FROM employees WHERE dept_id = NULL;   -- returns 0 rows!

-- RIGHT: use IS NULL / IS NOT NULL
SELECT name FROM employees WHERE dept_id IS NULL;      -- Frank
SELECT name FROM employees WHERE dept_id IS NOT NULL;

So why did Maya's dept_id = NULL return nothing? Because "is this unknown value equal to this unknown value?" is itself unknown, never true. You must use IS NULL and IS NOT NULL to test for missing values. There is no other correct way, and once Maya switches to IS NULL, Frank shows up.

NULL ripples everywhere

COALESCE(x, fallback) returns the first non-NULL argument, which is Maya's main tool for handling missing values gracefully. Want to treat a missing salary as zero in a calculation? COALESCE(salary, 0). This one function prevents a huge class of NULL-propagation bugs.

Maya's manager comes back: "Actually, just the top three earners, highest first." Remember that rows have no inherent order. ORDER BY is the only way to guarantee the sequence of your result. LIMIT caps how many rows come back, and DISTINCT removes duplicates.

-- highest paid first; ASC is default, DESC reverses
SELECT name, salary FROM employees
ORDER BY salary DESC;

-- top 3 earners: sort THEN limit
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;

-- the distinct set of departments people belong to
SELECT DISTINCT dept_id FROM employees;

-- sort by two keys: dept first, then salary within each dept
SELECT name, dept_id, salary FROM employees
ORDER BY dept_id ASC, salary DESC;

One trap Maya avoids: LIMIT without ORDER BY gives you some rows but no guarantee which ones, since the database returns whatever is convenient. "Give me the top 5 by revenue" always means ORDER BY revenue DESC LIMIT 5. The sort is what makes "top" meaningful.

Maya tries to reuse her bonus alias inside WHERE and gets an error she does not understand. The reason: you write a query starting with SELECT, but the database executes the clauses in a different order. Understanding this order explains a dozen "why doesn't this work?" errors, including why you cannot use a column alias from SELECT inside WHERE.

Notice SELECT runs at step 5, after WHERE. That is why Maya's query fails:

-- ERROR: "bonus" does not exist yet when WHERE runs
SELECT name, salary * 0.1 AS bonus
FROM employees
WHERE bonus > 15000;

-- FIX: repeat the expression, since WHERE runs before SELECT
SELECT name, salary * 0.1 AS bonus
FROM employees
WHERE salary * 0.1 > 15000;     -- ORDER BY *can* use the alias, though

Maya learned by typing, and so will you. Spin up the schema (any free Postgres playground works: db-fiddle, OneCompiler, or local psql). Create the three tables, insert the six employee rows above, then write each query before looking at any hint.

• Select just name and hire_date for every employee. Then change it to SELECT * and notice the difference in output width.
• Find all employees earning more than 100,000, sorted highest first. Predict the order on paper, then confirm.
• List employees whose name starts with a vowel using LIKE (hint: name LIKE 'A%' OR name LIKE 'E%' ...).
• Find the employee(s) with no manager. Then find the one with no department. Use the correct NULL test, and prove that = NULL returns zero rows the way it did for Maya.
• Add a computed column annual_bonus = 10% of salary, aliased. Then try to filter on the alias in WHERE, watch it fail, and fix it.
• Get the distinct list of status values that appear in orders. Then the top 3 orders by amount.
• Use COALESCE to show every employee's dept_id, but display 0 for Frank instead of NULL.
• Sort employees by salary descending with NULLS LAST, after temporarily setting one salary to NULL. Watch where that row lands.