Rounding Financial Data in SQL

Financial applications often require displaying monetary values with a specific number of decimal places for clarity and consistency. This challenge focuses on using the ROUND function in SQL to format numerical data, specifically prices, to two decimal places, representing standard currency formatting.

Problem Description

Your task is to write a SQL query that selects product information and formats the price column to always display two decimal places. This is crucial for presenting financial data accurately and professionally, ensuring that values like 10 are displayed as 10.00 and 12.7 as 12.70.

• What needs to be achieved: Select all columns from a Products table and format the price column to two decimal places.
• Key requirements: Utilize the ROUND function in your SQL query. The output for the price column must always have exactly two digits after the decimal point.
• Expected behavior: Numbers with more than two decimal places should be rounded to the nearest hundredth. Numbers with fewer than two decimal places should be padded with zeros to reach two decimal places.
• Edge cases to consider:
  • Prices that are whole numbers (e.g., 5).
  • Prices with only one decimal place (e.g., 7.3).
  • Prices with more than two decimal places (e.g., 9.995).

Examples

Example 1:

Input Table: Products
| product_id | name       | price  |
|------------|------------|--------|
| 1          | Widget A   | 19.99  |
| 2          | Gadget B   | 25     |
| 3          | Gizmo C    | 100.50 |

Output Table:
| product_id | name       | formatted_price |
|------------|------------|-----------------|
| 1          | Widget A   | 19.99           |
| 2          | Gadget B   | 25.00           |
| 3          | Gizmo C    | 100.50          |

Explanation: The `price` for Widget A is already two decimal places. Gadget B's price of 25 is rounded to 25.00. Gizmo C's price of 100.50 remains 100.50.

Example 2:

Input Table: Products
| product_id | name       | price  |
|------------|------------|--------|
| 4          | Thingamajig| 7.3    |
| 5          | Doohickey  | 12.789 |
| 6          | Contraption| 0.5    |

Output Table:
| product_id | name       | formatted_price |
|------------|------------|-----------------|
| 4          | Thingamajig| 7.30            |
| 5          | Doohickey  | 12.79           |
| 6          | Contraption| 0.50            |

Explanation: Thingamajig's price of 7.3 is rounded to 7.30. Doohickey's price of 12.789 is rounded to 12.79. Contraption's price of 0.5 is rounded to 0.50.

Constraints

• The Products table will contain at least one row.
• The price column will contain numerical data types (e.g., DECIMAL, FLOAT, NUMERIC).
• The price column will not contain NULL values for the purpose of this challenge.
• Your query should be efficient and run within reasonable time limits for a table of up to 1,000,000 rows.

Notes

• The exact syntax for ROUND might vary slightly between SQL dialects, but the core concept of rounding to a specified number of decimal places remains consistent.
• Consider the behavior of ROUND with negative numbers if your data were to include them (though not explicitly tested in these examples).
• The goal is to achieve the formatting of two decimal places, which ROUND(number, 2) is designed to do.