Schema and sample data
| product_id | name | category | price |
|---|---|---|---|
| 1 | USB Cable | Accessories | 12.99 |
| 2 | HDMI Adapter | Accessories | 20.00 |
| 3 | Wireless Mouse | Peripherals | 24.50 |
| 4 | Laptop Stand | Accessories | 45.00 |
| 5 | Mechanical Keyboard | Peripherals | 89.00 |
| 6 | Webcam | Peripherals | 100.00 |
| 7 | Monitor | Displays | 199.99 |
Show setup SQL (copy to run)
CREATE TABLE Products (
product_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
category VARCHAR(30),
price DECIMAL(10,2) NOT NULL
);
INSERT INTO Products (product_id, name, category, price) VALUES
(1, 'USB Cable', 'Accessories', 12.99),
(2, 'HDMI Adapter', 'Accessories', 20.00),
(3, 'Wireless Mouse', 'Peripherals', 24.50),
(4, 'Laptop Stand', 'Accessories', 45.00),
(5, 'Mechanical Keyboard', 'Peripherals', 89.00),
(6, 'Webcam', 'Peripherals', 100.00),
(7, 'Monitor', 'Displays', 199.99);From the Products table, return the name and price of every product whose price is between 20 and 100 inclusive. Use the BETWEEN operator and sort the result by price from lowest to highest.
Expected result
| name | price |
|---|---|
| HDMI Adapter | 20.00 |
| Wireless Mouse | 24.50 |
| Laptop Stand | 45.00 |
| Mechanical Keyboard | 89.00 |
| Webcam | 100.00 |
Show hint
Use WHERE price BETWEEN 20 AND 100 to keep only the rows in range, then ORDER BY price. Remember that BETWEEN is inclusive on both ends, so a product priced at exactly 20 or exactly 100 should still appear.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT name, price
FROM Products
WHERE price BETWEEN 20 AND 100
ORDER BY price;WHERE price BETWEEN 20 AND 100 is shorthand for price >= 20 AND price <= 100. Both endpoints are included, so the HDMI Adapter at exactly 20.00 and the Webcam at exactly 100.00 both stay in the result. The USB Cable at 12.99 falls below the lower bound and the Monitor at 199.99 rises above the upper bound, so both are dropped.
The order of the two values matters: BETWEEN expects the smaller value first. Writing BETWEEN 100 AND 20 would match nothing, because no price can be both at least 100 and at most 20. If you ever need to exclude the endpoints, switch to explicit strict comparisons such as price > 20 AND price < 100 instead.
ORDER BY price sorts the surviving rows from lowest to highest; ascending is the default, so no keyword is needed. The filter runs before the sort, which is why only the five in range rows are ordered. A common mistake is to assume BETWEEN is exclusive and then wonder why the boundary products appear - read the wording of each problem carefully.