ENUM and SET types in MySQL - Mini Project: Build & Apply

Using ENUM and SET Types in MySQL

📖 Scenario: You are creating a simple database for a small online store. You want to store product information including the product category and available colors. The category should be one of a few fixed options, and the colors can be multiple selections from a predefined list.

🎯 Goal: Build a MySQL table called products that uses ENUM for the product category and SET for the available colors.

📋 What You'll Learn

Create a table named products with columns id, name, category, and colors.

Use ENUM type for the category column with values: 'Clothing', 'Electronics', 'Books'.

Use SET type for the colors column with values: 'Red', 'Green', 'Blue', 'Black', 'White'.

Insert one product with category 'Electronics' and colors 'Red' and 'Black'.

💡 Why This Matters

🌍 Real World

ENUM and SET types help enforce data consistency for fields with limited options, like product categories and available colors in e-commerce databases.

💼 Career

Understanding ENUM and SET types is useful for database design roles, backend development, and data integrity management in real-world applications.

1

Create the products table with ENUM and SET columns

Write a CREATE TABLE statement to create a table called products with these columns: id as an integer primary key, name as a variable character string of length 50, category as an ENUM with values 'Clothing', 'Electronics', 'Books', and colors as a SET with values 'Red', 'Green', 'Blue', 'Black', 'White'.

MySQL

-- Write the CREATE TABLE statement for products with ENUM and SET columns
-- Your code here

Hint

Use ENUM to limit category to specific values and SET to allow multiple colors.

2

Insert a product with category and colors

Write an INSERT INTO statement to add a product with id 1, name 'Smartphone', category 'Electronics', and colors 'Red' and 'Black'.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  category ENUM('Clothing', 'Electronics', 'Books'),
  colors SET('Red', 'Green', 'Blue', 'Black', 'White')
);

-- Insert a product with id 1, name 'Smartphone', category 'Electronics', colors 'Red' and 'Black'
-- Your code here

Hint

Remember to separate multiple SET values with commas inside the string.

3

Query products by category

Write a SELECT statement to get all columns from products where the category is 'Electronics'. Use WHERE category = 'Electronics'.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  category ENUM('Clothing', 'Electronics', 'Books'),
  colors SET('Red', 'Green', 'Blue', 'Black', 'White')
);

INSERT INTO products (id, name, category, colors) VALUES (1, 'Smartphone', 'Electronics', 'Red,Black');

-- Select all columns from products where category is 'Electronics'
-- Your code here

Hint

Use WHERE to filter rows by the category column.

4

Query products by color using FIND_IN_SET

Write a SELECT statement to get all columns from products where the colors column includes 'Red'. Use FIND_IN_SET('Red', colors) > 0 in the WHERE clause.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  category ENUM('Clothing', 'Electronics', 'Books'),
  colors SET('Red', 'Green', 'Blue', 'Black', 'White')
);

INSERT INTO products (id, name, category, colors) VALUES (1, 'Smartphone', 'Electronics', 'Red,Black');

SELECT * FROM products WHERE category = 'Electronics';

-- Select all columns from products where colors include 'Red'
-- Your code here

Hint

Use FIND_IN_SET to check if a SET column contains a specific value.

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