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

Last update on December 20 2024 11:55:29 (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