Applying Logic with CASE - Assignments

1. Categorizing actors based on gender

Complete the following query to add a column using CASE.

In this column, the full name of a gender should be displayed.

So for example, female in case of gender F.

• Use CASE, WHEN, THEN, ELSE, and END.
• Scenarios:
  • If gender is male ('M'): 'male'.
  • If gender is female ('F'): 'female'.

SELECT
  *,
  CASE
    WHEN gender = 'M' THEN 'male'
    WHEN gender = 'F' THEN 'female'
    ELSE 'Unknown'
  END
FROM
  actors;

id	first_name	last_name	gender	film_count	case
933	Lewis	Abernathy	M	1	male
2547	Andrew	Adamson	M	1	male
2700	William	Addy	M	1	male
...	...	...	...	...	...

Columns: 6
Rows: 1,907

---

2. Name the new column

In the code from the previous question, you didn't give the new column a specific name.

As a result, the column is named case by default.

This is not very clear.

Reuse your code from the previous question and name the new column gender_category.

• Use AS <column_name>.

SELECT
  *,
  CASE
    WHEN gender = 'M' THEN 'male'
    WHEN gender = 'F' THEN 'female'
    ELSE 'Unknown'
  END AS gender_category
FROM
  actors;

id	first_name	last_name	gender	film_count	gender_category
933	Lewis	Abernathy	M	1	male
2547	Andrew	Adamson	M	1	male
2700	William	Addy	M	1	male
...	...	...	...	...	...

Columns: 6
Rows: 1,907

---

3. Categorizing based on the number of films

Actors have appeared in different numbers of films.

We will use a CASE comparison to make a distinction here.

We will add a column that, depending on the number of films (column film_count) an actor has appeared in, receives a value.

• Use CASE, WHEN, THEN, ELSE, and END.
• Scenarios:
  • If the number is equal to 1: 'one movie'.
  • Otherwise: 'multiple movies'.
• Use AS to give the new column a specific name: film_count_category.

SELECT
  *,
  CASE
    WHEN film_count = 1 THEN 'one movie'
    ELSE 'multiple movies'
  END AS film_count_category
FROM
  actors;

id	first_name	last_name	gender	film_count	film_count_category
933	Lewis	Abernathy	M	1	one movie
2547	Andrew	Adamson	M	1	one movie
2700	William	Addy	M	1	one movie
...	...	...	...	...	...

Columns: 6
Rows: 1,907

---

4. Categorizing based on the length of the last name

Actors have different last names of varying lengths.

We will use a CASE comparison to make a distinction here.

We will add a column that, depending on the length of the last name (column last_name), receives a value.

• Use CASE, WHEN, THEN, ELSE, and END.
• Scenarios:
  • If a last name consists of more than 5 characters: 'long last name'.
  • Otherwise: 'short last name'.
• Use the LENGTH() function.
• Use AS to give the new column a specific name: last_name_length_category.

SELECT
  *,
  CASE
    WHEN LENGTH(last_name) > 5 THEN 'long last name'
    ELSE 'short last name'
  END AS last_name_length_category
FROM
  actors;

id	first_name	last_name	gender	film_count	last_name_length_category
933	Lewis	Abernathy	M	1	long last name
2547	Andrew	Adamson	M	1	long last name
2700	William	Addy	M	1	short last name
...	...	...	...	...	...

Columns: 6
Rows: 1,907

---

5. (Extra) Retrieving data from a column

Write a query to add a column that categorizes actors based on gender and the number of films.

• Name the new column actor_category.
• Scenarios:
  • If male and appeared in 1 film: 'male, one movie'.
  • If male and appeared in multiple films: 'male, multiple movies'.
  • If female and appeared in 1 film: 'female, one movie'.
  • If female and appeared in multiple films: 'female, multiple movies'.
  • In other cases: other
• Select only the columns id, first_name, and last_name in addition to the new column.

SELECT
  id,
  first_name,
  last_name,
  CASE
    WHEN (
      gender = 'M'
      AND film_count = 1
    ) THEN 'male, one movie'
    WHEN (
      gender = 'M'
      AND film_count > 1
    ) THEN 'male, multiple movies'
    WHEN (
      gender = 'F'
      AND film_count = 1
    ) THEN 'female, one movie'
    WHEN (
      gender = 'F'
      AND film_count > 1
    ) THEN 'female, multiple movies'
    ELSE 'other'
  END AS actor_category
FROM
  actors;

id	first_name	last_name	actor_category
933	Lewis	Abernathy	male, one movie
2547	Andrew	Adamson	male, one movie
2700	William	Addy	male, one movie
...	...	...	...

Columns: 4
Rows: 1,907