Help improve SovranCode?

Google Analytics can measure anonymous usage after you choose Allow. Essential account and progress features work either way.

SovranCode
SQL: Query, Model, and Analyze Data Text, Dates, Patterns, and Conditional Results
This device
Course contentsText, Dates, Patterns, and Conditional Results · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
SQL: Query, Model, and Analyze Data12 complete · 20 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
PREVIOUS LESSONSorting, Limiting, and Distinct Values
NEXT LESSONAggregate Functions
Reading and filtering data · Lesson 08 130 min

Text, Dates, Patterns, and Conditional Results

Real queries rarely stop at exact equality. Editors search text, reports select a date window, and interfaces turn raw values into labels. This lesson combines those needs without changing the stored article rows or hiding the rules that produced a result.

What you will leave with

You will be able to normalize text for a result, use LIKE patterns deliberately, filter an ISO date range with an exclusive upper boundary, distinguish missing dates from date comparisons, write a searched CASE expression, and use COALESCE for a missing display value.

Transform values, then explain the result

TextUse LOWER, UPPER, and TRIM for readable, consistent output.
PatternsUse LIKE with wildcards that match the intended text shape.
DatesUse explicit boundaries that include every day in a reporting window.
ConditionsUse CASE and COALESCE to name a result without changing the source.
SOURCEarticlestitle · published_on · views · author_name

Nine rows include drafts, missing authors, and dates around September.

FILTERSeptember SQL articlesLIKE + date range + status

Only two rows pass all three rules.

RESULTReadable digestdisplay_author · reach_label

Computed labels are returned without modifying stored columns.

Text functions shape a result without changing stored values

LOWER(title) produces a lowercase value useful for a simple search comparison. UPPER(category) can produce an uppercase label. TRIM(author_name) removes leading and trailing spaces, so the deliberately padded author on CSS Layouts displays cleanly. These functions create result values; they do not rewrite the table. Function names are common across SQL systems, but Unicode case handling and collations can differ.

SQL BROWSER RUNNER

Normalize a display value

Compare each stored title and author with a lowercase title and trimmed author in the result.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

SELECT
  article_id,
  title,
  LOWER(title) AS search_title,
  TRIM(author_name) AS clean_author
FROM articles
ORDER BY article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add UPPER(category) AS category_label. Check that article 103 loses its surrounding author spaces only in the result.

LIKE matches a text pattern

The percent sign in LIKE 'sql%' means zero or more characters after sql. Using LOWER(title) makes this SQLite exercise find titles beginning with SQL regardless of their ASCII letter case. It matches SQL Basics, SQL Patterns, and SQL Window Functions. It does not match Learning SQL because SQL appears at the end of that title.

SQL BROWSER RUNNER

Find titles that start with SQL

Run a prefix search over the nine article titles.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

SELECT article_id, title
FROM articles
WHERE LOWER(title) LIKE 'sql%'
ORDER BY article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change sql% to %sql% and see Learning SQL join the result. Then try %patterns to find a suffix.

Case rules are not universal

SQLite LIKE is usually case-insensitive for ASCII text by default, but case and accent behavior vary by database and collation. The explicit LOWER makes the intent visible for these examples; production search may need a chosen collation, index strategy, or dedicated search feature.

Percent and underscore mean different things

% matches any sequence of characters, including an empty sequence. _ matches exactly one character. The first query below finds every slug starting with sql-. The second asks for sql-, any one character, then a, then anything else. That matches sql-basics and sql-patterns, but not sql-window-functions. For a literal percent or underscore, use an explicit escape convention supported by your SQL dialect.

SQL BROWSER RUNNER

Compare two LIKE wildcards

Inspect both result sets to see how a one-character wildcard narrows a prefix.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

SELECT article_id, slug
FROM articles
WHERE slug LIKE 'sql-%'
ORDER BY article_id;

SELECT article_id, slug
FROM articles
WHERE slug LIKE 'sql-_a%'
ORDER BY article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the second pattern to sql-__n% and predict which slug appears. Comment out one query to focus on a single result set.

A half-open date range covers a full month

This SQLite runner stores dates as fixed-width ISO text in YYYY-MM-DD form. With valid, consistently formatted dates, lexical comparison follows calendar order. The September query uses published_on >= '2026-09-01' and published_on < '2026-10-01'. It includes September 30 but excludes October 1 and the August article. The upper bound is exclusive, which also works well when a production column includes times throughout the final day.

