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'.