SQL Data Types — Numbers, Strings, Dates, etc.

A school stores names, dates and marks. Treating all three as interchangeable text makes validation and sorting harder.

A data type describes the kind of value a column or expression can represent. Its exact range, validation and conversion rules depend on the database engine.

Choose integer types for whole counts, suitable decimal types for exact monetary amounts, text types for names and date/time types for calendar values. Precision describes how many digits a numeric type can store; scale describes its fractional digits. A type and a constraint serve different purposes: a numeric type alone does not restrict marks to 0–100.

Related concepts: Data Constraints and Combine SQL Conditions.

Every value in a database has a type. Just like in everyday life where names are words and ages are numbers, SQL needs to know what kind of data you're storing—this is where data types come in.

Data types help define how much space a value needs, what operations are allowed on it, and how it behaves. Assigning the correct type is like assigning the right role in a play—it ensures your data behaves as expected.

SQL data types are broadly classified into:

• Numeric Types: For storing numbers (like marks or age)
• Character/String Types: For text (like names and cities)
• Date and Time Types: For time-sensitive data (like date of birth)
• Boolean: For true/false values (used in conditions)

SQL · portable concepts; preserved examples identify their engine

Common Types

• INT: Whole numbers. Ideal for roll numbers or age.
• FLOAT: Numbers with decimals. Useful for percentage or fee amounts.
• DECIMAL(p,s): Fixed-precision decimal. For storing money accurately.

Example

CREATE TABLE student_marks (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  maths_marks FLOAT,
  science_marks DECIMAL(5,2)
);

SQL · portable concepts; preserved examples identify their engine

Common Types

• CHAR(n): Fixed-length string (e.g., 10 characters exactly)
• VARCHAR(n): Variable-length string up to 'n' characters
• TEXT: Large blocks of text (not used often for structured tables)

Example

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class CHAR(4),
  city VARCHAR(30)
);

SQL · portable concepts; preserved examples identify their engine

Common Types

• DATE: For date only (YYYY-MM-DD)
• TIME: For time only (HH:MM:SS)
• DATETIME or TIMESTAMP: For date and time

Example

CREATE TABLE attendance (
  roll_no INT,
  date_of_entry DATE,
  entry_time TIME,
  PRIMARY KEY (roll_no, date_of_entry)
);

SQL · portable concepts; preserved examples identify their engine

Although not present in every SQL version (e.g., MySQL handles it as TINYINT), BOOLEAN is used to store TRUE or FALSE values.

Example

CREATE TABLE hostel_allocation (
  roll_no INT,
  has_room BOOLEAN
);

• Keep it specific: Use VARCHAR(30) for city names instead of TEXT.
• Don’t oversize: No need for VARCHAR(255) if names will only be 50 characters max.
• Use DECIMAL for money: Avoid rounding errors that come with FLOAT.

SQLite uses type affinity and flexible typing unless additional features such as STRICT tables apply. MySQL and PostgreSQL enforce different type rules. FLOAT is approximate, VARCHAR length behavior is engine-specific, and postal codes should usually be text because leading zeros matter.

Compute the class average as a fractional value instead of truncating an integer division.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT AVG(marks) AS average_marks FROM practice_students;

AVG aggregates the three marks 85, 70 and 85, producing 240 / 3 = 80. A type choice must still be paired with the school’s validation rules.

A data type describes the kind of value a column or expression can represent. Its exact range, validation and conversion rules depend on the database engine.

AVG aggregates the three marks 85, 70 and 85, producing 240 / 3 = 80. A type choice must still be paired with the school’s validation rules.

Continue your understanding with Data Constraints and Combine SQL Conditions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.