SQL Fundamentals — Your First Queries

Status: Active | Last Updated: 2026-08-29 Category: SQL — Fundamentals Prerequisites: Basic programming concepts — no SQL experience required Tags: sql, select, where, order-by, limit, distinct, null, coalesce, sqlite, postgres Estimated Time: 3-4 hours (Self-paced, includes lab time)

Summary

SQL (Structured Query Language) is the declarative standard for querying relational databases. Every database in this stack uses SQL: SQLite in development (through Prisma) and PostgreSQL in production. Learn to write SELECT, filter rows with WHERE, sort with ORDER BY, paginate with LIMIT/OFFSET, and handle missing data with NULL — the foundation every query is built on.

What You'll Learn (Core Competencies)

• Write SELECT statements targeting specific columns and entire rows
• Filter rows using WHERE with comparison and pattern operators
• • Sort results with ORDER BY and paginate with LIMIT/OFFSET
• Eliminate duplicate rows with DISTINCT
• Handle missing values correctly using IS NULL, IS NOT NULL, and COALESCE

Table of Contents

1. Architectural Overview & Core Schema
2. Deep Dive & Implementation
3. Anti-Patterns & Common Pitfalls
4. Independent Challenge
5. Consolidation & Key Invariants
6. Next Steps

---

1. Architectural Overview & Core Schema

What is SQL

SQL is a declarative language — you describe what you want, not how to get it. You say "give me all users from Germany" and the database's query planner decides the fastest way to retrieve them (scanning tables or using an index). This abstraction means SQL works nearly identically in SQLite, PostgreSQL, MySQL, and most relational databases.

Query Layer (SELECT, WHERE, ORDER BY, ...)
        ↓
  Query Planner (chooses access path, indexes)
        ↓
Storage Engine (reads pages, returns rows)

The query planner is the key engine. Two queries that return the same rows may have wildly different performance depending on how the planner chooses to execute them. This is why reading EXPLAIN output matters as you grow.

Core Table Used in This Lesson

All examples use these tables:

-- users table
CREATE TABLE users (
    id        INTEGER PRIMARY KEY,
    name      TEXT    NOT NULL,
    email     TEXT    UNIQUE,
    country   TEXT,
    phone     TEXT,
    active    INTEGER DEFAULT 1
);

-- orders table (referenced in Next Steps / sql-joins)
CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    user_id    INTEGER REFERENCES users(id),
    total      REAL    NOT NULL,
    status     TEXT    DEFAULT 'pending',
    created_at TEXT    DEFAULT (date('now'))
);

---

2. Deep Dive & Implementation

SELECT and FROM

The most basic query selects all columns from a table:

SELECT * FROM users;

Select specific columns — always prefer this in production code:

SELECT id, name, email FROM users;

Selecting only needed columns reduces memory overhead and makes intent explicit. Use column aliases to clarify results, especially when expressions or joins are involved:

SELECT id AS user_id, name AS full_name FROM users;

SELECT without a FROM clause is valid in some databases (e.g., SELECT 1 + 1 in PostgreSQL), but every real query names a table.

DISTINCT

DISTINCT collapses duplicate rows:

SELECT DISTINCT country FROM users;

DISTINCT applies to the entire row — SELECT DISTINCT country, name returns rows where both differ.

Filtering with WHERE

The WHERE clause restricts rows before they reach the result set. Operators include:

Operator	Meaning
=	Equal
<> or !=	Not equal
<, >, <=, >=	Comparison
BETWEEN a AND b	In range (inclusive)
LIKE '%pattern%'	Pattern match
IN (a, b, c)	Value in list
IS NULL / IS NOT NULL	Null test

Combine conditions with AND/OR. Use parentheses to enforce precedence:

-- Completed orders over $100
SELECT * FROM orders
WHERE total > 100 AND status = 'shipped';

-- Users in Germany or France
SELECT * FROM users
WHERE country IN ('Germany', 'France');

-- Email pattern match
SELECT * FROM users
WHERE email LIKE '%@example.com';

Sorting and Limiting

ORDER BY sorts ascending (ASC, the default) or descending (DESC). LIMIT caps results; OFFSET skips rows for deterministic pagination:

-- Top 10 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- Paginate: page 3 (skip 20, show 10)
SELECT * FROM logs ORDER BY created_at DESC LIMIT 10 OFFSET 20;

Without ORDER BY, LIMIT returns rows in storage order — which is undefined and can change between queries.

Handling NULL

NULL means unknown or missing value, not zero or empty string. Comparisons with NULL always return NULL (treated as false):

-- NULL is not equal to anything — this returns no rows
SELECT * FROM users WHERE phone = NULL;          -- WRONG

-- Correct null test
SELECT * FROM users WHERE phone IS NULL;

-- Default for missing phone
SELECT id, name, COALESCE(phone, 'unlisted') AS phone FROM users;

-- Orders not yet shipped
SELECT * FROM orders WHERE shipped_at IS NULL;

COALESCE(col, default) returns the first non-NULL argument. Aggregate functions (COUNT, SUM, etc.) ignore NULL — COUNT(col) counts only non-null values, while COUNT(*) counts all rows.

Guided Checkpoint

Verify that WHERE filtering, ORDER BY/LIMIT, and NULL handling behave as expected using SQLite:

