NEVO

SQL Data Types

In SQL, data types are used to define the type of data that can be stored in a column of a table. Each column in a table is associated with a specific data type that determines the kind of values that can be stored in that column. Understanding data types is essential for designing efficient and well-structured databases.

Common Data Types in SQL

SQL supports a wide range of data types that can be broadly categorized into the following groups:

Numeric Data Types: Used to store numeric values such as integers, decimals, and floating-point numbers.
String Data Types: Used to store character strings such as text and alphanumeric values.
Date/Time Data Types: Used to store date and time values.
Special Data Types: Used to store special values such as binary data, JSON, and XML.

Let's explore each of these data types in more detail.

Numeric Data Types

INT: Used to store whole numbers (integers) within a specific range.

CREATE TABLE products (
  id INT PRIMARY KEY,
  -- the quantity is an integer
  quantity INT
);
 
/*
 inserting a product with
 id = 1 and quantity = 10
 the quantity is stored as 10
 */
INSERT INTO
  products (id, quantity)
VALUES
  (1, 10);

DECIMAL: Used to store fixed-point numbers with a specified precision and scale.

CREATE TABLE products (
  id INT PRIMARY KEY,
  /*
   the price is a decimal number 
   with 10 digits and 2 decimal places
   */
  price DECIMAL(10, 2)
);
 
/*
 inserting a product with
 id = 1 and price = 19.99
 the price is stored as 19.99
 */
INSERT INTO
  products (id, price)
VALUES
  (1, 19.99);

FLOAT: Used to store floating-point numbers with a specified precision.

CREATE TABLE products (
  id INT PRIMARY KEY,
  -- the weight is a floating-point number
  weight FLOAT
);
 
/*
 inserting a product with
 id = 1 and weight = 2.5
 the weight is stored as 2.5
 */
INSERT INTO
  products (id, weight)
VALUES
  (1, 2.5);

DOUBLE: Used to store double-precision floating-point numbers.

CREATE TABLE products (
  id INT PRIMARY KEY,
  -- the value is a double-precision floating-point number
  value DOUBLE
);
 
/*
 inserting a product with
 id = 1 and value = 123.45
 the value is stored as 123.45
 */
INSERT INTO
  products (id, value)
VALUES
  (1, 123.45);

String Data types

CHAR: Used to store fixed-length character strings.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  /*
   the name is a fixed-length string 
   with a maximum length of 50 characters
   */
  name CHAR(50)
);
 
/*
 inserting a customer with
 id = 1 and name = 'John'
 the name is stored as 'John__________' (padded with spaces)
 */
INSERT INTO
  customers (id, name)
VALUES
  (1, 'John');

Note: whenever you define a CHAR data type, it will always store the fixed length of characters. If you store a string with fewer characters, it will pad the remaining characters with spaces. like if you store a string 'John' in a CHAR(10) column, it will be stored as 'John--------' (padded with spaces).

VARCHAR: Used to store variable-length character strings.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  /*
   the email is a variable-length string
   with a maximum length of 100 characters
   */
  email VARCHAR(100)
);
 
/*
 inserting a customer with
 id = 1 and email = 'hello@gmail.com'
 the email is stored as 'hello@gmail.com'
 */
INSERT INTO
  customers (id, email)
VALUES
  (1, 'hello@gmail.com');

Note: VARCHAR data type is used when you want to store a string with a variable length. It will only store the actual length of the string without any padding. For example, if you store a string 'John' in a VARCHAR(10) column, it will be stored as 'John' (without any padding).

TEXT: Used to store large text values.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  -- the address is a large text value
  address TEXT
);
 
/*
 inserting a customer with
 id = 1 and address = '123 Main Street, City, Country'
 the address is stored as '123 Main Street, City, Country'
 */
INSERT INTO
  customers (id, address)
VALUES
  (1, '123 Main Street, City, Country');

Date/Time Data types

DATE: Used to store date values in the format YYYY-MM-DD.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  /*
   the order_date is a date value
   format: YYYY-MM-DD
   */
  order_date DATE
);
 
/*
 inserting an order with
 id = 1 and order_date = '2022-10-15'
 the order_date is stored as '2022-10-15'
 */
INSERT INTO
  orders (id, order_date)
VALUES
  (1, '2022-10-15');

TIME: Used to store time values in the format HH:MM:SS.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  /*
   the delivery_time is a time value
   format: HH:MM:SS
   */
  delivery_time TIME
);
 
/*
 inserting an order with
 id = 1 and delivery_time = '14:30:00'
 the delivery_time is stored as '14:30:00'
 */
INSERT INTO
  orders (id, delivery_time)
VALUES
  (1, '14:30:00');

DATETIME: Used to store date and time values in the format YYYY-MM-DD HH:MM:SS.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  /*
   the created_at is a date and time value
   format: YYYY-MM-DD HH:MM:SS
   */
  created_at DATETIME
);
 
/*
 inserting an order with
 id = 1 and created_at = '2022-10-15
 the created_at is stored as '2022-10-15 14:30:00'
 */
INSERT INTO
  orders (id, created_at)
VALUES
  (1, '2022-10-15 14:30:00');

Special Data types

BLOB: Used to store binary large objects such as images and multimedia files.

CREATE TABLE products (
  id INT PRIMARY KEY,
  -- the image is a binary large object
  image BLOB
);
 
/*
 inserting a product with
 id = 1 and image = 'binary data'
 the image is stored as 'binary data'
 */
INSERT INTO
  products (id, image)
VALUES
  (1, 'binary data');

JSON: Used to store JSON (JavaScript Object Notation) data.

CREATE TABLE products (
  id INT PRIMARY KEY,
  -- the details is JSON data
  details JSON
);
 
/*
 inserting a product with
 id = 1 and details = '{"name": "Product 1", "price": 19.99}'
 the details are stored as '{"name": "Product 1", "price": 19.99}'
 */
INSERT INTO
  products (id, details)
VALUES
  (1, '{"name": "Product 1", "price": 19.99}');

XML: Used to store XML (eXtensible Markup Language) data.

CREATE TABLE products (
  id INT PRIMARY KEY,
  -- the description is XML data
  description XML
);
 
/*
 inserting a product with
 id = 1 and description = '<product><name>Product 1</name><price>19.99</price></product>'
 the description is stored as '<product><name>Product 1</name><price>19.99</price></product>'
 */
INSERT INTO
  products (id, description)
VALUES
  (
    1,
    '<product><name>Product 1</name><price>19.99</price></product>'
  );

For more about SQL data types, you can refer to the official documentation of the database management system you are using.

List of SQL Data Types

Special thanks to Prince Kumar Prasad for contributing to this guide on Nevo Code.