ENUM and SET types in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ENUM column insertion

Consider a MySQL table colors with a column color defined as ENUM('red', 'green', 'blue'). What will be the output of this query?

INSERT INTO colors (color) VALUES ('yellow');
SELECT color FROM colors;

MySQL

CREATE TABLE colors (color ENUM('red', 'green', 'blue'));
INSERT INTO colors (color) VALUES ('yellow');
SELECT color FROM colors;

A The row is inserted with color as NULL

B The row is inserted with color as 'yellow'

C The query raises a syntax error

D The row is inserted with color as an empty string ('')

💡 Hint

ENUM columns accept only predefined values; invalid values are stored as empty string.

In MySQL, inserting a value not listed in ENUM stores an empty string ('') as the value. It does not raise an error or store NULL.

❓ query_result

intermediate

Counting SET values stored

Given a table user_prefs with a column prefs SET('email', 'sms', 'push'), what will be the output of this query?

INSERT INTO user_prefs (prefs) VALUES ('email,sms');
SELECT FIND_IN_SET('sms', prefs) AS sms_position FROM user_prefs;

MySQL

CREATE TABLE user_prefs (prefs SET('email', 'sms', 'push'));
INSERT INTO user_prefs (prefs) VALUES ('email,sms');
SELECT FIND_IN_SET('sms', prefs) AS sms_position FROM user_prefs;

A 0

B 2

C 1

D NULL

💡 Hint

FIND_IN_SET returns the position of a string in a comma-separated list.

The SET value 'email,sms' is stored as a string 'email,sms'. FIND_IN_SET('sms', prefs) returns 2 because 'sms' is the second item in the list.

📝 Syntax

advanced

Valid ENUM declaration

Which of the following is a valid ENUM column declaration in MySQL?

A status ENUM('new', 'pending', 'closed') NOT NULL DEFAULT 'pending'

B status ENUM('new', 'pending', 'closed') DEFAULT 1

C status ENUM('new', 'pending', 'closed') DEFAULT 'open'

D status ENUM('new', 'pending', 'closed') NOT NULL DEFAULT 0

💡 Hint

Default value must be one of the ENUM values or empty string.

Option A correctly sets a default value that is one of the ENUM options. Options B and D use numeric defaults which are invalid. Option A uses a default not in the ENUM list.

❓ optimization

advanced

Optimizing queries with SET columns

You have a table notifications with a SET column methods SET('email', 'sms', 'push'). Which query is the most efficient to find rows where 'sms' is one of the methods?

A SELECT * FROM notifications WHERE methods & 2;

B SELECT * FROM notifications WHERE methods LIKE '%sms%';

C SELECT * FROM notifications WHERE methods = 'sms';

D SELECT * FROM notifications WHERE FIND_IN_SET('sms', methods) > 0;

💡 Hint

SET columns are stored as bitmaps internally.

Option A uses bitwise AND to check if the bit for 'sms' (which is the second SET value) is set. This is more efficient than string functions or LIKE.

🧠 Conceptual

expert

Behavior of ENUM with numeric values

What happens if you define an ENUM column as ENUM('1', '2', '3') and insert the value 2 (without quotes) into it?

A The value '2' (string) is stored as ENUM index 2

B The numeric 2 is converted to string '2' and stored correctly

C The value 2 is treated as the ENUM index and stores '2' (the second ENUM value)

D The insert fails with a type mismatch error

💡 Hint

ENUM values have numeric indexes starting at 1 internally.

When inserting a number into an ENUM column, MySQL treats it as the index of the ENUM value, not as a string. So 2 stores the second ENUM value '2'.

Practice

1. What is the main difference between ENUM and SET types in MySQL?

easy

A. ENUM allows multiple values, SET allows only one value.

B. ENUM stores numbers, SET stores strings.

C. ENUM allows only one value from a list, SET allows multiple values.

D. ENUM is for dates, SET is for text.

Solution

1. Step 1: Understand ENUM type

   ENUM restricts a column to one value from a predefined list.

2. Step 2: Understand SET type

   SET allows storing zero or more values from a predefined list in one column.

3. Final Answer:

   ENUM allows only one value, SET allows multiple values -> Option C

4. Quick Check:

   ENUM = single, SET = multiple [OK]

