SQL Exercises: Find the name and price of the cheapest item(s)

Last update on February 10 2025 06:29:12 (UTC/GMT +8 hours)

From the following table, write a SQL query to find the cheapest item(s). Return pro_name and, pro_price.

Sample table: item_mast

 PRO_ID PRO_NAME                       PRO_PRICE    PRO_COM
------- ------------------------- -------------- ----------
    101 Mother Board                    3200.00         15
    102 Key Board                        450.00         16
    103 ZIP drive                        250.00         14
    104 Speaker                          550.00         16
    105 Monitor                         5000.00         11
    106 DVD drive                        900.00         12
    107 CD drive                         800.00         12
    108 Printer                         2600.00         13
    109 Refill cartridge                 350.00         13
    110 Mouse                            250.00         12

Sample Solution:

-- This query selects specific columns 'pro_name' and 'pro_price' from the 'item_mast' table.
SELECT pro_name, pro_price
-- Specifies the table from which to retrieve the data (in this case, 'item_mast').
FROM item_mast
-- Filters the rows to only include those where the 'pro_price' column is equal to the minimum 'pro_price'.
WHERE pro_price = (SELECT MIN(pro_price) FROM item_mast);

Output of the Query:

pro_name	pro_price
ZIP drive	250.00
Mouse	    250.00

Code Explanation:

The above query in SQL that selects the pro_name and pro_price columns from the table named 'item_mast' where the pro_price is equal to the minimum pro_price value in the table. As a result, the function returns the pro_name and the pro_price of the item in the table that has the lowest price in the table.

Practice Online

For more Practice: Solve these Related Problems:

• Write a SQL query to find the most expensive item(s) in the table and return the product name and price.
• Write a SQL query to identify the item(s) with the highest price and display their names and prices.
• Write a SQL query to retrieve the second cheapest item(s) from the table, including their names and prices.
• Write a SQL query to locate the top 3 cheapest items and return their names and prices.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Display the average price of the items for each company.
Next SQL Exercise: Find the last name of all employees, without duplicates.