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 MS SQL Server

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 will require that you combine your knowledge from different parts of the course to solve it. Let's take a look at a database of a garden shop:

Customer (Id, Name, Country)

Purchase (Id, CustomerId, Year)

PurchaseItem (Id, PurchaseId, Name, Quantity)

Here, customers purchase flowers. Each purchase consists of one or more PurchaseItems. Each PurchaseItem belongs to one purchase (column PurchaseId).

For example, if John Smith places an order for two roses and one lily, there will be two PurchaseItems in this specific purchase: one describing the roses (Quantity = 2) and one describing the lily (Quantity = 1).

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 CusId).
  • Name (name the column CusName).
  • Latest purchase ID (name the column LatestPurchaseId)
  • Total number of flowers purchased (name the column AllItemsPurchased).

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 the ID of the latest purchase for each customer.
  3. Third, select the total number of all flowers purchased by each customer.
  4. Finally, combine the three parts for get the final query.