Hint: Remember: ENUM = one choice, SET = many choices [OK]

Common Mistakes:

• Confusing which type allows multiple values
• Thinking ENUM stores numbers only
• Assuming SET is for single values

2. Which of the following is the correct syntax to create a column named colors that can store multiple colors using SET type?

easy

A. colors SET('red', 'green', 'blue')

B. colors ENUM('red', 'green', 'blue')

C. colors SET['red', 'green', 'blue']

D. colors ENUM['red', 'green', 'blue']

Solution

1. Step 1: Recall correct syntax for SET

   The correct syntax uses parentheses and single quotes: SET('value1', 'value2', ...)

2. Step 2: Identify correct option

   colors SET('red', 'green', 'blue') uses SET with parentheses and quoted values correctly.

3. Final Answer:

   colors SET('red', 'green', 'blue') -> Option A

4. Quick Check:

   SET uses parentheses and quotes [OK]

Hint: SET and ENUM use parentheses, not brackets [OK]

Common Mistakes:

• Using square brackets instead of parentheses
• Mixing ENUM syntax with SET
• Omitting quotes around values

3. Given the table products with a column features SET('wifi', 'bluetooth', 'gps'), what will be the result of this query?

SELECT features FROM products WHERE FIND_IN_SET('gps', features);

medium

A. Returns rows where 'gps' is one of the features.

B. Returns rows where features exactly equal 'gps'.

C. Returns all rows regardless of features.

D. Syntax error due to FIND_IN_SET usage.

Solution

1. Step 1: Understand FIND_IN_SET function

   FIND_IN_SET('gps', features) checks if 'gps' is one of the comma-separated values in the SET column.

2. Step 2: Interpret the query result

   The query returns rows where 'gps' is included in the features set, not just exact matches.

3. Final Answer:

   Returns rows where 'gps' is one of the features. -> Option A

4. Quick Check:

   FIND_IN_SET finds value inside SET [OK]

Hint: FIND_IN_SET checks if value is in SET list [OK]

Common Mistakes:

• Thinking FIND_IN_SET matches exact whole column
• Assuming syntax error with FIND_IN_SET
• Confusing SET with ENUM behavior

4. Consider this table creation:

CREATE TABLE users (status ENUM('active', 'inactive', 'pending'));

Which of the following INSERT statements will cause an error?

medium

A. INSERT INTO users (status) VALUES (NULL);

B. INSERT INTO users (status) VALUES ('active');

C. INSERT INTO users (status) VALUES ('pending');

D. INSERT INTO users (status) VALUES ('blocked');

Solution

1. Step 1: Check allowed ENUM values

   The ENUM allows only 'active', 'inactive', or 'pending'. 'blocked' is not allowed.

2. Step 2: Analyze each INSERT

   Inserting 'blocked' will cause an error because it is not in the ENUM list. NULL is allowed if column permits it.

3. Final Answer:

   INSERT with 'blocked' causes error -> Option D

4. Quick Check:

   Only ENUM values allowed, others error [OK]

Hint: Only ENUM list values allowed, others cause error [OK]

Common Mistakes:

• Assuming NULL always causes error
• Thinking any string is allowed
• Confusing ENUM with SET behavior

5. You want to store user preferences for notifications: email, sms, and push. Users can choose any combination. Which column type and definition is best to store this efficiently?

hard

A. Use ENUM('email', 'sms', 'push') to store one preference.

B. Use SET('email', 'sms', 'push') to store multiple preferences.

C. Use VARCHAR(255) and store comma-separated values.

D. Use three separate BOOLEAN columns for each preference.

Solution

1. Step 1: Identify requirement for multiple choices

   Users can select any combination of preferences, so multiple values per row are needed.

2. Step 2: Choose appropriate data type

   SET type allows storing multiple predefined values efficiently in one column.

3. Step 3: Compare other options

   ENUM allows only one value, VARCHAR wastes space and needs parsing, separate BOOLEANs increase columns.

4. Final Answer:

   Use SET('email', 'sms', 'push') for multiple preferences -> Option B

5. Quick Check:

   Multiple choices = SET type [OK]

Hint: Multiple options? Use SET, not ENUM [OK]

Common Mistakes:

• Using ENUM for multiple selections
• Storing comma-separated strings manually
• Adding many boolean columns unnecessarily