ROUND, ABS and CAST

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — ROUND

Codemon's weights were measured with decimals. ROUND(x, n) keeps n decimals — none when n is omitted:

SELECT codemon, weight_kg, ROUND(weight_kg, 1) AS one_decimal, ROUND(weight_kg) AS whole FROM sightings LIMIT 4;

You already used ROUND on averages; it matters even more when the data itself has decimals:

SELECT ROUND(AVG(weight_kg), 2) AS avg_weight, ROUND(SUM(weight_kg), 1) AS total_weight FROM sightings;

ROUND returns a REAL

ROUND(7.0) gives 7.0, not 7, and ROUND(4.5) gives 5.0 (halves round away from zero). To get an integer, combine with CAST, coming up in this chapter.

Exercise 2 — ABS: distance, not direction

ABS(x) removes the sign. It turns a difference into a distance — here, how far each weight is from the 10 kg of the field guide:

SELECT codemon, weight_kg, ROUND(ABS(weight_kg - 10), 2) AS gap FROM sightings ORDER BY gap LIMIT 3;

Without ABS, Codémons lighter than 10 kg would get negative gaps and sort before the closest one. A few more examples:

SELECT ABS(-3) AS a, ABS(3) AS b, ABS(2.5 - 7) AS c;

Exercise 3 — CAST: changing the type

Column level_text holds numbers typed as text — and sometimes no number at all. Sorting text is alphabetical: '9' comes after '14':

SELECT level_text FROM sightings WHERE level_text NOT IN ('', 'unknown') ORDER BY level_text;

CAST(value AS INTEGER) converts the text into an actual number, and the sort becomes numeric:

SELECT level_text, CAST(level_text AS INTEGER) AS level FROM sightings WHERE level_text NOT IN ('', 'unknown') ORDER BY level;

Garbage in, zero out

CAST('unknown' AS INTEGER) and CAST('' AS INTEGER) give 0, silently. Filter such values out before averaging, or they drag the result down: with them, the average level is 8.0 instead of 11.0.

Exercise 4 — Integer division

What does SELECT 7 / 2, CAST(7 AS REAL) / 2 return?

Options
• 3 and 3.5
• 3.5 and 3.5
• 3 and 3
• 4 and 3.5

Hint : Two integers divide into an integer; CAST one side to REAL to keep the decimals.

Exercise 5 — Average level, properly

Complete the query so that it returns the average reported level, ignoring the rows where the level is missing ('' or 'unknown').

Hint : Convert the text to a number, and keep only the rows that are not missing.

Exercise 6 — Numbers in, numbers out

Match each expression with its result.

Items to match
• ROUND(12.333, 1)
• ROUND(4.5)
• ABS(-2.5)
• CAST('12' AS INTEGER) + 1
Definitions
• 12.3
• 5.0
• 2.5
• 13

Exercise 7 — Not a number

What does CAST('unknown' AS INTEGER) return in SQLite?

Options
• 0
• NULL
• An error
• 'unknown'

Hint : SQLite converts what it can and gives up silently.

Exercise 8 — Closest to the guide

The field guide gives 10 kg as the typical weight of an adult Codémon. Professor Pi wants the sightings closest to it.

Return codemon (as typed), weight (weight_kg rounded to 1 decimal) and gap_to_guide (the absolute difference between the weight and 10, rounded to 1 decimal). Sort by gap_to_guide ascending and keep the first 5 rows.

Starter code
-- Write your query below
Back to the course