iPhone Product Catalog
Which iPhone models are currently for sale, and what are their storage, colour, and price options?
- Filtering
- Sorting
Challenge brief
Understand the request
Product Marketing is updating the website product listing page and needs the current active iPhone lineup with full configuration details.
List all active (non-discontinued) iPhone products with model, storage, colour, and price.
Return
- product_name
- model
- storage_capacity
- color
- price
Constraints
- Return active iPhone products only
- Show the lowest prices first and resolve price ties by product ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
products
product_nameVARCHAR(100)modelVARCHAR(50)categoryVARCHAR(50)storage_capacityVARCHAR(20)colorVARCHAR(30)priceREALdiscontinuedINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
All product data is in the products table. The category column identifies the product line. The discontinued column is an integer flag: 0 = active, 1 = discontinued. No JOIN required.
Hint 2
WHERE category = 'iPhone' AND discontinued = 0. ORDER BY price ASC.
Hint 3
Build question 1 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT product_name, model, storage_capacity, color, price FROM products WHERE category = 'iPhone' AND discontinued = 0 ORDER BY price ASC, product_id ASC;Why this works
Two WHERE conditions work together: category = 'iPhone' narrows to iPhones and discontinued = 0 excludes the iPhone 12 (which is discontinued = 1). iPhone 12 is the only discontinued product in the dataset.
Success check
4 iPhone configurations — iPhone 15 ($799, $899) and iPhone 15 Pro ($999, $1099)
Expected result
Use this output to verify values, aliases, ordering, and row count.
| product_name | model | storage_capacity | color | price |
|---|---|---|---|---|
| iPhone 15 | 15 | 128GB | Pink | 799 |
| iPhone 15 | 15 | 256GB | Blue | 899 |
| iPhone 15 Pro | 15 Pro | 128GB | Natural Titanium | 999 |
| iPhone 15 Pro | 15 Pro | 256GB | Blue Titanium | 1099 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Amazon
Independent Amazon-style e-commerce, warehouse, inventory, and customer analytics SQL practice.
Return to the complete interview preparation experience.