String types (VARCHAR, CHAR, TEXT) in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output length difference between CHAR and VARCHAR

Consider a MySQL table users with a name column defined as CHAR(10) and another column nickname defined as VARCHAR(10). If you insert the value 'John' into both columns, what will be the result of SELECT LENGTH(name), LENGTH(nickname) FROM users WHERE id = 1;?

MySQL

CREATE TABLE users (id INT PRIMARY KEY, name CHAR(10), nickname VARCHAR(10));
INSERT INTO users VALUES (1, 'John', 'John');
SELECT LENGTH(name), LENGTH(nickname) FROM users WHERE id = 1;

A LENGTH(name) = 10, LENGTH(nickname) = 4

B LENGTH(name) = 4, LENGTH(nickname) = 10

C LENGTH(name) = 4, LENGTH(nickname) = 4

D LENGTH(name) = 10, LENGTH(nickname) = 10

💡 Hint

Remember that CHAR pads the string with spaces to its fixed length, while VARCHAR stores only the actual string length.

CHAR columns always store fixed-length strings, padding with spaces if needed. VARCHAR stores only the actual string length. So LENGTH(name) returns 10 (4 characters + 6 spaces), LENGTH(nickname) returns 4.

🧠 Conceptual

intermediate

Choosing between TEXT and VARCHAR for large strings

Which of the following is a key difference between TEXT and VARCHAR types in MySQL when storing large strings?

A TEXT columns are stored inline with the table always, VARCHAR is stored off-page.

B VARCHAR columns can store unlimited data, TEXT columns have a strict 255 character limit.

C TEXT columns can store larger amounts of data but cannot have default values, while VARCHAR has size limits but can have defaults.

D VARCHAR columns cannot be indexed, TEXT columns can be indexed fully.

💡 Hint

Think about size limits and default value support.

TEXT can store large data but does not support default values. VARCHAR has size limits (up to 65535 bytes depending on row size) but supports defaults. TEXT is stored off-page in many cases.

📝 Syntax

advanced

Valid syntax for defining a VARCHAR column

Which of the following MySQL column definitions for a VARCHAR type is syntactically correct?

A username VARCHAR(256) NOT NULL DEFAULT 'guest'

B username VARCHAR NOT NULL DEFAULT 'guest'

C username VARCHAR(0) NOT NULL DEFAULT 'guest'

D username VARCHAR(65536) NOT NULL DEFAULT 'guest'

💡 Hint

Check the required length specification and valid length range for VARCHAR.

VARCHAR requires a length between 1 and 65535 bytes (depending on row size). Omitting length or using 0 or exceeding max length is invalid syntax.

❓ optimization

advanced

Optimizing storage for fixed-length strings

You have a column that stores US state abbreviations, always exactly 2 characters. Which data type choice is most storage-efficient and why?

A TEXT because it can store any length and is flexible.

B CHAR(2) because it stores fixed-length strings without overhead.

C VARCHAR(2) because it saves space by storing only actual length.

D CHAR(10) to allow future longer codes.

💡 Hint

Consider fixed length and storage overhead.

CHAR(2) is best for fixed-length data because it uses fixed storage and minimal overhead. VARCHAR(2) stores length bytes and actual data, so slightly more overhead. TEXT is overkill and less efficient.

🔧 Debug

expert

Why does this TEXT column default cause an error?

Given the table creation statement:
CREATE TABLE articles (id INT PRIMARY KEY, content TEXT NOT NULL DEFAULT '');
Why does this statement cause an error in MySQL?

A NOT NULL is not allowed on TEXT columns.

B PRIMARY KEY must be on a VARCHAR or CHAR column, not INT.

C The empty string '' is not a valid default for any string type.

D TEXT columns cannot have default values, so DEFAULT '' is invalid.

💡 Hint

Check MySQL rules about default values for TEXT columns.

MySQL does not allow default values on TEXT or BLOB columns. Attempting to set DEFAULT '' on a TEXT column causes an error.