SQL Data Types | Data Types in SQL - sql - sql tutorial - learn sql



 data types in sql
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

What is data types?

  • A data type, in programming, is a classification that specifies which type of value a variable has and what type of mathematical, relational or logical operations can be applied to it without causing an error.
  • Each column in a database table is required to have a name and a data type.
  • SQL developers have to decide what types of data will be stored inside each and every table column when creating a SQL table.
  • The data type is a label and a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.
  • It is necessary to specify the data type for each column when we create a table. So exactly what are the different types of data we can store in a table
  • We can nevertheless group the different data types into the following categories:
  • We can nevertheless group the different data types into the following categories:
    • Numeric
    • Character String
    • Date/Datetime
    • Binary
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Numeric

  • This type of data stores numerical values.
  • Data types that fall in this category include Integer, Float, Real, Numeric, or Decimal. Common functions that operate on this type of data include COUNT, SUM, MAX, MIN, and AVG.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Character String

  • This type of data stores character values. The two common types are CHAR(n) and VARCHAR(n).
  • The CHAR(n) data type holds n characters, padded with spaces at the end if needed.
  • VARCHAR stands for Varying Char, meaning that the length of the field can vary. For example, a VARCHAR (10) data type holds up to 10 characters.
  • But if data is only 8 characters long, then it will only store 8 characters. Common functions that operate on this type of data are discussed in String Functions
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Binary

  • This type of data allows us to store binary objects in a database table.
  • Data types that fall in this category include Blob, Binary, and Raw.
  • Please note that a field of binary data type cannot be used as keys, and one cannot build a table index using a binary column.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Date/Datetime

  • This type of data allows us to store date or datetime in a database table.
  • Different databases have very different implementations of the date / datetime data type.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Data Types

  • PointBase supports the following data types for its column and parameter declarations
  • PointBase supports the following data types for its column and parameter declarations
    • CHARACTER [(length)] or CHAR [(length)]
    • VARCHAR (length)
    • BOOLEAN
    • SMALLINT
    • INTEGER or INT
    • DECIMAL [(p[,s])] or DEC [(p[,s])]
    • NUMERIC [(p[,s])]
    • REAL
    • FLOAT(p)
    • DOUBLE PRECISION
    • DATE
    • TIME
    • TIMESTAMP
    • CLOB [(length)] or CHARACTER LARGE OBJECT [(length)] or CHAR LARGE OBJECT [(length)]
    • BLOB [(length)] or BINARY LARGE OBJECT [(length)]
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

CHARACTER [(length)] or CHAR [(length)]

  • The CHARACTER data type accepts character strings, including Unicode, of a fixed length.
  • The length of the character string should be specified in the data type declaration; for example, CHARACTER(n) where n represents the desired length of the character string.
  • If no length is specified during the declaration, the default length is 1.
  • The minimum length of the CHARACTER data type is 1 and it can have a maximum length up to the table page size.
  • Character strings that are larger than the page size of the table can be stored as a Character Large Object (CLOB).
  • NOTE: CHARACTER(0) is not allowed and raises an exception.
  • If you assign a value to a CHARACTER column containing fewer characters than the defined length, the remaining space is filled with blanks characters.
  • Any comparisons made to a CHARACTER column must take these trailing spaces into account.
  • Attempting to assign a value containing more characters than the defined length results in the truncation of the character string to the defined length.
  • If any of the truncated characters are not blank, an error is raised.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Character String Examples:

VARCHAR(10)

  • Valid
'Race car'
'RACECAR'
'24865'
'1998-10-25'
'1998-10-25 '
  • Invalid
24865
1998-10-25
'Date: 1998-10-25'
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

BOOLEAN

  • The BOOLEAN data type supports the storage of two values: TRUE or FALSE.
  • No parameters are required when declaring a BOOLEAN data type.
  • Use the case insensitive keywords TRUE or FALSE to assign a value to a BOOLEAN data type.
  • Comparisons using the BOOLEAN data type should also use these keywords.
  • If you attempt to assign any other value to a BOOLEAN data type, an error is raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

BOOLEAN

  • Valid
TRUE
true 
True
False
  • Invalid
1
0
Yes
No
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

