data types in sql

1# Numeric Types
2BIT	TINYINT SMALLINT INT BIGINT DECIMAL NUMERIC FLOAT REAL
3
4# Date & Time Types
5DATE TIME DATETIME TIMESTAMP YEAR
6
7# Char & String Types (N) Denotes Unicode Versions
8CHAR VARCHAR TEXT NCHAR NVARCHAR NTEXT
9
10# Binary Data Types
11BINARY VARBINARY IMAGE
12
13# Misc
14CLOB BLOB XML JSON

1• number(num) - whole numbers up to num digits
2• number(num,num2) - num whole numbers up to num2 decimals
3• char(num) - fixed length character/string
4• varchar2(num) - used for varying length data
5• date - full date
6• currency - used for prices 

1-- Text Data Types:
2CHAR(size)
3Fixed length string which can contain letters, numbers and special
4characters. The size parameter sets the maximum string length, from
50 – 255 with a default of 1.
6VARCHAR(size) Variable length string similar to CHAR(), but with a maximum string
7length range from 0 to 65535.
8BINARY(size) Similar to CHAR() but stores binary byte strings.
9VARBINARY(size) Similar to VARCHAR() but for binary byte strings.
10TINYBLOB Holds Binary Large Objects (BLOBs) with a max length of 255 bytes.
11TINYTEXT Holds a string with a maximum length of 255 characters. Use
12VARCHAR() instead, as it’s fetched much faster.
13TEXT(size) Holds a string with a maximum length of 65535 bytes. Again, better to
14use VARCHAR().
15BLOB(size) Holds Binary Large Objects (BLOBs) with a max length of 65535
16bytes.
17MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters.
18MEDIUMBLOB Holds Binary Large Objects (BLOBs) with a max length of 16,777,215
19bytes.
20LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters.
21LONGBLOB Holds Binary Large Objects (BLOBs) with a max length of
224,294,967,295 bytes.
23ENUM(a, b, c,
24etc…)
25A string object that only has one value, which is chosen from a list of
26values which you define, up to a maximum of 65535 values. If a value
27is added which isn’t on this list, it’s replaced with a blank value instead.
28Think of ENUM being similar to HTML radio boxes in this regard.
29CREATE TABLE tshirts (color ENUM(‘red’, ‘green’,
30‘blue’, ‘yellow’, ‘purple’));
31SET(a, b, c, etc…)
32A string object that can have 0 or more values, which is chosen from a
33list of values which you define, up to a maximum of 64 values. Think of
34SET being similar to HTML checkboxes in this regard.

1SELECT TYPEOF(value);

1-- Numeric Data Types:
2BIT(size) A bit-value type with a default of 1. The allowed number of bits in a
3value is set via the size parameter, which can hold values from 1 to 64.
4TINYINT(size)
5A very small integer with a signed range of -128 to 127, and an
6unsigned range of 0 to 255. Here, the size parameter specifies the
7maximum allowed display width, which is 255.
8BOOL Essentially a quick way of setting the column to TINYINT with a size of
91. 0 is considered false, whilst 1 is considered true.
10BOOLEAN Same as BOOL.
11SMALLINT(size)
12A small integer with a signed range of -32768 to 32767, and an
13unsigned range from 0 to 65535. Here, the size parameter specifies
14the maximum allowed display width, which is 255.
15MEDIUMINT(size)
16A medium integer with a signed range of -8388608 to 8388607,
17and an unsigned range from 0 to 16777215. Here, the size parameter
18specifies the maximum allowed display width, which is 255.
19INT(size)
20A medium integer with a signed range of -2147483648 to
212147483647, and an unsigned range from 0 to 4294967295. Here, the
22size parameter specifies the maximum allowed display width, which is
23255.
24INTEGER(size) Same as INT.
25BIGINT(size)
26A medium integer with a signed range of -9223372036854775808
27to 9223372036854775807, and an unsigned range from 0 to
2818446744073709551615. Here, the size parameter specifies the
29maximum allowed display width, which is 255.
30FLOAT(p)
31A floating point number value. If the precision (p) parameter is between
320 to 24, then the data type is set to FLOAT(), whilst if its from 25 to 53,
33the data type is set to DOUBLE(). This behaviour is to make the storage
34of values more efficient.
35DOUBLE(size, d)
36A floating point number value where the total digits are set by the size
37parameter, and the number of digits after the decimal point is set by
38the d parameter.
39DECIMAL(size, d)
40An exact fixed point number where the total number of digits is set by
41the size parameters, and the total number of digits after the decimal
42point is set by the d parameter.
43For size, the maximum number is 65 and the default is 10, whilst for d,
44the maximum number is 30 and the default is 10.
45DEC(size, d) Same as DECIMAL.

11- Data Manipulation Language(DML)-(SELECT, INSTERT, UPDATE...)
2DML statements affect records in table. These are
3basic operations we perform on data such as selecting
4few records from a table, inserting new records,
5deleting unnecessary records, updating existing records.
6
72-Data Definition Language (CREATE , ALTER , DROP)...
8DDL statements are used to modify database or
9table schema. These statements handle the design
10and storage of database objects.
11
123-Data Control Language (GRANT , REVOKE)...
13DCL statements control the level of access that
14users have on database objects
15
164- Transaction Control Language (BEGIN TRAN, COMMIT TRAN, ROLLBACK)...
17
18TCL statements allows us to control and manage
19transactions to maintain the integrity of data
20withing SQL statements.