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 with GROUP BY.
• Select the gender column, and count the number of rows with the COUNT() function.

SELECT
  gender,
  COUNT(*)
FROM
  actors
GROUP BY
  gender;

gender	count
M	1464
F	443

Columns: 2
Rows: 2

---

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 with GROUP BY.
• Select/calculate columns:
  • Gender: gender
  • Count the number of rows with the COUNT() function.
  • Calculate the average number of films with AVG().
    • Name this column film_count_avg with AS.

SELECT
  gender,
  COUNT(*),
  AVG(film_count) AS film_count_avg
FROM
  actors
GROUP BY
  gender;

gender	count	film_count_avg
M	1464	1.0484972677595628
F	443	1.0248306997742664

Columns: 3
Rows: 2

---

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 with GROUP BY.
• Select/calculate columns:
  • Gender: gender
  • Count the number of rows with the COUNT() function.
  • Calculate the average number of films with AVG().
    • Name this column film_count_avg with AS.
  • Find the largest number of films with MAX().
    • Name this column film_count_max with AS.

SELECT
  gender,
  COUNT(*),
  AVG(film_count) AS film_count_avg,
  MAX(film_count) AS film_count_max
FROM
  actors
GROUP BY
  gender;

gender	count	film_count_avg	film_count_max
M	1464	1.0484972677595628	9
F	443	1.0248306997742664	3

Columns: 4
Rows: 2

---

4. (Extra) Length of names

Complete the following query to also calculate the average length (number of characters) of first names of actors per 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?

SELECT
  gender,
  COUNT(*),
  AVG(film_count) AS film_count_avg,
  MAX(film_count) AS film_count_max,
  AVG(LENGTH(first_name)) AS first_name_avg
FROM
  actors
GROUP BY
  gender;

gender	count	film_count_avg	film_count_max	first_name_avg
M	1464	1.0484972677595628	9	6.4699453551912568
F	443	1.0248306997742664	3	6.8442437923250564

Columns: 5
Rows: 2

---

5. (Extra) Number of actors with names of 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.

SELECT
  LENGTH(first_name) as first_name_length,
  COUNT(*)
FROM
  actors
GROUP BY
  LENGTH(first_name)
ORDER BY
  first_name_length;

first_name_length	count
2	12
3	146
4	296
5	365
...	...

Columns: 2
Rows: 19