Revision
So you think you can count
9. New keyword CAST
Round and other functions
Revision and practice

Instruction

Ha! It worked this time, didn't it? That's how you can force the desired result in SQL.

There is one 'but'. What can we do if both numbers are given as columns, e.g. hp/level? We need to use another trick: convert one type to another explicitly. This procedure is called casting and it uses the structure shown in the example below:

SELECT CAST(hp AS numeric)/level
FROM character;

CAST(column AS type) changes the column to the specified type.

In SQL standard there are three kinds of numeric data types:

  • integer data types. There are types with names like INTEGER, INT, SMALLINT, BIGINT, etc.
  • exact numeric data types (=decimal types). These are types with names like NUMERIC and DECIMAL.
  • inexact numeric data types, with names like FLOAT, REAL, DOUBLE PRECISION.
Data types are very tricky in databases. Each database has its own names and ranges for numeric data types. To get accurate information about data types supported in your database check its documentation!

In this course whenever we're doing division of two integers, we'll always cast the numerator to a NUMERIC data type. In your real-world application, you may need a different precision and a different casting (for example cast both numbers to REAL).

Exercise

For each character, show its name, level and the hp/mp ratio (column name ratio). Use casting to get a precise result.

Stuck? Here's a hint!

You can rename columns with the keyword AS.

Console

Code editor

Result

TableConsole