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

Need a 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

Need a 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

Need a 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

Need a hint?

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