Sorting with ORDER BY - Assignments

1. Default sorting by first names of actors

Complete the following query to obtain all data from the actors table. Sort by default (ascending) by the first name of actors (column first_name).

• Use SELECT and FROM to make the selection.
• Use ORDER BY to sort by column first_name.

SELECT
  *
FROM
  actors
ORDER BY
  first_name;

id	first_name	last_name	gender	film_count
390972	A. Ray	Ratliff	M	1
447419	Aaron	Sorkin	M	1
76443	Aaron James	Cash	M	1
...	...	...	...	...

Columns: 5
Rows: 1,907

---

2. Default sorting by the number of films of actors

Complete the following query to obtain all data from the actors table. Sort by default (ascending) by the number of films of actors (column film_count).

• Use ORDER BY to sort by column film_count.

SELECT
  *
FROM
  actors
ORDER BY
  film_count;

id	first_name	last_name	gender	film_count
841405	Hyowon K.	Yoo	F	1
2547	Andrew	Adamson	M	1
2700	William	Addy	M	1
...	...	...	...	...

Columns: 5
Rows: 1,907

---

3. Descending sorting by the number of films of actors

Complete the following query to obtain all data from the actors table. Sort descending by the number of films of actors (column film_count).

• Use ORDER BY and keyword DESC to sort descending by column film_count.

SELECT
  *
FROM
  actors
ORDER BY
  film_count DESC;

id	first_name	last_name	gender	film_count
22591	Kevin	Bacon	M	9
292028	Michael (I)	Madsen	M	3
812916	Uma	Thurman	F	3
...	...	...	...	...

Columns: 5
Rows: 1,907

---

4. Descending and ascending sorting by the number of films and first names of actors

Complete the following query to obtain all data from the actors table.

• Sort descending by the number of films of actors (column film_count).
• And ascending by the first name of actors (column first_name).
• Use ORDER BY and keywords ASC and DESC.

SELECT
  *
FROM
  actors
ORDER BY
  film_count DESC,
  first_name ASC;

id	first_name	last_name	gender	film_count
22591	Kevin	Bacon	M	9
366173	Bill	Paxton	M	3
376249	Brad	Pitt	M	3
...	...	...	...	...

Columns: 5
Rows: 1,907

---

5. (Extra) Sorting by the number of characters in the last name

Complete the following query to obtain all data from the actors table.

• Sort ascending by the number of characters in the last name of actors.
• Use the function LENGTH().

SELECT
  *
FROM
  actors
ORDER BY
  LENGTH(last_name) ASC;

id	first_name	last_name	gender	film_count
518886	Malcolm	X	M	1
216347	Xiaohui	Hu	M	1
99325	Sidney	Cox	M	1
...	...	...	...	...

Columns: 5
Rows: 1,907

---

6. (Extra) Sorting by the number of characters in the last name

Complete the following query to obtain a selection of data from the actors table.

• Select (filter) where the second letter in the first name is equal to o, regardless of whether it is lowercase or uppercase.
• Also select where the number of films is greater than 1.
• Sort descending the first name of actors.

SELECT
  *
FROM
  actors
WHERE
  lower(first_name) LIKE '_o%'
  AND film_count > 1
ORDER BY
  first_name DESC;

id	first_name	last_name	gender	film_count
101879	Tom	Cruise	M	2
412573	Robert	Ruth	M	2
159346	Morgan (I)	Freeman	M	2
...	...	...	...	...

Columns: 5
Rows: 10