Aggregating - Assignments

1. Number of actors by gender

Complete the following query to obtain the number of actors by gender.

• Retrieve data from the actors table.
• Group by the gender column using GROUP BY.
• Select the gender column, and count the number of rows using the COUNT() function.

...
  ...,
  ...(*)
FROM
  ...
...
  ...;

---

2. Average number of films

Complete the following query to also calculate the average number of films for actors of a certain gender.

• Reuse your code from the previous assignment.
• Retrieve data from the actors table.
• Group by the gender column using GROUP BY.
• Select/calculate columns:
  • Gender: gender
  • Count the number of rows using the COUNT() function.
  • Calculate the average number of films using AVG().
    • Name this column film_count_avg using AS.

...
  ...,
  ...(*),
  ...(...) AS ...
FROM
  ...
...
  ...;

---

3. Largest number of films

Complete the following query to also calculate the largest number of films for an actor of a certain gender.

• Reuse your code from the previous assignment.
• Retrieve data from the actors table.
• Group by the gender column using GROUP BY.
• Select/calculate columns:
  • Gender: gender
  • Count the number of rows using the COUNT() function.
  • Calculate the average number of films using AVG().
    • Name this column film_count_avg using AS.
  • Find the largest number of films using MAX().
    • Name this column film_count_max using AS.

...
  ...,
  ...(*),
  ... AS ...,
  ...
FROM
  ...
...
  ...;

---

4. (Extra) Length of names

Complete the following query to also calculate the average length (number of characters) of first names of actors by gender.

• Reuse your code from the previous assignment.
• Calculate the average length of first names, column first_name.
  • Use the AVG() and LENGTH() functions.
  • Name the new column first_name_avg.
• Which gender has the longest first name on average?

...
  ...,
  ...(*),
  ... AS ...,
  ... AS ...,
  ... AS ...
FROM
  ...
...
  ...;

---

5. (Extra) Number of actors with names of a certain length

Complete the following query to:

• Group by the length of first names of actors.
• Select/calculate as columns:
  • Length (number of characters) of first names of actors.
  • The number of rows (number of actors with a first name of a certain length).
• Sort by the length (number of characters) of first names of actors.

...