SQL BROWSER RUNNER

Read all September publications

Find the five published articles whose ISO dates fall within September 2026.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

SELECT article_id, title, published_on
FROM articles
WHERE status = 'published'
  AND published_on >= '2026-09-01'
  AND published_on < '2026-10-01'
ORDER BY published_on, article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the upper boundary to 2026-09-30 and observe why Data Quality disappears. Restore the exclusive October boundary.

Use the database date type in a real schema

SQLite has flexible date storage, so this browser lesson uses ISO text to keep the example runnable. PostgreSQL and other engines provide dedicated DATE and timestamp types. Choose the appropriate type, define the time zone for timestamps, and avoid mixing date formats or comparing local times to UTC boundaries.

A missing date needs IS NULL

The draft JavaScript DOM article has no publication date. It does not match a September comparison because comparing NULL with a date yields unknown. Use published_on IS NULL to find unscheduled articles and IS NOT NULL to select rows with a date. An empty string would be a different stored value and should not be used as a substitute for missing data.

SQL BROWSER RUNNER

Separate scheduled from unscheduled articles

Compare the one missing publication date with eight dated rows.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

SELECT article_id, title, published_on
FROM articles
WHERE published_on IS NULL;

SELECT article_id, title, published_on
FROM articles
WHERE published_on IS NOT NULL
ORDER BY published_on, article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Replace IS NULL with = NULL in the first query and explain why no row appears. Restore the correct null test.

CASE turns conditions into a readable result label

A searched CASE checks WHEN conditions in order and returns the value from the first true branch. Here 1,000 or more views means Popular, 500 through 999 means Growing, and anything else means New. The order matters: test the higher threshold first. END AS reach_label names the calculated result column; it does not add a stored column. Include an ELSE when unmatched rows should get a deliberate label rather than NULL.

SQL BROWSER RUNNER

Classify article reach

Turn raw view counts into clear Popular, Growing, and New result labels.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

SELECT
  article_id,
  title,
  views,
  CASE
    WHEN views >= 1000 THEN 'Popular'
    WHEN views >= 500 THEN 'Growing'
    ELSE 'New'
  END AS reach_label
FROM articles
ORDER BY article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Swap the two WHEN branches and observe which high-view articles are mislabeled. Restore the higher threshold first.

COALESCE supplies a fallback for NULL

COALESCE(TRIM(author_name), 'Editorial team') returns the trimmed author when present and the fallback when the author is NULL. SQL Patterns and SQL Window Functions have missing authors, so both display Editorial team. A blank string is not NULL; COALESCE alone does not treat empty text as missing. If blank input is invalid, reject or normalize it at the data boundary.

SQL BROWSER RUNNER

Display a fallback author

Fill missing author labels while preserving the stored NULL values.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

SELECT
  article_id,
  title,
  COALESCE(TRIM(author_name), 'Editorial team') AS display_author
FROM articles
ORDER BY article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the fallback label, then use SELECT * FROM articles to confirm the stored author_name values are unchanged.

Compose transformations without hiding the source rule

Queries can transform several output values while keeping the filter explicit. The example uppercases Data and Web category labels, then uses CASE to display Unscheduled for a missing publication date. It still returns the draft article because the filter checks category, not status. Read WHERE to learn which rows qualify and the SELECT list to learn how those rows will appear.

SQL BROWSER RUNNER

Shape a readable article result

Combine a category filter, an uppercase label, and a missing-date label.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

SELECT
  article_id,
  title,
  UPPER(category) AS category_label,
  CASE
    WHEN published_on IS NULL THEN 'Unscheduled'
    ELSE published_on
  END AS display_date
FROM articles
WHERE category IN ('Data', 'Web')
ORDER BY article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add status = 'published' to WHERE and observe which row disappears. Try adding a reach_label CASE to the output.

Common text and date mistakes

LIKE 'sql'

Missing wildcard

That pattern matches only the whole value sql. Add % for a prefix or substring search.

LIKE 'sql_%'

Accidental wildcard

An underscore means any one character. Escape it when you need a literal underscore.

published_on <= '2026-09-30'

Incomplete final day

For timestamp values, a midnight upper bound can exclude later hours on September 30. Use an exclusive October 1 bound.

COALESCE(author_name, fallback)

Blank is not NULL

An empty or whitespace-only string stays present unless you normalize or reject it deliberately.

Independent lab: build a September SQL digest