SMALLINT

  • The SMALLINT data type accepts numeric values with an implied scale of zero.
  • It stores any integer value between the range 2^ -15 and 2^15 -1. Attempting to assign values outside this range causes an error.
  • If you assign a numeric value with a precision and scale to a SMALLINT data type, the scale portion truncates, without rounding.
  • NOTE: To store values beyond the range (2^-15) to (2^15)-1, use the INTEGER data type.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

  • Valid
-32768
0
-30.3 (digits to the right of the decimal point are truncated)
32767
  • Invalid
-33,000,567
-32769
32768
1,897,536,000
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

INTEGER or INT

  • The INTEGER data type accepts numeric values with an implied scale of zero.
  • It stores any integer value between the range 2^ -31 and 2^31 -1.
  • Attempting to assign values outside this range causes an error.
  • If you assign a numeric value with a precision and scale to an INTEGER data type, the scale portion truncates, without rounding.
  • NOTE: To store integer values beyond the range (2^-31) to (2^31)-1, use the DECIMAL data type with a scale of zero.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

INTEGER or INT

  • Valid
-2147483648
-1025
0
1025.98 (digits to the right of the decimal point are truncated)
2147483647
  • Invalid
-1,025,234,000,367
-2147483649
2147483648
1,025,234,000,367
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

DECIMAL [(p[,s])] or DEC [(p[,s])]

  • The DECIMAL data type accepts numeric values, for which you may define a precision and a scale in the data type declaration.
  • The precision is a positive integer that indicates the number of digits that the number will contain.
  • The scale is a positive integer that indicates the number of these digits that will represent decimal places to the right of the decimal point.
  • The scale for a DECIMAL cannot be larger than the precision.
  • DECIMAL data types can be declared in one of three different ways.
  • The declaration of it controls how the number is presented to an SQL query, but not how it is stored.
  • The DECIMAL data type accepts numeric values, for which you may define a precision and a scale in the data type declaration. The precision is a positive integer that indicates the number of digits that the number will contain. The scale is a positive integer that indicates the number of these digits that will represent decimal places to the right of the decimal point. The scale for a DECIMAL cannot be larger than the precision. DECIMAL data types can be declared in one of three different ways. The declaration of it controls how the number is presented to an SQL query, but not how it is stored.
    • DECIMAL - Precision defaults to 38, Scale defaults to 0
    • DECIMAL(p) - Scale defaults to 0
    • DECIMAL(p, s) - Precision and Scale are defined by the user
  • In the above examples, p is an integer representing the precision and s is an integer representing the scale.
  • NOTE: If you exceed the number of digits expected to the left of the decimal point, an error is thrown. If you exceed the number of expected digits to the right of the decimal point, the extra digits are truncated.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

DECIMAL(10,3)

  • Valid
1234567
1234567.123
1234567.1234 (Final digit is truncated)
-1234567
-1234567.123
-1234567.1234 (Final digit is truncated)
  • Invalid
12345678
12345678.12
12345678.123
-12345678
-12345678.12
-12345678.123
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

NUMERIC [(p[,s])]

  • PointBase treats the NUMERIC data type in exactly the same way as the DECIMAL data type
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

REAL

  • The REAL data type accepts approximate numeric values, up to a precision of 64.
  • No parameters are required when declaring a REAL data type.
  • If you attempt to assign a value with a precision greater than 64 an error is raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

REAL

  • Valid
-2345
0
1E-3
1.245
123456789012345678901234567890
  • Invalid
123,456,789,012,345,678,901,234,567,890,123
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

FLOAT(p)

  • The FLOAT data type accepts approximate numeric values, for which you may define a precision up to a maximum of 64.
  • If no precision is specified during the declaration, the default precision is 64.
  • Attempting to assign a value lager than the declared precision will cause an error to be raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

FLOAT(8)

  • Valid
12345678
1.2
123.45678
-12345678
-1.2
-123.45678
  • Invalid
123456789
123.456789
-123456789
-123.456789
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

DOUBLE PRECISION

  • The REAL data type accepts approximate numeric values, up to a precision of 64.
  • No parameters are required when declaring a DOUBLE PRECISION data type.
  • If you attempt to assign a value with a precision greater than 64 an error is raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

DOUBLE PRECISION

  • Valid
12345678901234567890123456789012345678901234567890123456
7890

-12345678901234567890123456789012345678901234567890123456
7890
  • Invalid
123,456,789,012,345,678,901,234,567,890,123,123,456,789,

012,345,678,901,234,567,890

-123,456,789,012,345,678,901,234,567,890,123,123,456,789,

