Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics in PostgreSQL

Quiz

Instruction

Excellent! Looks like you've mastered all parts of the Basics of PostgreSQL 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 at a florist's 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 identifying a rose (the column quantity would contain the value 2 here) and one identifying a 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 highest 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 carefully.

Stuck? Here's a hint!

Split building the query into parts:

  1. Select the first two columns, ID and name. This should be straightforward.
  2. In a subquery, select the ID of the latest purchase for each customer.
  3. In a subquery, select the total quantity of all flowers purchased by each customer.
  4. Combine the three parts to get the final query.