Find Products Below a Certain Price
A small online store wants to list its cheapest products. They have a table of products.
products(product_idinteger,product_namevarchar(50),pricenumeric(10,2)
Return the product_name and price for all products whose price is strictly less than 50.00. The results should be ordered by price in ascending order. Ties in price should be broken by product_name in ascending order. NULL prices are not expected but should be ignored if present. An empty result set is possible if no products meet the criteria.
Tables
CREATE TABLE products (
product_id integer PRIMARY KEY,
product_name varchar(50) NOT NULL,
price numeric(10,2)
);Example data
INSERT INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 1200.00), (2, 'Mouse', 25.50), (3, 'Keyboard', 75.00), (4, 'Monitor', 300.00), (5, 'Webcam', 45.75), (6, 'Desk Lamp', 50.00), (7, 'Notebook', 5.20), (8, 'Pen', 2.00);
Your query is also checked on hidden datasets with different data.
Hints
-- Select product names and prices
SELECT
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.