Section 10 - Revisiting Data Types

Question 1

What might be one reason for working with CHAR instead of VARCHAR?

Answer 1

For a reminder, VARCHAR is a variable character length, whereas CHAR is a set character length.

• Maybe we’re working with something like a state field, where it always needs to be 2 characters long.
• If the field you need is a set character length always, sometimes it makes sense to set use CHAR, because CHAR speeds up data searches compared to VARCHAR. It’s not normally noticeable, though when working with massive amounts of data, it can become important.

Question 2

What happens when you have a CHAR field, say with a length 5, and an entry is inserted that’s over that limit?

Answer 2

The entry is truncated.

Question 3

If you have a CHAR field, say with a length of 10, and data is inserted under that length, what happens?

Answer 3

Additional spaces are added to the data to get the length to 10.

In this way, CHAR would allocate 10 characters to each piece of data inserted into the column.

Question 4

If you have a CHAR field, and there are entries under the specified character length, does MySQL show you the additional spaces when the shorter-than-specified entries are printed?

Answer 4

NO, it does not.

That is, unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

Question 5

What is the DECIMAL numeric type.

Answer 5

It is used for decimals. (duh)

Question 6

How is the DECIMAL numeric type formatted?

Answer 6

DECIMAL(1st_number, 2nd_number)

The 1st number is the total number of digits allocated.
The 2nd number is the digits after the decimal point.

For example, the number 12.34 would be represented as DECIMAL(4, 2)

The number 1587.394 would be
DECIMAL(7, 3)

The number 12345 would be DECIMAL(5,0)

Question 7

Recall the DECIMAL numeric type.

DECIMAL(4, 2)

What do the numbers 4 and 2 represent in the above example?

Answer 7

The 4 represents the total number of digits, whereas the 2 represents the number of digits after the period.

Question 8

Write a command for creating a table.

• That table should be called animals, and in that table should be an animal_name column, and an animal_weight column, which should be a decimal with 4 digits before the period and 2 digits after the period.

Answer 8

CREATE TABLE animals(animal_name VARCHAR(100), animal_weight DECIMAL(6, 2));