Finding Bouquets Within a Price Range

The Complete Query

SELECT bouquets.name, bouquets.price
FROM bouquets
WHERE bouquets.price BETWEEN 30.45 AND 40.36
ORDER BY bouquets.name;

Understanding the Problem

We need to help a customer find bouquets within their budget, specifically between $30.45 and $40.36. This is one of the most common types of database queries in e-commerce - letting customers find products in their price range. Think of it like a customer telling a florist: "Show me all bouquets between thirty and forty dollars, and list them alphabetically."

Real World Analogy

This query is similar to many everyday situations:

Imagine you're house hunting and tell your realtor: "I want to see all houses between $300,000 and $350,000, listed alphabetically by street name." The realtor would:

  1. Look at their complete listing database (FROM)
  2. Filter for houses in your price range (WHERE)
  3. Sort the matches by street name (ORDER BY)
  4. Show you the house name and price (SELECT)

Breaking Down Each Part

1. The SELECT Clause

SELECT bouquets.name, bouquets.price

This determines what information we want to see about each matching bouquet. We're using the full table name (bouquets.name instead of just name) for clarity and to prevent any potential ambiguity if we later join with other tables. Notice that we're only selecting what the customer needs - the name and price. This follows the principle of returning only necessary data.

2. The FROM Clause

FROM bouquets

This tells the database which table to search in. In our case, it's simple - we only need the bouquets table. We don't need to join with the flowers or flower_varieties tables because we're only interested in bouquet-level information.

3. The WHERE Clause

WHERE bouquets.price BETWEEN 30.45 AND 40.36

This is where the filtering magic happens. The BETWEEN operator is inclusive, meaning it includes bouquets that cost exactly $30.45 or $40.36. We could have written this as:

WHERE bouquets.price >= 30.45 AND bouquets.price <= 40.36

But BETWEEN is more readable and concise. It's especially useful when working with:

4. The ORDER BY Clause

ORDER BY bouquets.name

This sorts our results alphabetically by bouquet name. Without this, the results might come back in any order (often by the internal database ID). Consistent ordering is important for:

How Data Flows Through The Query

1. Start with all bouquets:
   name           | price
   Get Well Soon  | 59.99
   Mother's Day   | 39.99

2. Apply price filter (BETWEEN 30.45 AND 40.36):
   name           | price
   Mother's Day   | 39.99

3. Sort by name (ORDER BY):
   name           | price
   Mother's Day   | 39.99

Testing the Query

The test specs show this query works with different price ranges. This is possible because the query is properly structured. When the test changes the price range to $50-$60, it finds:

   name           | price
   Get Well Soon  | 59.99

Alternative Approaches

While our solution uses BETWEEN, here are other valid ways to write this query:

-- Using >= and <=
SELECT bouquets.name, bouquets.price
FROM bouquets
WHERE bouquets.price >= 30.45 
  AND bouquets.price <= 40.36
ORDER BY bouquets.name;

-- Using decimal comparison
SELECT bouquets.name, bouquets.price
FROM bouquets
WHERE CAST(bouquets.price AS DECIMAL(10,2)) >= 30.45
  AND CAST(bouquets.price AS DECIMAL(10,2)) <= 40.36
ORDER BY bouquets.name;

Common Pitfalls

When working with price ranges, watch out for:

Performance Considerations

This query performs well because:

Decimal Precision in SQL

Notice that our prices are stored with two decimal places (39.99). This is important for:

Key Learning Points

This query demonstrates several fundamental SQL concepts: