Working with Single-Table Data

Simple Expressions in Queries

---

So far, our queries have mostly returned stored values more or less as they appear in the table. Often, that is enough. But many real questions also ask us to shape the output a little.

For example:

• How could we show a temporary points value without changing the stored max_points?
• How could we show 0 when a score is still missing?
• How could we return a short human-readable label instead of a raw value?

SQL can do this with expressions. An expression is a small piece of SQL that computes a value for one result row.

At this stage, expressions usually appear in the SELECT part of a query, in places where a column name could also appear.

Simple Expressions and Aliases

Suppose the question is: “How could we show each exercise and also a temporary value that is five points higher than max_points?”

SELECT
  title,
  max_points,
  max_points + 5 AS extended_points
FROM exercises;

Read this row by row:

• take the stored max_points value from one exercise row
• compute max_points + 5
• show that computed value in a result column called extended_points

This is a useful first example because it shows two ideas at once.

• max_points + 5 is an expression
• AS extended_points gives the computed result an alias, meaning a temporary output name

That alias does not change the database schema. It only changes how the query result is labeled.

That distinction matters. Expressions and aliases change the result of the query, not the stored data itself.

Loading Exercise...

Handling Missing Values with COALESCE

Sometimes the question is not only “What value is stored?” but also “What should we show if no value is stored?”

Suppose the question is: “How could we show 0 for scores that are still missing?”

SELECT
  id,
  status,
  COALESCE(score, 0) AS shown_score
FROM submissions;

COALESCE returns the first value that is not NULL.

In this example, read it as:

• if score has a value, show that value
• otherwise, show 0

This can feel subtle at first, so it is worth saying clearly: COALESCE does not fill in the database with zeros. It only changes what the query result shows.

That makes it useful when missing values are acceptable in the database, but awkward in a page or report.

Loading Exercise...

Condition-Based Output with CASE

Sometimes we do not want to show the raw value directly at all. Instead, we want the query to return a label based on a condition.

Suppose the question is: “How could we label each submission as graded or not graded?”

SELECT
  id,
  CASE
    WHEN score IS NULL THEN 'not graded'
    ELSE 'graded'
  END AS grading_status
FROM submissions;

Read it row by row:

• if the row satisfies score IS NULL, return 'not graded'
• otherwise, return 'graded'

CASE is useful when you want the result to contain simple categories or labels instead of only raw stored values.

The same structure can also be used for other small classifications, not only for missing values.

CASE can also contain more than one WHEN branch. For example:

SELECT
  id,
  CASE
    WHEN score IS NULL THEN 'not graded'
    WHEN score >= 5 THEN 'passed'
    ELSE 'needs work'
  END AS score_label
FROM submissions;

Read this from top to bottom:

• if no score is stored, return 'not graded'
• otherwise, if the score is at least 5, return 'passed'
• otherwise, return 'needs work'

This is another good place to be careful: CASE checks the WHEN branches in order and uses the first one that matches.

Loading Exercise...

Check Your Understanding

1. Why is an alias useful in a query result?
2. What does COALESCE(score, 0) mean in ordinary language?
3. When can a small CASE expression make a result easier to read?

SQL Practice

Complete the SQL sequence below to practice aliases, simple expressions, COALESCE, and CASE.

Loading Exercise...