SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | Datatypes

In SQL, datatypes specify the type of data that a column can store. Every column in a table is required to have a datatype associated with it. Datatypes can vary between different relational database management systems (RDBMS), but there are many commonalities.

Here's an overview of some commonly used SQL datatypes:

Numeric Datatypes:

INTEGER: Stores whole numbers. Variants include SMALLINT, BIGINT, and sometimes TINYINT.
DECIMAL(p, s) or NUMERIC(p, s): Fixed precision and scale numbers. p is the total number of digits, and s is the number of digits after the decimal.
FLOAT or REAL: Floating-point numbers.
DOUBLE PRECISION: Double-precision floating-point numbers.

Date and Time:

DATE: Stores a date (year, month, day).
TIME: Stores a time (hours, minutes, seconds).
TIMESTAMP: Stores date and time together.

Character Strings:

CHAR(n): Fixed-length character string, where n is the string length.
VARCHAR(n) or CHARACTER VARYING(n): Variable-length character string with a maximum length of n.
TEXT: For storing long text. Not all databases have this type; in some RDBMSs, VARCHAR can be used without specifying a length.

Unicode Characters:

NCHAR(n) or NATIONAL CHAR(n): Fixed-length Unicode character string.
NVARCHAR(n) or NATIONAL CHARACTER VARYING(n): Variable-length Unicode character string.

Binary Data:

BINARY(n): Fixed-length binary data.
VARBINARY(n): Variable-length binary data.
BLOB: For storing long binary data, like images or files.

Miscellaneous:

BOOLEAN: Stores TRUE or FALSE values.
ENUM: A list of predefined strings. Used in MySQL.

Array:

ARRAY: Some databases, like PostgreSQL, support array types, where you can define a column to hold an array of another datatype, such as INT[] for an integer array.

JSON:

JSON: Stores JSON-formatted data. Supported by databases like PostgreSQL and MySQL.
JSONB: Binary JSON data type. Specifically in PostgreSQL.

Spatial:

GEOMETRY, POINT, LINE, etc.: Datatypes used for storing spatial data. Supported in databases like MySQL and PostgreSQL with PostGIS.

User-Defined Types:

DOMAIN: Custom types defined by users (not supported by all RDBMS).

Remember, the exact support and specification of these datatypes can vary by RDBMS. It's important to refer to the official documentation of the specific database system you're using when defining tables and selecting datatypes.

Numeric Datatypes in SQL:

Example:

CREATE TABLE example_table (
  id INTEGER,
  price DECIMAL(10, 2),
  quantity INT
);

String Datatypes in SQL:

Example:

CREATE TABLE example_table (
  name VARCHAR(50),
  description TEXT
);

Date and Time Datatypes in SQL:

Example:

CREATE TABLE example_table (
  order_date DATE,
  delivery_time TIMESTAMP
);

Boolean Datatypes in SQL:

Example:

CREATE TABLE example_table (
  is_active BOOLEAN
);

Binary Datatypes in SQL:

Example:

CREATE TABLE example_table (
  image_data BLOB
);

Special-Purpose Datatypes in SQL:

Example:

CREATE TABLE example_table (
  user_id UUID,
  json_data JSON,
  xml_content XML
);

User-Defined Datatypes in SQL:

Example:

CREATE TYPE email_address AS VARCHAR(100);
CREATE TABLE example_table (
  user_email email_address
);

Altering Datatypes in SQL:

Example:

ALTER TABLE example_table
ALTER COLUMN quantity SET DATA TYPE SMALLINT;

Datatype Conversion in SQL:

Example:

SELECT CAST(column_name AS INT) FROM example_table;

Handling NULL Values with SQL Datatypes:
- Example:
```
CREATE TABLE example_table (
  name VARCHAR(50) NULL,
  description TEXT
);
```
- Use NULL where appropriate to represent missing or unknown information.