• Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query • Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a numeric value • State the implications for business when applying TRUNC and ROUND to numeric values
• It is mainly used to round numbers to a specified number of decimal places, but it can also be used to round numbers to the left of the decimal point. • Syntax: ROUND(column|expression, decimal places)
• Note that if the number of decimal places is not specified or
is zero, the number will round to no decimal places. • ROUND(45.926) 46 • ROUND(45.926, 0) 46
• If the number of decimal places is a positive number, the
number is rounded to that number of decimal places to the right of the decimal point. • ROUND(45.926, 2) 45.93 • If the number of decimal places is a negative number, the number is rounded to that number of decimal places to the left of the decimal point. • ROUND(45.926, -1) 50
• The TRUNC function can be used with both numbers and
dates. It is mainly used to terminate the column, expression, or value to a specified number of decimal places. • When TRUNC is used, if the number of decimal places is not specified, then like ROUND, the specified number defaults to zero. • Syntax: TRUNC(column|expression, decimal places)
• As with ROUND, if the TRUNC expression does not specify the
number of decimal places or specifies a zero, the number is truncated to zero decimal places. • TRUNC (45.926, 0) 45 • TRUNC (45.926) 45 • Remember that TRUNC does not round the number. • It simply terminates the number at a given point.
• The MOD function finds the remainder after one value is
divided by another value. • For example, the MOD of 5 divided by 2 is 1. • MOD can be used to determine whether a value is odd or even. If you divide a value by 2 and there is no remainder, the number must be an even number. • For example, if the MOD of x divided by 2 is 0, then x must be an even number.
• The "Mod Demo" column will show if number of airports for
each country is an odd or even number. SELECT country_name, MOD(airports,2) COUNTRY_NAME Mod Demo AS "Mod Demo" FROM wf_countries; Canada 1 Republic of Costa Rica 0 • 1 means the number is odd, Republic of Cape Verde 1 and zero means that it is even. Greenland 0 Dominican Republic 0 State of Eritrea 1 …
• Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query • Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a numeric value • State the implications for business when applying TRUNC and ROUND to numeric values
