Review
So you think you can count?
10. New keyword: CAST()
Rounding functions and more
Review and practice

Instruction

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

There is one problem with this method: What if both numbers are given as columns, like HP / Level? We need to use another trick: explicitly converting one column to another data type. This procedure is called casting, and it uses the structure shown below:

SELECT
  CAST(HP AS NUMERIC) / Level AS Result
FROM Character;

CAST(column AS type) changes the column to the specified type. In T-SQL, there are three kinds of numeric data types:

  • integer data types with names like BIT, INT, SMALLINT, BIGINT, etc.;
  • exact numeric data types (i.e. decimal types) with names like NUMERIC, DECIMAL, MONEY, and SMALLMONEY;
  • inexact numeric data types with names like FLOAT and REAL.

Data types are very tricky in databases. Each database has its own name and ranges for numeric data types.

When dividing two integers in this course, we'll always cast the numerator to a NUMERIC data type. In a real-world application, you may need a different precision and a different casting (e.g., casting both numbers to REAL).

Exercise

For each character, show its name, level, and the HP / MP as Ratio.

Cast HP to a NUMERIC data type to get a precise result.