Quiz
8. Task 7 - Challenge

Instruction

Excellent! Looks like you've mastered all parts of the SQL Basics course. Congratulations!

Before you finish, we have one final task for you. It's a bit more complicated than the previous tasks and you should combine the knowledge from different parts of the course to solve it. Let's take a look into a database of a garden shop:

customer (id, name, country)

purchase (id, customer_id, year)

purchase_item (id, purchase_id, name, quantity)

Here, customers purchase flowers.

Each purchase consists of one or more purchase_items. Each purchase_item belongs to one purchase (column purchase_id).

For example, if John Smith places an order for 2 roses and 1 lily, there will be two purchase_items in this specific purchase: one describing the roses (the column quantity would contain the value 2 here) and one describing the lily (column quantity would contain the value 1 here).

All purchase ids are stored chronologically, i.e. the last id placed in the shop has the greatest id.

Exercise

The owner of the shop would like to see each customer's

  • id (name the column cus_id),
  • name (name the column cus_name)
  • id of their latest purchase (name the column latest_purchase_id)
  • the total quantity of all flowers purchased by the customer, in all purchases, not just the last purchase (name the column all_items_purchased).

Remember, you need not use all columns from all the tables here - choose them wisely.

 

Stuck? Here's a hint!

Split the query into parts.

  1. First, select the first two columns, id and name. This should be straightforward.
  2. Second, select id of the latest purchase for each customer.
  3. Third, select the total quantity of all flowers purchased by each customer.
  4. Finally, combine the three parts for get the final query.