Header

Grouping with `GROUP BY` - Assignments

1. Grouping by first name of actors

Complete the following query to group by the first name of actors (column first_name) from the actors table.

Count the number of rows.

This will show you how many actors have a certain first name.

Use the GROUP BY statement to group.
Use the COUNT() function to count the number of rows.

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

2. Renaming a column

In the previous assignment, we determined how often a certain first name appears in the actors table.

From GROUP BY and COUNT(), the new column is automatically named count.

We will change this by renaming the column.

Reuse your code from the previous assignment.
Rename the column count to first_name_count.
Use the AS statement.

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

3. Sorting after grouping

In the previous assignments, we determined how often a certain first name appears in the actors table, and we changed the column name.

You can't easily see which first name appears most often yet. This is because it hasn't been sorted yet.

We will improve this by sorting on the column first_name_count.

Reuse your code from the previous assignment.
Sort descending on the column first_name_count.
- Use the ORDER BY statement.

Which name appears most often?

SELECT
  ...,
  ...(*) ... ...
FROM
...
  ...
...
  COUNT(*) ...;

4. Filtering after grouping

So far, we have done: * Grouping on the column first_name. * Changing the column name count to first_name_count. * Sorting descending on the new column.

We will now apply a filter after sorting. So that only names that appear more than 10 times are shown.

Reuse your code from the previous assignment.
Filter on the new column.
- Use the HAVING statement.
  - Make sure to apply this in the correct place.

SELECT
  ...,
  ...(*) ... ...
FROM
  ...
...
  ...
...
  COUNT(*) > ...
...
  COUNT(*) ...;

5. (Extra) Grouping by first name and gender

So far, we have grouped by first name for both female and male actors. This without making a distinction.

Now write a query to group by the column first_name as well as the column gender.

Select existing columns first_name and gender.
Group by columns first_name and gender.
Count the number of rows with COUNT(*)
Sort:
- On the new column, descending.
- On gender, ascending.

...

6. (Extra) Most common female first names

So far, we have grouped by first name for both female and male actors.

Now write a query to obtain the top 10 most common female first names.

...

Grouping with GROUP BY - Assignments