SQL - Data types

SQL data types play a crucial role in defining the characteristics of columns in database tables. They help ensure data integrity and provide guidelines for the type of data that can be stored in a particular column. Here's a breakdown of SQL data types based on the categories mentioned:

String Data Types:

1. CHAR(size): Fixed-length string, size specifies the length (up to 255 characters).
2. VARCHAR(size): Variable-length string, size specifies the maximum length (up to 65535 characters).
3. BINARY(size): Fixed-length binary string.
4. VARBINARY(size): Variable-length binary string.
5. TINYTEXT, TEXT(size), MEDIUMTEXT, LONGTEXT: Variable-length string with varying maximum lengths.
6. TINYBLOB, BLOB(size), MEDIUMBLOB, LONGBLOB: Binary large objects with varying maximum lengths.
7. ENUM(val1, val2, ...), SET(val1, val2, ...): String objects with predefined values.

Numeric Data Types:

1. INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT: Various sizes of integers, signed or unsigned.
2. FLOAT(M,D), DOUBLE(M,D): Floating-point numbers with specified precision.
3. DECIMAL(M,D), NUMERIC(M,D): Fixed precision and scale numbers.
4. REAL: Single-precision floating-point number.

Date and Time Data Types:

1. DATE: Stores date in 'YYYY-MM-DD' format.
2. DATETIME: Stores date and time in 'YYYY-MM-DD HH:MM:SS' format.
3. TIMESTAMP: A timestamp from 1970 to 2037.
4. TIME: Stores time in 'HH:MM:SS' format.
5. YEAR(M): Stores a year in 2 or 4 digits.

MySQL-Specific Data Types:

1. BIT: Integer data type with values 0 or 1.
2. JSON: Stores JSON data.

MS SQL Server-Specific Data Types:

1. bit, tinyint, smallint, int, bigint: Various sizes of integers.
2. float(n), real: Floating-point numbers.
3. decimal(p, s), numeric(p, s), smallmoney, money: Fixed precision and scale numbers.
4. datetime, datetime2, smalldatetime, date, time, timestamp: Date and time data types.

Oracle-Specific Data Types:

1. CHAR(size), NCHAR(size), VARCHAR2(size), NVARCHAR2(size): String data types.
2. NUMBER(p, s), FLOAT(p), BINARY_FLOAT, BINARY_DOUBLE: Numeric data types.
3. DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND: Date and time data types.
4. BLOB, BFILE, CLOB, NCLOB, RAW(size), LONG RAW: Large object data types.

MS Access-Specific Data Types:

1. Short Text, Long Text: String data types.
2. Number, Large Number: Numeric data types.
3. Date/Time, Date/Time Extended: Date and time data types.
4. Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, Attachment: Specialized data types.

Understanding and choosing the appropriate data types is crucial for designing efficient and accurate database structures. It ensures data consistency, prevents errors, and contributes to the overall performance of database operations.