ENUM and SET types in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a column named 'status' with ENUM type having values 'active' and 'inactive'.

MySQL

CREATE TABLE users (id INT, status ENUM([1]));

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

A'open', 'closed'

B'yes', 'no'

C'active', 'inactive'

D'true', 'false'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to quote the ENUM values.

Using values not related to the column meaning.

The ENUM type requires listing the allowed string values inside parentheses and quotes, separated by commas. Here, 'active' and 'inactive' are the correct values for the 'status' column.

2fill in blank

medium

Complete the code to define a SET column named 'features' that can include 'wifi', 'parking', and 'pool'.

MySQL

CREATE TABLE hotels (id INT, features SET([1]));

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

A'wifi', 'parking', 'pool'

B'wifi', 'parking'

C'wifi', 'pool'

D'parking', 'pool'

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving out one or more allowed values.

Using ENUM instead of SET for multiple selections.

The SET type allows multiple values from the list. Here, the column 'features' can include 'wifi', 'parking', and 'pool'.

3fill in blank

hard

Fix the error in the ENUM definition for a column 'priority' with values 'low', 'medium', 'high'.

MySQL

CREATE TABLE tasks (id INT, priority ENUM([1]));

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

Alow, medium, high

B'low', 'medium', high

C'low', medium, 'high'

D'low', 'medium', 'high'

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting all ENUM values.

Mixing quoted and unquoted values.

ENUM values must be quoted strings. Option D correctly quotes all values.

4fill in blank

hard

Fill both blanks to create a table with a SET column 'options' allowing 'red', 'green', and 'blue', and a default value of 'red'.

MySQL

CREATE TABLE colors (id INT, options SET([1]) DEFAULT [2]);

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

A'red', 'green', 'blue'

B'red'

C'green'

D'blue'

Attempts:

3 left

💡 Hint

Common Mistakes

Setting default value not in the SET list.

Forgetting quotes around default value.

The SET column must list all allowed values, and the default must be one of those values quoted.

5fill in blank

hard

Fill all three blanks to define a table with an ENUM column 'level' with values 'beginner', 'intermediate', 'advanced', a SET column 'skills' with 'sql', 'python', 'java', and default 'beginner' for 'level'.

MySQL

CREATE TABLE learners (id INT, level ENUM([1]) DEFAULT [2], skills SET([3]));

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

A'beginner', 'intermediate', 'advanced'

B'beginner'

C'sql', 'python', 'java'

D'expert'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a default value not in ENUM list.

Mixing up ENUM and SET values.

The ENUM 'level' must list all levels, default to 'beginner', and the SET 'skills' must list all allowed skills.

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