Cars24 mid CASTWHEREORDER BY

Return car_model and the price as a number (aliased price) for every listing in cars24_listings priced above 500000. Use CAST to convert price_text to a numeric type before comparing — a plain string comparison on price_text gives the wrong answer. Sort by price descending.

Cars24 · Text-Stored Price Filtering — practice this real-world SQL scenario live in your browser.

📖 Story Cars24 · Text-Stored Price Filtering

Saturday morning at Cars24's Inventory Pricing desk. cars24_listings holds every used car currently listed, but price_text was imported straight from an old spreadsheet years ago and stored as TEXT — every value looks like a number ('450000', '1150000'), but SQLite treats the whole column as plain text, quotes and all. One listing, Toyota Innova Crysta 2019, has price_text = NULL because the seller and buyer are still negotiating and there's genuinely no final number to record yet — that's different from a price of zero. The pricing team wants every listing priced above 500000 so they can route it to the premium-inventory team, sorted from most to least expensive.

🎯 Your Mission

📋 Table Structure

🗂 cars24_listings

id INTEGER 1

car_model TEXT Maruti Swift VXI 2019

price_text TEXT 450000

mileage_kmpl REAL 22.5

⚡ Step-by-Step Walkthrough

Look at the raw listings, prices stored as TEXT

query.sql

SELECT *
FROM cars24_listings
ORDER BY id;

💡 Explanation

cars24_listings has 9 rows, and price_text is declared TEXT, not INTEGER or REAL — a legacy import kept every price as a plain digit string like '450000' instead of an actual number.
Two of the priciest cars, Hyundai Creta SX 2021 at '1150000' and Kia Seltos HTX 2021 at '1050000', are seven-digit strings, while a cheaper car like Maruti Swift VXI 2019 at '450000' is only six digits — that digit-count mismatch is exactly what's about to cause trouble.
Toyota Innova Crysta 2019 has price_text = NULL, not '0' or an empty string — the seller and buyer are still negotiating, and Cars24 genuinely doesn't have a final number to record yet.
Nothing is filtered or compared yet — this step exists purely to see the raw TEXT values as they actually are, string quotes and all.

See the trap: comparing price_text as a string picks the wrong cars

query.sql

SELECT car_model, price_text
FROM cars24_listings
WHERE price_text > '500000'
ORDER BY price_text DESC;

💡 Explanation

This returns only 4 cars — Mahindra XUV500 (980000), Honda City (900000), Hyundai i20 Sportz (650000), and Tata Nexon (620000) — and misses the two most expensive cars in the entire table.
Hyundai Creta SX at 1150000 and Kia Seltos at 1050000 both vanish, even though they're worth more than every car that did make the list — '1150000' > '500000' compares FALSE because SQLite compares TEXT character by character, and '1' comes before '5' as the very first character, no matter how many digits follow.
This is a genuine trap, not a rounding error: a seven-digit price beginning with '1' will always lose to the six-digit '500000' in a plain string comparison, no matter how large the actual number behind it is.
Toyota Innova Crysta's NULL price_text correctly stays out of this result too — NULL > '500000' evaluates to NULL, not TRUE, so no special-casing was needed for the one listing that has no price yet.

Fix it: CAST(price_text AS REAL) compares the actual numbers

query.sql

SELECT car_model, CAST(price_text AS REAL) AS price
FROM cars24_listings
WHERE CAST(price_text AS REAL) > 500000
ORDER BY price DESC;

💡 Explanation

CAST(price_text AS REAL) converts each digit string into an actual floating-point number before the comparison ever runs, so 1150000 and 1050000 compare correctly against 500000 as the numbers they always were.
Six cars come back now, correctly sorted by price descending from Hyundai Creta SX at 1150000 down to Tata Nexon at 620000 — the two cars the string comparison silently dropped are now right at the top of the list.
Maruti Swift (450000) and Maruti Alto (285000) are correctly excluded either way, since both really are below 500000 — the bug in step 2 only ever bit the high end of the range, which is exactly why it's easy to miss on a small dataset.
CAST(NULL AS REAL) is still just NULL, so Toyota Innova Crysta's negotiation-pending listing needed no extra handling here either — a genuine NULL, once again, turns out to be the one thing in this query that was never actually broken.

▶ Practice this live ← Browse all questions