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
  *,
  ...
    ... .. = '...' THEN '...'
    WHEN ...
    ... ...
  END
FROM
  actors;

---

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>.

...

---

3. Categorizing based on the number of films

Actors have acted in different numbers of films.

We will make a distinction here using a CASE comparison.

We will add a column that, depending on the number of films (column film_count) an actor has acted 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
  *,
  ...
    ...
    ELSE ...
  ...
FROM
  actors;

---

4. Categorizing based on the length of the last name

Actors have different last names of varying lengths.

We will make a distinction here using a CASE comparison.

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 ...(last_name) ...
    ...
 ...
FROM
  actors;

---

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 acted in 1 film: 'male, one movie'.
  • If male and acted in multiple films: 'male, multiple movies'.
  • If female and acted in 1 film: 'female, one movie'.
  • If female and acted 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.

...