String types (VARCHAR, CHAR, TEXT) in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a column that stores variable-length strings up to 100 characters.

MySQL

CREATE TABLE users (username [1](100));

Drag options to blanks, or click blank then click option'

ATEXT

BINT

CCHAR

DVARCHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT for short strings wastes space.

Using INT for string data causes errors.

The VARCHAR type stores variable-length strings with a maximum length you specify, here 100 characters.

2fill in blank

medium

Complete the code to create a fixed-length string column of 10 characters.

MySQL

CREATE TABLE products (code [1](10));

Drag options to blanks, or click blank then click option'

ABLOB

BVARCHAR

CCHAR

DTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR when fixed length is needed wastes space.

Using BLOB for text data is incorrect.

The CHAR type stores fixed-length strings. Here, it always uses 10 characters.

3fill in blank

hard

Fix the error in the code to create a column for long text data.

MySQL

CREATE TABLE articles (content [1]);

Drag options to blanks, or click blank then click option'

ACHAR

BTEXT

CVARCHAR

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR for long text data.

Using INT for text data is invalid.

The TEXT type is used for long text data and does not require a length specifier (unlike VARCHAR or CHAR).

4fill in blank

hard

Fill both blanks to create a table with a fixed-length code and a variable-length description.

MySQL

CREATE TABLE items (code [1](5), description [2](255));

Drag options to blanks, or click blank then click option'

ACHAR

BTEXT

CVARCHAR

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT for code wastes space.

Using INT for text columns is wrong.

CHAR(5) is for fixed-length codes, and VARCHAR(255) is for variable-length descriptions.

5fill in blank

hard

Fill all three blanks to create a table with a variable-length name, fixed-length code, and a long text notes column.

MySQL

CREATE TABLE records (name [1](100), code [2](8), notes [3]);

Drag options to blanks, or click blank then click option'

AVARCHAR

BCHAR

CTEXT

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using INT for text columns.

Using CHAR or VARCHAR for long notes.

VARCHAR(100) for variable-length names, CHAR(8) for fixed-length codes, and TEXT for long notes.

Practice

1. Which MySQL string type is best for storing names that can vary in length but are usually short?

easy

A. VARCHAR

B. CHAR

C. TEXT

D. BLOB

Solution

1. Step 1: Understand string types for short variable-length text

   VARCHAR stores variable-length strings efficiently, saving space for shorter values.

2. Step 2: Compare with other types

   CHAR is fixed-length and wastes space if data varies; TEXT is for large text; BLOB is for binary data.

3. Final Answer:

   VARCHAR -> Option A

4. Quick Check:

   Variable-length short text = VARCHAR [OK]

Hint: Use VARCHAR for short, varying length text [OK]

Common Mistakes:

• Choosing CHAR wastes space for variable length
• Using TEXT for small strings is inefficient
• Confusing BLOB with text types

2. Which of the following is the correct syntax to declare a column named description that can hold up to 255 characters using VARCHAR in MySQL?

easy

A. description VARCHAR(255)

B. description CHAR(255)

C. description TEXT(255)

D. description VARCHAR

Solution

1. Step 1: Recall VARCHAR syntax

   VARCHAR requires a length in parentheses, e.g., VARCHAR(255).

2. Step 2: Check options for correct syntax

   description VARCHAR(255) uses correct syntax; description TEXT(255) uses wrong type; description CHAR(255) uses fixed-length CHAR; description VARCHAR misses length.

3. Final Answer:

   description VARCHAR(255) -> Option A

4. Quick Check:

   VARCHAR needs length in parentheses [OK]

Hint: Always specify length in parentheses for VARCHAR [OK]

Common Mistakes:

• Omitting length for VARCHAR
• Adding length to TEXT type
• Confusing CHAR and VARCHAR syntax

3. Given the table users with a username CHAR(10) column, what will be the result of this query?

INSERT INTO users (username) VALUES ('bob');
SELECT username, LENGTH(username) FROM users WHERE username = 'bob';

medium

A. username = 'bob', LENGTH(username) = 3

B. username = 'bob ', LENGTH(username) = 3

C. username = 'bob', LENGTH(username) = 10

D. username = 'bob ', LENGTH(username) = 10

Solution

1. Step 1: Understand CHAR behavior on insert

   CHAR(10) stores fixed length strings, padding with spaces to length 10.

2. Step 2: Check LENGTH function on padded string

   LENGTH counts all characters including spaces, so LENGTH('bob ') = 10.

3. Final Answer:

   username = 'bob ', LENGTH(username) = 10 -> Option D

4. Quick Check:

   CHAR pads with spaces, LENGTH counts all chars [OK]

Hint: CHAR pads with spaces; LENGTH counts all characters [OK]

Common Mistakes:

• Assuming LENGTH returns 3 for 'bob'
• Ignoring space padding in CHAR
• Confusing CHAR with VARCHAR behavior

4. You have a column defined as notes TEXT. You try to insert a string longer than 65,535 characters and get an error. What is the likely cause and how to fix it?

medium

A. VARCHAR max length is 65,535; use TEXT instead

B. TEXT type has a max length of 65,535 bytes; use MEDIUMTEXT for longer text

C. CHAR cannot store large text; use VARCHAR(65535)

D. TEXT type has no length limit; error is from syntax

Solution

1. Step 1: Recall TEXT max size limits

   TEXT can store up to 65,535 bytes; longer text causes error.

2. Step 2: Choose correct larger text type

   MEDIUMTEXT supports up to 16 million bytes, suitable for longer text.

3. Final Answer:

   TEXT type has a max length of 65,535 bytes; use MEDIUMTEXT for longer text -> Option B

4. Quick Check:

   TEXT max 65,535 bytes; MEDIUMTEXT for more [OK]

Hint: TEXT max 65k chars; use MEDIUMTEXT for bigger text [OK]

Common Mistakes:

• Thinking TEXT has unlimited length
• Using VARCHAR for very large text
• Confusing CHAR with TEXT size limits

5. You want to store user comments that can be very long but also want to optimize storage for short comments. Which MySQL string type should you choose and why?

hard

A. Use VARCHAR(500) to save space for short comments and allow long ones

B. Use CHAR(500) to fix length and speed up queries

C. Use TEXT because it stores large text without fixed length

D. Use VARCHAR(65535) to cover all comment lengths

Solution

1. Step 1: Consider storage needs for very long text

   VARCHAR max length is limited (usually 65,535 bytes total row size), so very long comments may exceed it.

2. Step 2: Evaluate TEXT type for large variable text

   TEXT stores large text efficiently without fixed length and supports very long strings.

3. Final Answer:

   Use TEXT because it stores large text without fixed length -> Option C

4. Quick Check:

   Large variable text = TEXT type [OK]

Hint: Use TEXT for very long text, VARCHAR for shorter variable text [OK]

Common Mistakes:

• Choosing fixed-length CHAR wastes space
• Using VARCHAR too large causes errors
• Assuming VARCHAR can store unlimited text