Precise Rounding: Formatting Numerical Data in SQL

Many applications require presenting numerical data in a user-friendly format, often involving rounding to a specific number of decimal places. This challenge focuses on utilizing the ROUND function in SQL to achieve precise numerical formatting, ensuring accurate and aesthetically pleasing data display. You'll be tasked with writing SQL queries that round numerical values to specified precision.

Problem Description

The goal is to write SQL queries that round numerical values within a table to a specified number of decimal places. You will be given a table with numerical columns and a target precision (number of decimal places). Your query should return a result set with the original columns, but with the specified numerical columns rounded to the target precision.

Key Requirements:

• Rounding: Use the ROUND() function to round numerical values.
• Precision: Round to the exact number of decimal places specified.
• Column Selection: The query should select all columns from the original table, with only the specified numerical columns being rounded.
• Data Types: The query should handle various numerical data types (e.g., INT, FLOAT, DECIMAL).
• No Data Modification: The query should not modify the underlying table data; it should only return a formatted result set.

Expected Behavior:

The query should return a result set identical in structure to the original table, but with the specified numerical columns rounded to the target precision. If a column is not numerical, it should be returned unchanged.

Edge Cases to Consider:

• Null Values: Handle NULL values gracefully. ROUND(NULL, precision) should return NULL.
• Negative Numbers: Ensure correct rounding for negative numbers.
• Zero Precision: Rounding to zero decimal places should truncate the number (e.g., ROUND(3.14159, 0) should return 3).
• Large/Small Numbers: The ROUND function should handle large and small numbers correctly without overflow or underflow issues (within the database's limits).
• Non-Numerical Columns: The query should not attempt to round non-numerical columns.

Examples

Example 1:

Input:
Table: `products`
Columns: `product_id` (INT), `product_name` (VARCHAR), `price` (DECIMAL(10, 2)), `discount` (FLOAT)

Target Precision: 2

SQL Query:
SELECT product_id, product_name, ROUND(price, 2) AS rounded_price, ROUND(discount, 2) AS rounded_discount FROM products;

Output:
product_id | product_name | rounded_price | rounded_discount
------------|--------------|---------------|-----------------
1          | Widget A     | 19.99         | 0.15
2          | Gadget B     | 49.50         | 0.25
3          | Thing C      | 99.00         | 0.05

Explanation: The price and discount columns are rounded to 2 decimal places and aliased as rounded_price and rounded_discount respectively. The product_id and product_name columns remain unchanged.

Example 2:

Input:
Table: `orders`
Columns: `order_id` (INT), `total_amount` (DECIMAL(12, 4)), `tax_rate` (FLOAT)

Target Precision: 0

SQL Query:
SELECT order_id, ROUND(total_amount, 0) AS rounded_total, ROUND(tax_rate, 0) AS rounded_tax FROM orders;

Output:
order_id | rounded_total | rounded_tax
-----------|---------------|-------------
101       | 125.00        | 0.0
102       | 78.55         | 0.0
103       | 210.99        | 0.0

Explanation: The total_amount and tax_rate columns are rounded to 0 decimal places, effectively truncating them.

Example 3:

Input:
Table: `employees`
Columns: `employee_id` (INT), `salary` (DECIMAL(15, 2)), `bonus` (FLOAT), `name` (VARCHAR)

Target Precision: 1

SQL Query:
SELECT employee_id, name, ROUND(salary, 1) AS rounded_salary, ROUND(bonus, 1) AS rounded_bonus FROM employees;

Explanation: This demonstrates rounding both salary and bonus to one decimal place.

Constraints

• The target precision will be an integer between 0 and 10 (inclusive).
• The input table will always exist and contain at least one numerical column.
• The SQL dialect is assumed to be standard SQL (compatible with most major database systems like MySQL, PostgreSQL, SQL Server, etc.).
• The query should execute within a reasonable time (e.g., less than 1 second) for tables with up to 10,000 rows.

Notes

• Consider using aliases to rename the rounded columns in the result set. This improves readability.
• The ROUND() function's behavior with respect to rounding ties (e.g., 2.5) can vary slightly between database systems. The challenge assumes standard rounding behavior (round half up).
• Focus on writing a clear and concise query that accurately rounds the specified numerical columns. Error handling for invalid input (e.g., non-integer precision) is not required for this challenge.
• Think about how to identify the numerical columns dynamically (although this is not strictly required for the solution). A hardcoded solution is acceptable.