When selecting a numeric data type in SQL, you need to consider several important factors that directly affect how your data is stored, processed, and retrieved. The first factor is range: different numeric types can store values within specific minimum and maximum limits. For example, SMALLINT is suitable for small numbers, while BIGINT can handle very large integers. Next, precision refers to the exactness of the stored value, especially important for decimal numbers. Types like DECIMAL allow you to specify both the total number of digits and the number of digits after the decimal point, making them ideal for values where accuracy is critical. Storage is also a key consideration; each type uses a different amount of disk space, so choosing the right type can optimize your database's size and efficiency. Finally, performance may be affected by your choice: smaller types are generally faster to process, while larger or more precise types may require more resources. Balancing these factors ensures your database remains accurate, efficient, and suitable for your application's needs.

CREATE TABLE city_statistics (
    id SERIAL PRIMARY KEY,
    population BIGINT,
    avg_temperature FLOAT,
    average_price DECIMAL(10,2)
);

In the example above, each column uses a different numeric type tailored to its data requirements. The population column uses BIGINT because city populations can reach into the billions, and only whole numbers make sense for counting people. The avg_temperature column is defined as FLOAT, which is suitable for storing approximate values with fractional parts, such as average temperatures that may vary widely and do not require exact precision. For average_price, the DECIMAL(10,2) type is chosen to represent financial values accurately, allowing up to ten digits in total, with two digits after the decimal point. This precision is essential for prices, where rounding errors are unacceptable. By matching the data type to the real-world characteristics and requirements of each column, you ensure data integrity and optimal storage.

INSERT INTO city_statistics (population, avg_temperature, average_price) VALUES
    (15000000, 21.5, 12345.67),
    (3500000, -5.3, 987.50),
    (1000000000, 27.8, 1999999.99);