012,345,678,901,234,567,890
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

DATE

  • The DATE data type accepts date values.
  • No parameters are required when declaring a DATE data type.
  • Date values should be specified in the form: YYYY-MM-DD.
  • However, PointBase will also accept single digits entries for month and day values.
  • Month values must be between 1 and 12, day values should be between 1 and 31 depending on the month and year values should be between 0 and 9999.
  • Values assigned to the DATE data type should be enclosed in single quotes, preceded by the case insensitive keyword DATE; for example, DATE '1999-04-04'.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

DATE

  • Valid
DATE '1999-01-01'
DATE '2000-2-2'
date '0-1-1'
  • Invalid
DATE '1999-13-1'
date '2000-2-30'
'2000-2-27'
date 2000-2-27
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

TIME

  • The TIME data type accepts time values.
  • No parameters are required when declaring a TIME data type.
  • Date values should be specified in the form: HH:MM:SS. An optional fractional value can be used to represent nanoseconds.
  • The minutes and seconds values must be two digits.
  • Hour values should be between zero 0 and 23, minute values should be between 00 and 59 and second values should be between 00 and 61.999999.
  • Values assigned to the TIME data type should be enclosed in single quotes, preceded by the case insensitive keyword TIME; for example, TIME '07:30:00'.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

TIME

  • Valid
TIME '00:00:00'
TIME '1:00:00'
TIME '23:59:59'
time '23:59:59.99'
  • Invalid
TIME '00:62:00'
TIME '00:3:00'
TIME '23:01'
'24:01:00'
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

TIMESTAMP

  • The TIMESTAMP data type accepts timestamp values, which are a combination of a DATE value and a TIME value.
  • No parameters are required when declaring a TIMESTAMP data type.
  • Timestamp values should be specified in the form: YYYY-MM-DD HH:MM:SS.
  • There is a space separator between the date and time portions of the timestamp.
  • All specifications and restrictions noted for the DATE and TIME data types also apply to the TIMESTAMP data type.
  • Values assigned to the TIMESTAMP data type should be enclosed in single quotes, preceded by the case insensitive keyword TIMESTAMP; for example, TIMESTAMP '1999-04-04 07:30:00'.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

TIMESTAMP

  • Valid
TIMESTAMP `1999-12-31 23:59:59.99'
TIMESTAMP `0-01-01 00:00:00'
  • Invalid
1999-00-00 00:00:00
TIMESTAMP `1999-01-01 00:64:00'

CLOB [(length)] or CHARACTER LARGE OBJECT [(length)] or CHAR LARGE OBJECT [(length)]

  • The Character Large Object (CLOB) data type accepts character strings longer than those that are allowed in the CHARACTER [(length)] or VARCHAR (length) data types.
  • The CLOB declaration uses the following syntax to specify the length of the CLOB in bytes:
n [K | M | G]
  • In the above syntax, n is an unsigned integer that represents the length.
  • K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively.
  • If K, M, or G is specified in addition to n, then the actual length of n is the following:
  • In the above syntax, n is an unsigned integer that represents the length. K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively. If K, M, or G is specified in addition to n, then the actual length of n is the following:
    • K = n * 1024
    • M = n * 1,048,576
    • G = n * 1,073,741,824
  • The maximum size allowed for CLOB data types is 2 gigabytes. If a length is not specified, then a default length of one byte is used. CLOB values can vary in length from one byte up to the specified length. NOTE: The CLOB data type supports Unicode data.

BLOB [(length)] or BINARY LARGE OBJECT [(length)]

  • The Binary Large Object (BLOB) data type accepts binary values.
  • The BLOB declaration uses the following syntax to specify the length in bytes:
n [K | M | G]
  • In the above syntax, n is an unsigned integer that represents the length. K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively. If K, M, or G is specified in addition to n, then the actual length of n is the following:
  • In the above syntax, n is an unsigned integer that represents the length. K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively. If K, M, or G is specified in addition to n, then the actual length of n is the following:
    • K = n * 1024
    • M = n * 1,048,576
    • G = n * 1,073,741,824
  • The maximum size allowed for BLOB data types is 2 gigabytes. If a length is not specified, then a default length of one byte is used. BLOB values can vary in length from one byte up to the specified length.
  • NOTE: BLOB data types cannot be used with SQL scalar functions.

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Data Types in SQL