numeric type sql code example
Example 1: sql numeric functions
ABS Returns the absolute value of the given number.
ACOS Returns the arc cosine of the given number.
ASIN Returns the arc sine of the given number.
ATAN Returns the arc tangent of one or 2 given numbers.
ATAN2 Return the arc tangent of 2 given numbers.
AVG Returns the average value of the given expression.
CEIL Returns the closest whole number (integer) upwards from a given
decimal point number.
CEILING Same as CEIL.
COS Returns the cosine of a given number.
COT Returns the cotangent of a given number.
COUNT Returns the amount of records that are returned by a SELECT query.
DEGREES Converts a radians value to degrees.
DIV Allows you to divide integers.
EXP Returns e to the power of the given number.
FLOOR Returns the closest whole number (integer) downwards from a given
decimal point number.
GREATEST Returns the highest value in a list of arguments.
LEAST Returns the smallest value in a list of arguments.
LN Returns the natural logarithm of the given number
LOG Returns the natural logarithm of the given number, or the logarithm of
the given number to the given base
LOG10 Does the same as LOG, but to base 10.
LOG2 Does the same as LOG, but to base 2.
MAX Returns the highest value from a set of values.
MIN Returns the lowest value from a set of values.
MOD Returns the remainder of the given number divided by the other given
number.
PI Returns PI.
POW Returns the value of the given number raised to the power of the other
given number.
POWER Same as POW.
RADIANS Converts a degrees value to radians.
RAND Returns a random number.
ROUND Round the given number to the given amount of decimal places.
SIGN Returns the sign of the given number.
SIN Returns the sine of the given number.
SQRT Returns the square root of the given number.
SUM Returns the value of the given set of values combined.
TAN Returns the tangent of the given number.
TRUNCATE Returns a number truncated to the given number of decimal places.
Example 2: sql data types
-- Numeric Data Types:
BIT(size) A bit-value type with a default of 1. The allowed number of bits in a
value is set via the size parameter, which can hold values from 1 to 64.
TINYINT(size)
A very small integer with a signed range of -128 to 127, and an
unsigned range of 0 to 255. Here, the size parameter specifies the
maximum allowed display width, which is 255.
BOOL Essentially a quick way of setting the column to TINYINT with a size of
1. 0 is considered false, whilst 1 is considered true.
BOOLEAN Same as BOOL.
SMALLINT(size)
A small integer with a signed range of -32768 to 32767, and an
unsigned range from 0 to 65535. Here, the size parameter specifies
the maximum allowed display width, which is 255.
MEDIUMINT(size)
A medium integer with a signed range of -8388608 to 8388607,
and an unsigned range from 0 to 16777215. Here, the size parameter
specifies the maximum allowed display width, which is 255.
INT(size)
A medium integer with a signed range of -2147483648 to
2147483647, and an unsigned range from 0 to 4294967295. Here, the
size parameter specifies the maximum allowed display width, which is
255.
INTEGER(size) Same as INT.
BIGINT(size)
A medium integer with a signed range of -9223372036854775808
to 9223372036854775807, and an unsigned range from 0 to
18446744073709551615. Here, the size parameter specifies the
maximum allowed display width, which is 255.
FLOAT(p)
A floating point number value. If the precision (p) parameter is between
0 to 24, then the data type is set to FLOAT(), whilst if its from 25 to 53,
the data type is set to DOUBLE(). This behaviour is to make the storage
of values more efficient.
DOUBLE(size, d)
A floating point number value where the total digits are set by the size
parameter, and the number of digits after the decimal point is set by
the d parameter.
DECIMAL(size, d)
An exact fixed point number where the total number of digits is set by
the size parameters, and the total number of digits after the decimal
point is set by the d parameter.
For size, the maximum number is 65 and the default is 10, whilst for d,
the maximum number is 30 and the default is 10.
DEC(size, d) Same as DECIMAL.
Example 3: sql data types
• number(num) - whole numbers up to num digits
• number(num,num2) - num whole numbers up to num2 decimals
• char(num) - fixed length character/string
• varchar2(num) - used for varying length data
• date - full date
• currency - used for prices