Return published articles whose titles start with SQL and whose publication dates fall in September 2026. Include a readable author label and a reach label: Popular for at least 900 views, Growing otherwise. The starter query should return SQL Patterns first and SQL Window Functions second. Change the date range to include August, then explain why SQL Basics joins the result. Finally, make the reach threshold 1,000 and predict which label changes.

SQL BROWSER RUNNER

Publish a two-article SQL digest

Combine text matching, a half-open date window, COALESCE, CASE, and stable ordering.

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  published_on TEXT,
  views INTEGER NOT NULL,
  author_name TEXT
);

INSERT INTO articles
  (article_id, title, slug, category, status, published_on, views, author_name)
VALUES
  (101, 'SQL Basics', 'sql-basics', 'Data', 'published', '2026-08-28', 1200, 'Ada'),
  (102, 'SQL Patterns', 'sql-patterns', 'Data', 'published', '2026-09-02', 950, NULL),
  (103, 'CSS Layouts', 'css-layouts', 'Web', 'published', '2026-09-10', 800, ' Grace Hopper '),
  (104, 'JavaScript DOM', 'javascript-dom', 'Web', 'draft', NULL, 0, 'Linus'),
  (105, 'Data Quality', 'data-quality', 'Data', 'published', '2026-09-30', 1600, 'Ada'),
  (106, 'Python APIs', 'python-apis', 'Backend', 'published', '2026-10-01', 1100, 'Omar'),
  (107, 'SQL Window Functions', 'sql-window-functions', 'Data', 'published', '2026-09-15', 400, NULL),
  (108, 'Accessible Forms', 'accessible-forms', 'Web', 'published', '2026-09-20', 200, 'Grace'),
  (109, 'Learning SQL', 'learning-sql', 'Data', 'published', '2026-10-05', 350, 'Mina');

-- Build a September digest of published SQL articles.
SELECT
  article_id,
  title,
  published_on,
  COALESCE(TRIM(author_name), 'Editorial team') AS display_author,
  CASE
    WHEN views >= 900 THEN 'Popular'
    ELSE 'Growing'
  END AS reach_label
FROM articles
WHERE status = 'published'
  AND LOWER(title) LIKE 'sql%'
  AND published_on >= '2026-09-01'
  AND published_on < '2026-10-01'
ORDER BY published_on, article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Verify IDs 102 and 107 in date order. Extend the lower boundary into August, then change the Popular threshold to 1000 and compare labels.

Lab review criteria

The September result has exactly two rows. The draft and October articles are excluded, and neither missing author appears as a blank label. The 950-view article is Popular at the initial threshold; the 400-view article is Growing. Changing the threshold changes only the computed label, not the stored view count.

Lesson review

LOWER, UPPER, and TRIM transform output text without changing stored values. LIKE supports sequence and single-character wildcards. A half-open date range makes month boundaries clear, while IS NULL handles missing dates. CASE chooses a result from ordered conditions, and COALESCE provides a fallback for NULL. Keep the source filter visible and test the edge dates and missing values before trusting a report.

  • I can distinguish a transformed result from a stored value.
  • I can explain what % and _ mean in a LIKE pattern.
  • I can filter a month with an inclusive start and exclusive next-month boundary.
  • I can test a missing date with IS NULL.
  • I can order CASE branches from specific to general.
  • I can use COALESCE for NULL without confusing it with blank text.
KNOWLEDGE CHECK

Check text, date, and conditional logic

Answer all ten questions, then rerun any example whose boundary or output surprised you.

01What does TRIM(author_name) change in a SELECT result?
02What does the percent sign mean in LIKE 'sql%'?
03What does the underscore mean in a LIKE pattern?
04Why should a production text search specify its case and collation behavior?
05Which date window includes every September 2026 date but excludes October 1?
06Why do ISO date strings compare in calendar order in this SQLite exercise?
07How do you find the draft article with no publication date?
08Which CASE branch wins when views is 1,200?
09What does COALESCE(author_name, 'Editorial team') do for an empty string?
10Which articles belong in the September SQL digest from the lesson dataset?
PREVIOUS LESSONSorting, Limiting, and Distinct Values
NEXT LESSONAggregate Functions
ON THIS PAGEText, Dates, Patterns, and Conditional ResultsLesson mapText functionsLIKEWildcardsDate rangesMissing datesCASECOALESCECombined resultCommon mistakesIndependent labLesson reviewKnowledge check
Course contents