SQL Data Types | Academic Block

SQL DATA TYPES

Learn about SQL data types and how SQLite stores numbers, text, dates, binary data, and other values.

What are SQL Data Types?

Data Types define the kind of value that can be stored in a database column. Different database systems provide different data types. SQLite uses a flexible type system based mainly on five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB.

SQLite Storage Classes

Storage Class	Description	Example
NULL	Represents a missing or unknown value	NULL
INTEGER	Whole numbers	42
REAL	Floating-point numbers	19.95
TEXT	Text and strings	‘Delhi’
BLOB	Binary data	Binary file data

INTEGER

The INTEGER storage class is used for whole numbers without a fractional part. It is commonly used for IDs, quantities, ages, counts, and other whole-number values.

DROP TABLE IF EXISTS inventory;

CREATE TABLE inventory (
  item_id INTEGER,
  item_name TEXT,
  quantity INTEGER
);

INSERT INTO inventory (item_id, item_name, quantity)
VALUES
  (101, 'Keyboard', 15),
  (102, 'Mouse', 28),
  (103, 'Monitor', 7);

SELECT *
FROM inventory
ORDER BY item_id;

REAL

The REAL storage class is used for floating-point numbers. It is useful for values such as prices, measurements, percentages, and averages.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_name TEXT,
  price REAL,
  rating REAL
);

INSERT INTO products (product_name, price, rating)
VALUES
  ('Desk Lamp', 1299.50, 4.5),
  ('USB Hub', 749.99, 4.2),
  ('Laptop Stand', 1899.75, 4.7);

SELECT *
FROM products
ORDER BY price;

TEXT

The TEXT storage class is used for character strings. Names, addresses, product descriptions, categories, and email addresses are common examples.

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT,
  city TEXT
);

INSERT INTO customers (customer_id, customer_name, city)
VALUES
  (1, 'Anaya', 'Delhi'),
  (2, 'Vihaan', 'Pune'),
  (3, 'Myra', 'Bengaluru');

SELECT customer_name, city
FROM customers
WHERE city = 'Delhi';

NULL

NULL represents a missing, unknown, or unavailable value. It is different from zero, an empty string, or the text value 'NULL'.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER,
  employee_name TEXT,
  phone TEXT
);

INSERT INTO employees (employee_id, employee_name, phone)
VALUES
  (1, 'Riya', '9876543210'),
  (2, 'Dev', NULL),
  (3, 'Tara', '9123456780');

SELECT employee_name, phone
FROM employees
WHERE phone IS NULL;

BLOB

BLOB stands for Binary Large Object. SQLite can store binary data such as images, documents, or other files using the BLOB storage class.

In SQLite, hexadecimal literals can be used to create small BLOB values for testing.

DROP TABLE IF EXISTS files;

CREATE TABLE files (
  file_id INTEGER,
  file_name TEXT,
  file_data BLOB
);

INSERT INTO files (file_id, file_name, file_data)
VALUES
  (1, 'sample.bin', X'48656C6C6F'),
  (2, 'data.bin', X'53514C');

SELECT
  file_id,
  file_name,
  typeof(file_data) AS data_type,
  length(file_data) AS size_bytes
FROM files
ORDER BY file_id;

Using typeof() in SQLite

SQLite provides the typeof() function to determine the storage class of a value.

SELECT
  typeof(25) AS integer_value,
  typeof(25.75) AS real_value,
  typeof('Hello SQL') AS text_value,
  typeof(NULL) AS null_value,
  typeof(X'414243') AS blob_value;

SQLite Boolean Values

SQLite does not have a separate Boolean storage class. Boolean values are generally represented using integers: 0 for false and 1 for true.

DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
  task_id INTEGER,
  task_name TEXT,
  completed INTEGER
);

INSERT INTO tasks (task_id, task_name, completed)
VALUES
  (1, 'Read documentation', 1),
  (2, 'Practice SQL', 0),
  (3, 'Build database', 1);

SELECT
  task_name,
  completed,
  CASE
    WHEN completed = 1 THEN 'Yes'
    ELSE 'No'
  END AS is_completed
FROM tasks
ORDER BY task_id;

SQLite Date and Time Values

SQLite does not have a dedicated DATE or DATETIME storage class. Dates and times are commonly stored as TEXT, REAL, or INTEGER.

DROP TABLE IF EXISTS events;

CREATE TABLE events (
  event_id INTEGER,
  event_name TEXT,
  event_date TEXT
);

INSERT INTO events (event_id, event_name, event_date)
VALUES
  (1, 'SQL Workshop', '2026-08-27'),
  (2, 'Database Seminar', '2026-09-05'),
  (3, 'Technology Meetup', '2026-09-12');

SELECT
  event_name,
  event_date
FROM events
WHERE event_date >= '2026-09-01'
ORDER BY event_date;

SQLite Type Affinity

SQLite uses type affinity rather than enforcing data types in the same way as many traditional relational database systems. A column has an affinity that influences how SQLite stores values.

DROP TABLE IF EXISTS measurements;

CREATE TABLE measurements (
  measurement_id INTEGER,
  description TEXT,
  value REAL
);

INSERT INTO measurements (measurement_id, description, value)
VALUES
  (1, 'Temperature', 36.5),
  (2, 'Length', 12.75),
  (3, 'Weight', 68.4);

SELECT
  measurement_id,
  description,
  value,
  typeof(value) AS stored_type
FROM measurements
ORDER BY measurement_id;

Checking Different Data Types

The following example stores several types of values in one table and uses typeof() to show how SQLite stores them.

DROP TABLE IF EXISTS data_samples;

CREATE TABLE data_samples (
  sample_id INTEGER,
  sample_value
);

INSERT INTO data_samples (sample_id, sample_value)
VALUES
  (1, 125),
  (2, 45.75),
  (3, 'Database'),
  (4, NULL),
  (5, X'414243');

SELECT
  sample_id,
  typeof(sample_value) AS storage_class
FROM data_samples
ORDER BY sample_id;

Common SQLite Data Types

Data Type / Class	Typical Use	Example
INTEGER	IDs, quantities, counts	100
REAL	Decimal measurements and values	25.75
TEXT	Names, descriptions, dates	‘Delhi’
BLOB	Binary information	X’414243′
NULL	Missing or unknown values	NULL

Advantages of Using Appropriate Data Types

Makes database structures easier to understand.
Helps store values in an appropriate representation.
Makes queries and calculations easier to manage.
Improves consistency when designing database tables.
Helps developers understand the intended purpose of each column.

Best Practices

Choose column declarations that clearly describe the intended data.
Use INTEGER for whole-number values.
Use REAL when fractional numeric values are required.
Use TEXT for names, descriptions, and text-based dates.
Use NULL only when a value can genuinely be missing or unknown.
Use BLOB when binary data needs to be stored in SQLite.
Remember that SQLite’s type system differs from stricter database systems.

SQLite primarily uses five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Understanding these storage classes helps you design SQLite tables and write queries that handle data correctly.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.