What would you use ENUM for in SQL?

I am trying to complete an assignment for my CIS class and I am required to include a data type called ENUM. I know that this stands for enumerated lists, but I am not sure when would be an appropriate time to use ENUM. A couple of the examples given in the book I am using were like a list of the continents. Could I use this to describe a list of departments for a workplace?

ENUM datatype can be seen as a "human-readable" numeric value, commonly used for convenience and data storage efficiency.

Let's take your example with a table listing the countries and their respective continents.

Using VARCHAR

CREATE TABLE country_continent (
    country VARCHAR(100),
    continent VARCHAR(100)
);

INSERT INTO country_continent VALUES ('Kenya', 'Africa');
INSERT INTO country_continent VALUES ('New-York', 'America');
INSERT INTO country_continent VALUES ('Paris', 'Europe');

Using SMALLINT

For better storage efficiency you'd better use SMALLINT (2 bytes) instead of VARCHAR (generally 1 byte + the Length of the string) for the continent field:

CREATE TABLE continents (
    id SMALLINT,
    label VARCHAR(100)
);

INSERT INTO continents VALUES (1, 'Africa');
INSERT INTO continents VALUES (2, 'America');
INSERT INTO continents VALUES (3, 'Europe');

CREATE TABLE country_continent (
    country VARCHAR(100),
    continent_id SMALLINT
);

INSERT INTO country_continent VALUES ('Kenya', 1);
INSERT INTO country_continent VALUES ('New-York', 2);
INSERT INTO country_continent VALUES ('Paris', 3);

Using ENUM

This is where ENUM makes sense:

CREATE TABLE country_continent (
    country VARCHAR(100),
    continent ENUM('Africa', 'America', 'Antarctica', 'Asia', 'Europe', 'Oceania')
);

INSERT INTO country_continent VALUES ('Kenya', 'Africa');
INSERT INTO country_continent VALUES ('New-York', 'America');
INSERT INTO country_continent VALUES ('Paris', 'Europe');

With ENUM you get same storage efficiency (or even better) than SMALLINT, but the ease of use of a VARCHAR data type.

StackExchange Database Administrators Q#231795, answer score: 5