Compute new columns
Calculate values in SELECT and label rows with CASE WHEN. About 9 minutes.
SELECT can calculate
A column in SELECT doesn't have to be a stored column. It can be a calculation on columns: price times quantity, a mark out of 100, or a name plus a city. Give the result a name with AS so you can read it.
The calculation is done for every row, and nothing in the table changes. One trap: dividing two whole numbers gives a whole number in SQLite, so 7 / 2 is 3. Multiply by 1.0 first, or divide by a decimal.
SELECT item,
price * qty AS total,
round(price * 1.0 / qty, 2) AS each
FROM orders;CASE WHEN labels rows
CASE WHEN is SQL's if / else. Give it a list of tests in order; the first one that is true decides the answer for that row. ELSE covers everything left, and without it the leftovers become NULL.
Write the strictest test first, because once a row matches, the rest are skipped.
CASE WHEN score >= 90 THEN 'A'
WHEN score >= 75 THEN 'B'
ELSE 'C'
END AS grade| student | score |
|---|---|
| Kabir | 92 |
| Isha | 78 |
| Dev | 55 |
| Mira | 90 |
92 is at least 90: the first test is true, grade A.
In SQLite, what does SELECT 7 / 2 return?
- A3
- B3.5
- C4
Show the answer
3. Both are integers, so the division is integer division and drops the fraction.
Report card
The marks table has one row per student per subject. Show the student, the subject and a grade column that is Distinction for a score of 75 or more, Pass for 40 to 74, and Fail below 40. Order by student, then subject.
marks(id, student, subject, score)
Up to 2,000 rows