>_ IzzyLab

Hands-on task

This task is step 10 of the course “SQL with SQLite”

SQLite: categorization with CASE

medium~7 min

The database /root/school.db has a table students(name TEXT, score INTEGER). Write a query with a CASE expression that translates the numeric score into a letter grade by the rule: score >= 90 → A, score >= 70 (but below 90) → B, otherwise → C. Write the result to /root/grades.txt in the format name|grade.

What you'll practise:
  • case
  • conditional expressions
  • categorization
  • select
  • sqlite3

Topic breakdown

CASE is a conditional expression inside SQL, the analog of "if — else": it goes through the branches WHEN <condition> THEN <value> from top to bottom and returns the value of the first one that fires, while ELSE covers the remaining cases. The order of branches is critical — with overlapping thresholds the "stronger" condition is put first, otherwise a row will fall into the wrong category. Such an expression is placed right in the SELECT next to the other columns; text values are put in single quotes. Columns in sqlite3 output are separated by |.

What's checked

  1. high score got grade A
  2. all scores, including boundary ones, categorized correctly

What it looks like