Product Catalog Search Challenges

Welcome to your hands-on SQL adventure! You're now the database detective for TechMart's online store. Your mission: help customers find exactly what they're looking for by mastering the SELECT statement.

Your Database Setup

You have access to TechMart's product catalog database with the following tables:

Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock_quantity INT,
    brand VARCHAR(50),
    rating DECIMAL(2,1),
    release_date DATE
);

Sample Data Preview

Challenge One: Basic Product Discovery

Help a customer who wants to see all available smartphones.

Your Task: Write a SELECT query to show all products in the 'Smartphones' category.

SELECT * 
FROM products 
WHERE category = 'Smartphones';

Challenge 2: Budget Shopping

A student wants to find all products under $500.

Your Task: Display product name, price, and category for all items priced below $500.

SELECT name, price, category 
FROM products 
WHERE price < 500;

Challenge 3: Premium Products Hunt

Find all highly-rated premium products (``rating >= 4.7`` AND price > $1000).

Your Task: Show name, brand, price, and rating for these premium items.

SELECT name, brand, price, rating 
FROM products 
WHERE rating >= 4.7 AND price > 1000;

Challenge 4: Brand Loyalty Search

A customer loves Apple products. Show them all Apple items sorted by price (highest first).

Your Task: List all Apple products with their details, ordered by price descending.

SELECT * 
FROM products 
WHERE brand = 'Apple' 
ORDER BY price DESC;

Challenge 5: Stock Alert

The inventory manager needs to identify products running low on stock (less than 20 units).

Your Task: Find products with ``stock_quantity < 20``, showing name, stock_quantity, and category.

SELECT name, stock_quantity, category 
FROM products 
WHERE stock_quantity < 20;

Challenge 6: New Arrivals

Marketing wants to feature products released in 2024.

Your Task: Display all products released after December 31, 2023, sorted by release date.

SELECT * 
FROM products 
WHERE release_date > '2023-12-31' 
ORDER BY release_date;

Challenge 7: Multi-Category Search

A customer is interested in either Laptops OR Audio products.

Your Task: Show all products from these two categories.

-- Method 1: Using OR
SELECT * 
FROM products 
WHERE category = 'Laptops' OR category = 'Audio';

-- Method 2: Using IN
SELECT * 
FROM products 
WHERE category IN ('Laptops', 'Audio');

Challenge 8: Price Range Filter

Find all products priced between $200 and $1000 (inclusive).

Your Task: Display name, price, and brand for products in this price range.

-- Method 1: Using BETWEEN
SELECT name, price, brand 
FROM products 
WHERE price BETWEEN 200 AND 1000;

-- Method 2: Using comparison operators
SELECT name, price, brand 
FROM products 
WHERE price >= 200 AND price <= 1000;

Challenge 9: Top Rated Products

Management wants to see the top 3 highest-rated products.

Your Task: Show the 3 products with the highest ratings.

SELECT * 
FROM products 
ORDER BY rating DESC 
LIMIT 3;

Challenge 10: Smart Shopping Query

A tech-savvy customer wants:

• Products from either Apple or Samsung
• Priced under $1000
• With a rating of at least 4.7
• Sorted by price (lowest first)

Your Task: Write the complete query for this specific request.

SELECT * 
FROM products 
WHERE (brand = 'Apple' OR brand = 'Samsung') 
  AND price < 1000 
  AND rating >= 4.7 
ORDER BY price ASC;

Bonus Challenge: Pattern Matching

Find all products with "Pro" in their name.

Your Task: Use pattern matching to find these premium products.

SELECT * 
FROM products 
WHERE name LIKE '%Pro%';

Practice Makes Perfect!

Now try creating your own search scenarios:

1. Find all products from a specific brand with ``stock > 50``
2. List products released in the last 6 months
3. Show the cheapest product in each category
4. Find products with names starting with a specific letter

Remember: The SELECT statement is your Swiss Army knife for data retrieval. Master it, and you'll unlock the power of database querying!

Key Takeaways

✅ SELECT chooses which columns to display
✅ WHERE filters rows based on conditions
✅ ORDER BY sorts your results
✅ LIMIT restricts the number of rows returned
✅ Combine conditions with AND, OR, and NOT
✅ Use comparison operators: =, <, >, <=, >=, <>
✅ Pattern matching with LIKE and % wildcards

Keep practicing these queries - they're the foundation of all database interactions!