Jorge Castro DAPT NOV2021
The database is structured as follows:
-
(filter out the rows that have nulls or 0s in length column). In your output, only select the columns title, length, and the rank.
Answer:
SELECT
title,
`length`,
rank() over(
ORDER BY
`length` DESC
) AS ranking
FROM
film
WHERE
`length` != 0
OR NOT NULL;
-
(filter out the rows that have nulls or 0s in length column). In your output, only select the columns title, length, rating and the rank.
Answer:
SELECT
title,
`length`,
rating,
dense_rank() over(
ORDER BY
`length` DESC
) AS ranking
FROM
film
WHERE
`length` NOT IN (0, '', ' ')
ORDER BY
3;
-
Use appropriate join to write this query
Answer:
SELECT
a.`name` AS film_category,
count(b.film_id) AS number_of_films
FROM
category a
INNER JOIN film_category b ON a.category_id = b.category_id
GROUP BY
1
ORDER BY
2 DESC;
Answer:
SELECT
concat((a.first_name), ' ', (a.last_name)) actor_name,
count(b.film_id) AS actors_appearances
FROM
actor a
INNER JOIN film_actor b ON a.actor_id = b.actor_id
GROUP BY
1
ORDER BY
2 DESC
LIMIT
1;
-
(the customer that has rented the most number of films)
Answer:
SELECT
concat((a.first_name), ' ', (a.last_name)) customer_name,
count(b.rental_id) AS rented_films
FROM
customer a
INNER JOIN rental b ON a.customer_id = b.customer_id
GROUP BY
1
ORDER BY
2 DESC
LIMIT
1;
-
The answer is Bucket Brotherhood This query might require using more than one join statement. Give it a try. We will talk about queries with multiple join statements later in the lessons.
Answer:
SELECT
b.title,
count(c.rental_id) AS number_of_rents
FROM
inventory a
INNER JOIN film b ON a.film_id = b.film_id
INNER JOIN rental c ON a.inventory_id = c.inventory_id
GROUP BY
1
ORDER BY
2 DESC
LIMIT
1;
|
💡
|
To do any join, I find it helpful to think of it as a 3 step process:
Then build the SELECT statement: This is where we define the actual layout of the table, the order in which the columns are in the table from left to right and using the aggregations and functions if need be.
|
