LECTURE #29|DATA TYPES IN SQL|
NOTES LINK: https://drive.google.com/file/d/1ZfEK...
Attribute Data Types and Domains in SQL
The basic data types available for attributes include numeric, character string, bit string, Boolean, date, and time.
1. Numeric:
Integers of various sizes and Floating point numbers with various precisions.
Keyword for Integer : INTEGER or INT, SMALLINT
Keywords for Floating Point (Real) : FLOAT, REAL, DOUBLE PRECESION
Formatted Numbers: DECIMAL (i, j) or DEC (i, j), NUMERIC (i, j)
Where i represents: Precision (Total Number of Decimal Digits in the Number)
j represents: Scale (Number of Digits After the Decimal)
For eg. 467298.24
Here Precision i= 8 and Scale j=2, Thus Number is DEC (8, 2).
Default for Scale is Zero.
Default for Precision is implementation oriented.
Difference between DEC (i, j) and NUMERIC (i, j):
NUMERIC determines the exact precision and scale where; DEC specifies only the exact scale, the precision is equal to or greater than what is specified by the programmer. i.e. DECIMAL columns can have a larger than specified precision if this is more convenient or efficient for the database system.
2. Character String:
Two Possibilities:
(i) Fixed Length: CHAR (n) or CHARACTER (n); n is number of characters.
For fixed length strings, a shorter string is padded with blank characters to the right. For example, if the value ‘Smith’ is for an attribute of type CHAR(10), it is padded with five blank characters to become ‘Smith’ if needed. Padded blanks are generally ignored when strings are compared.
(ii) Variable Length: VARCHAR (n) or CHAR VARYING (n) or CHARACTER VARYING (n); where n is the maximum number of characters.
For comparison purposes, strings are considered ordered in alphabetic (or lexicographic) order; if a string str1 appears before another string str2 in alphabetic order, then str1 is considered to be less than str2.
There is also a concatenation operator denoted by || (double vertical bar) that can concatenate two strings in SQL. For example, ‘abc’ || ‘XYZ’ results in a single string ‘abcXYZ’.
CHARACTER LARGE OBJECT (CLOB):
It is variable-length string data type available in SQL to specify columns that have large text values, such as documents. The CLOB maximum length can be specified in kilobytes (K), megabytes (M), or gigabytes (G). For example, CLOB(20M) specifies a maximum length of 20 megabytes.
3. Bit-string:
Two possibilities:
(i) Fixed Length: BIT(n), n is the number of bits
(ii) Varying Length: BIT VARYING(n), n is the maximum number of bits
The default for n, the length of a character string or bit string, is 1.
Literal bit strings are placed between single quotes but preceded by a B to distinguish them from character strings; for example, B‘10101’.
BINARY LARGE OBJECT (BLOB):
It is variable-length bit string data type available in SQL to specify columns that have large binary values, such as images. As for CLOB, the maximum length of a BLOB can be specified in kilobits (K), megabits (M), or gigabits (G). For example, BLOB(30G) specifies a maximum length of 30 gigabits.
4. BOOLEAN:
A Boolean data type has the traditional values of TRUE or FALSE. In SQL, because of the presence of NULL values, a three-valued logic is used, so a third possible value for a Boolean data type is UNKNOWN.
5. DATE & TIME:
The DATE data type has ten positions, and its components are YEAR, MONTH, and DAY in the form YYYY-MM-DD.
The TIME data type has at least eight positions, with the components HOUR, MINUTE, and SECOND in the form HH:MM: SS.
Only valid dates and times should be allowed by the SQL implementation. This implies that months should be between 1 and 12 and days must be between 01 and 31; furthermore, a day should be a valid day for the corresponding month.
Less than comparison can be used with dates or times—an earlier date is considered to be smaller than a later date, and similarly with time.
Literal values are represented by single-quoted strings preceded by the keyword DATE or TIME; for example, DATE ‘2014-09-27’ or TIME ‘09:12:47’.
A data type TIME(i), where i is called time fractional seconds precision, specifies i + 1 additional positions for TIME—one position for an additional period (.) separator character, and i positions for specifying decimal fractions of a second.
A TIME WITH TIME ZONE data type includes an additional six positions for specifying the displacement from the standard universal time zone, which is in the range +13:00 to –12:59 in units of HOURS: MINUTES. If WITH TIME ZONE is not included, the default is the local time zone for the SQL session.