Integer types (TINYINT, INT, BIGINT) in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of TINYINT overflow in MySQL

Consider a MySQL table with a column defined as TINYINT (signed). What will be the output of the following query?

SELECT CAST(130 AS SIGNED TINYINT) AS result;

MySQL

SELECT CAST(130 AS SIGNED TINYINT) AS result;

A 127

B -126

C 130

D 0

💡 Hint

Remember that TINYINT signed range is from -128 to 127, and values outside wrap around.

TINYINT signed can store values from -128 to 127. Casting 130 overflows and wraps around, resulting in -126.

🧠 Conceptual

intermediate

Range of BIGINT unsigned in MySQL

What is the maximum value that can be stored in a BIGINT UNSIGNED column in MySQL?

A 18446744073709551615

B 9223372036854775807

C 4294967295

D 2147483647

💡 Hint

BIGINT is 8 bytes, unsigned means all bits used for positive values.

BIGINT unsigned uses 64 bits for positive values only, so max is 2^64 - 1 = 18446744073709551615.

📝 Syntax

advanced

Correct syntax for defining an INT column with display width

Which of the following is the correct syntax to define an INT column with a display width of 5 in MySQL?

A CREATE TABLE t (col INT(5));

B CREATE TABLE t (col INT WIDTH 5);

C CREATE TABLE t (col INT DISPLAY 5);

D CREATE TABLE t (col INT SIZE 5);

💡 Hint

MySQL allows specifying display width in parentheses after the type name.

The correct syntax is INT(5). Other options are invalid keywords.

❓ optimization

advanced

Choosing integer types for storage optimization

You need to store user ages ranging from 0 to 120 in a MySQL table. Which integer type is the most storage-efficient choice without risking overflow?

A SMALLINT SIGNED

B BIGINT UNSIGNED

C TINYINT UNSIGNED

D INT SIGNED

💡 Hint

Consider the range and storage size of each integer type.

TINYINT unsigned stores 0 to 255 using 1 byte, enough for ages 0-120, saving space.

🔧 Debug

expert

Why does this BIGINT assignment cause an error?

Given the table users with a column id BIGINT SIGNED, why does this insert fail?

INSERT INTO users (id) VALUES (9223372036854775808);

A The value is too small for BIGINT SIGNED causing underflow

B Syntax error due to missing quotes around the number

C BIGINT SIGNED cannot store positive numbers

D Value exceeds BIGINT SIGNED max limit causing out-of-range error

💡 Hint

Check the maximum value allowed for BIGINT SIGNED.

BIGINT SIGNED max is 9223372036854775807. The inserted value is one more, causing out-of-range error.