# Create test table and data
sqlite3 :memory: -header -column "
  CREATE TABLE users (id INTEGER, name TEXT, country TEXT, phone TEXT);
  INSERT INTO users VALUES (1, 'Alice', 'Germany', '123');
  INSERT INTO users VALUES (2, 'Bob',   'France',  NULL);
  INSERT INTO users VALUES (3, 'Carol', 'Germany', '');
  INSERT INTO users VALUES (4, 'Dan',   NULL,      '456');

  -- Test 1: WHERE filter
  SELECT '=== Test 1: WHERE country=Germany ===' AS test;
  SELECT id, name, country FROM users WHERE country = 'Germany';

  -- Test 2: ORDER BY + LIMIT
  SELECT '=== Test 2: ORDER BY id DESC LIMIT 2 ===' AS test;
  SELECT id, name FROM users ORDER BY id DESC LIMIT 2;

  -- Test 3: NULL handling
  SELECT '=== Test 3: IS NULL vs empty string ===' AS test;
  SELECT id, name, phone, 'phone IS NULL' AS test FROM users WHERE phone IS NULL;
  SELECT id, name, phone, 'phone = ""'   AS test FROM users WHERE phone = '';

  -- Test 4: COALESCE
  SELECT '=== Test 4: COALESCE(phone, "unlisted") ===' AS test;
  SELECT id, name, COALESCE(phone, 'unlisted') AS phone FROM users;
"

Expected output: Alice and Carol (Germany filter); Dan and Carol (DESC id); row for Bob (phone IS NULL); Bob shows unlisted under COALESCE.

---

3. Anti-Patterns & Common Pitfalls

Anti-Pattern 1: SELECT * in Application Code

Fetching all columns transfers unnecessary data over the network and breaks when columns are added or reordered.

-- Bad: pulls every column, including large TEXT/BLOB fields
SELECT * FROM users WHERE id = 42;

-- Good: only the columns the application needs
SELECT id, name, email FROM users WHERE id = 42;

Anti-Pattern 2: String Concatenation Leading to SQL Injection

Never build SQL strings by concatenating user input:

-- Bad: user-supplied 'name' could inject SQL
const query = `SELECT * FROM users WHERE name = '${userName}'`;

-- Good: parameterized query
const query = 'SELECT * FROM users WHERE name = $1';
// driver sends userName as a bound parameter, never interpreted as SQL

Even in raw SQL scripts, prefer parameterized queries or proper escaping.

Anti-Pattern 3: Implicit Type Coercion Causing Index Scans

SQLite loosely coerces types; PostgreSQL is stricter. Mixing types can prevent index usage:

-- If user_id is INTEGER but queried as TEXT, index may not be used
SELECT * FROM orders WHERE user_id = '42';   -- string literal in some contexts

-- Safe: use the correct type
SELECT * FROM orders WHERE user_id = 42;

Anti-Pattern 4: OFFSET Without ORDER BY for Pagination

OFFSET skips rows arbitrarily when no sort order is defined. Large offsets are also slow — the database scans and discards rows.

-- Bad: non-deterministic, slow for large offsets
SELECT * FROM orders LIMIT 10 OFFSET 10000;

-- Good: deterministic keyset pagination
SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 10;

---

4. Independent Challenge

Design a query set for a small event RSVP system.

Given these tables:

CREATE TABLE events (
    id          INTEGER PRIMARY KEY,
    title       TEXT    NOT NULL,
    location    TEXT,
    event_date  TEXT    NOT NULL,
    max_attendees INTEGER
);

CREATE TABLE rsvps (
    id         INTEGER PRIMARY KEY,
    event_id   INTEGER REFERENCES events(id),
    user_name  TEXT    NOT NULL,
    created_at TEXT    DEFAULT (date('now'))
);

Write a single .sql file containing queries that:

1. List all upcoming events (event_date >= today), sorted by date, showing event title, location, and current RSVP count.
2. Find events with zero RSVPs — no registration yet.
3. For a given event (by ID), show the RSVP list including attendee name and time since RSVP (in days).
4. Find the top-3 most popular events by RSVP count.

Save as challenge_rsvp.sql. Run against a test database you create with sqlite3.

---

5. Consolidation & Key Invariants

Bullet-list summary of the must-remember rules.

• SQL is declarative: SELECT describes the result set; the database engine decides the execution path.
• WHERE filters before aggregation/sorting; use HAVING for post-aggregation filters.
• NULL is not equal to anything — not even itself. Always use IS NULL / IS NOT NULL.
• COALESCE(col, default) substitutes a default for NULL without changing the stored data.
• ORDER BY is required for deterministic pagination — storage order is undefined.
• LIMIT without ORDER BY returns arbitrary rows — the database makes no guarantees.
• Prefer explicit column lists over SELECT * in application code to avoid schema coupling.

---

6. Next Steps

• SQL Joins — Relate data across multiple tables using INNER JOIN, LEFT JOIN, and more. Prerequisite: this lesson.
• SQL Aggregation — Summarize data with COUNT, SUM, AVG, GROUP BY, and window functions. Prerequisite: fundamentals and joins.
• SQL Schema — Design tables, choose data types, define constraints, and create indexes.
• Backend overview — See how these SQL queries map to Prisma ORM and PostgreSQL in the fogserv.cloud stack.

---

Change Log

• 2026-08-29: Migrated to 6-section canonical template (2026-08-29)
• 2026-08-29: Expanded from stub to full entry-level lesson (sections, code, Next Steps, Change Log)