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.

Practice

1. Which MySQL integer type should you use to store a small number between 0 and 255 efficiently?

easy

A. BIGINT

B. INT

C. TINYINT

D. SMALLINT

Solution

1. Step 1: Understand the range of TINYINT and compare with other types

   TINYINT can store values from 0 to 255 when unsigned, which fits small numbers efficiently. INT and BIGINT store much larger numbers but use more space. SMALLINT also uses more space unnecessarily.

2. Final Answer:

   TINYINT -> Option C

3. Quick Check:

   Small numbers (0-255) = TINYINT [OK]

Hint: Use TINYINT for numbers up to 255 to save space [OK]

Common Mistakes:

• Choosing INT for small numbers wastes space
• Confusing TINYINT with SMALLINT
• Not considering unsigned range

2. Which of the following is the correct syntax to declare a NOT NULL BIGINT column named user_id in MySQL?

easy

A. user_id BIGINT;

B. BIGINT user_id;

C. user_id INT BIG;

D. user_id BIGINT NOT NULL;

Solution

1. Step 1: Check correct order of declaration

   In MySQL, the column name comes first, then the data type, optionally followed by constraints like NOT NULL. Only A follows this: user_id BIGINT NOT NULL;.

2. Final Answer:

   user_id BIGINT NOT NULL; -> Option D

3. Quick Check:

   Column name + type + constraints = correct syntax [OK]

Hint: Column name first, then type, then constraints [OK]

Common Mistakes:

• Putting type before column name
• Using invalid order of keywords
• Missing semicolon in some contexts

3. Given the table orders with a column order_count TINYINT UNSIGNED, what will be the result of this query?

INSERT INTO orders (order_count) VALUES (300);
SELECT order_count FROM orders;

medium

A. 300

B. Error: Out of range value

C. 0

D. 44

Solution

1. Step 1: Understand TINYINT UNSIGNED range and insert effect

   TINYINT UNSIGNED stores 0-255 only. 300 exceeds range, so INSERT throws 'Error: Out of range value' (default strict mode).

2. Final Answer:

   Error: Out of range value -> Option B

3. Quick Check:

   Value > 255 in TINYINT UNSIGNED causes error [OK]

Hint: Check value fits type range before insert [OK]

Common Mistakes:

• Assuming values wrap around silently
• Ignoring unsigned range limits
• Expecting automatic truncation without error

4. You have a column defined as INT but you want to store numbers larger than 4,294,967,295. Which change fixes the problem?

medium

A. Change the column type to BIGINT

B. Change the column type to TINYINT

C. Add UNSIGNED to the INT column

D. Use VARCHAR instead of INT

Solution

1. Step 1: Understand INT range limits and select proper type

   Signed INT max ~2.1 billion, UNSIGNED INT max 4,294,967,295. BIGINT handles much larger (up to ~9 quintillion).

2. Final Answer:

   Change the column type to BIGINT -> Option A

3. Quick Check:

   BIGINT stores bigger numbers than INT [OK]

Hint: Use BIGINT for numbers bigger than INT max [OK]

Common Mistakes:

• Using TINYINT which is smaller
• Adding UNSIGNED but still too small
• Switching to VARCHAR loses numeric benefits

5. You want to store user scores that can be very large, but you also want to save space when scores are small. Which MySQL integer type strategy is best?

hard

A. Use INT for most scores and BIGINT only if scores exceed INT range

B. Use TINYINT and switch to BIGINT if needed later

C. Always use BIGINT for all scores

D. Use BIGINT but compress data manually

Solution

1. Step 1: Balance storage and range needs

   TINYINT too small; always BIGINT wastes space. Use INT initially (efficient for small/medium), ALTER to BIGINT later if needed.

2. Final Answer:

   Use INT for most scores and BIGINT only if scores exceed INT range -> Option A

3. Quick Check:

   Balance size and range with INT then BIGINT [OK]

Hint: Start with INT, upgrade to BIGINT if needed [OK]

Common Mistakes:

• Always using BIGINT wastes space
• Starting with TINYINT causes overflow
• Manual compression is complex and error-prone