SQL code copied to buffer

SQLtest.online
  1. 1.  Introduction to Databases
  2. 2.  Types of Databases
  3. 3.  Relational Database Concepts
  4. 4.  Basic Data Types
  5. 5.  Understanding NULL Values in SQL
  6. 6.  Overview of SQL
  1. 1.  Select Data from a Table
  2. 2.  Filtering Data
  3. 3.  Combine Multiple Conditions
  4. 4.  Aliasing Columns
  5. 5.  Ordering Results
  6. 6.  Limiting Results with LIMIT and OFFSET
  7. 7.  Putting It All Together: WHERE, ORDER BY, and LIMIT
  1. 1.  Built-in SQL Functions
  2. 2.  Common String Functions
  3. 3.  Core Mathematical Functions
  4. 4.  Date and Time Functions
  5. 5.  Conditional Operator
  1. 1.  Basic Aggregation Functions
  2. 2.  Grouping Data
  3. 3.  Filtering Grouped Data
  4. 4.  Conditional Aggregation
  5. 5.  Advanced Aggregation
  1. 1.  Fundamentals of JOINs in SQL
  2. 2.  INNER JOIN - Combining Matching Rows
  3. 3.  LEFT JOIN - Including All Records from the Left Table
  4. 4.  RIGHT JOIN - Including All Records from the Right Table
  5. 5.  FULL OUTER JOIN - Combining Everything from Both Tables
  6. 6.  CROSS JOIN - The Cartesian Product
  7. 7.  SELF JOIN - Joining a Table to Itself
  8. 8.  Practical JOIN Scenarios and Techniques
  9. 9.  Join Algorithms
  1. 1.  Introduction to Subqueries - Nested Queries and Inline Views
  2. 2.  Subqueries in the WHERE Clause
  3. 3.  Correlated Subqueries
  4. 4.  Common Table Expressions (CTEs)
  5. 5.  Recursive CTEs
  6. 6.  Applying Recursive CTEs
  1. 1.  Window Functions
  2. 2.  Using ROW_NUMBER, RANK, DENSE_RANK, and NTILE
  3. 3.  Window Frames — Controlling the Window Boundaries
  1. 1.  The INSERT INTO Statement
  2. 2.  The UPDATE Statement
  3. 3.  The DELETE Statement
  1. 1.  The CREATE TABLE Statement
  2. 2.  The TRUNCATE and DROP TABLE Statements
  3. 3.  Temporary Tables
  4. 4.  Views
  1. 1.  Best Practices for Readable and Maintainable SQL Code
  2. 2.  Writing Efficient SQL Queries
  3. 3.  Understanding Query Optimization Methods
  1. 1.  The fastest flight option
  2. 2.  Calculate the average occupancy rate of flights
  3. 3.  Aircraft Seat Map

Lesson 1.4 · Reading time: ~8 min

Data types define what kind of values each column can store in a relational database. In this lesson, you will learn the most common SQL data types, when to use each of them, and how correct type choices improve data quality, storage efficiency, and query performance.

SQL Data Types Explained: INTEGER, VARCHAR, DATE and More

In the previous lesson, we covered tables, keys, constraints, and ACID. Now we move to a practical design decision you make for every table: choosing the right data type for each column.

Comparison of SQL numeric, text, and date/time data types used when designing table columns

Before diving into subtypes, here are the main SQL data type groups:

  • Numeric types: TINYINT, INT, BIGINT, DECIMAL, FLOAT
  • String types: CHAR, VARCHAR, TEXT
  • Date/time types: DATE, TIME, DATETIME, TIMESTAMP
  • Specialized types: BOOLEAN, BLOB, JSON

What Are Numeric Data Types in SQL?

Numeric data types store numbers, but not all numbers should be stored the same way. In practice, you choose between:

  • integer types for whole numbers,
  • exact decimal types for financial values,
  • floating-point types for approximate scientific values.

INTEGER Family

Integer types store whole numbers without decimal places.

TypeTypical sizeApproximate signed range
TINYINT1 byte-128 to 127
SMALLINT2 bytes-32,768 to 32,767
INTEGER / INT4 bytes-2,147,483,648 to 2,147,483,647
BIGINT8 bytes-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

The exact ranges can vary slightly by database and by signed/unsigned support.

DECIMAL / NUMERIC

DECIMAL stores exact values with fixed precision.

  • DECIMAL(p, s) means:
    • p = total number of digits,
    • s = digits after the decimal point.
  • Example: DECIMAL(10, 2) supports values up to 99,999,999.99.
  • Best for prices, invoices, taxes, and any value where accuracy is mandatory.

FLOAT / REAL / DOUBLE

Floating-point types store approximate values.

  • Good for scientific calculations and telemetry data.
  • Not ideal for currency because binary floating-point can introduce small rounding differences.
  • DOUBLE generally offers higher precision than FLOAT.

What Are String Data Types in SQL?

Text types differ mainly by length behavior and storage strategy.

CHAR

  • Fixed-length text.
  • CHAR(10) always reserves space for 10 characters.
  • If the value is shorter, many databases pad it with spaces.
  • Useful for fixed-size data like ISO country codes.

VARCHAR

  • Variable-length text with an upper limit.
  • VARCHAR(255) stores up to 255 characters but uses only the needed space.
  • Good default choice for names, emails, titles, and labels.

TEXT

  • Large variable-length text.
  • Useful for long descriptions, article bodies, comments, or logs.
  • Behavior and indexing limits can differ by database system.

What Are Date and Time Data Types?

Temporal types should be used for any value representing a date, a time, or an event timestamp.

DATE

Stores only year-month-day.

TIME

Stores only hour-minute-second.

DATETIME / TIMESTAMP

Stores both date and time.

TIMESTAMP may have timezone-aware behavior in some systems, while DATETIME is often timezone-neutral. Always verify your DBMS behavior before designing audit or event tables.

What Other Data Types Should You Know?

Many relational databases also support useful specialized types:

  • BOOLEAN: stores true/false values.
  • BLOB: stores binary content such as images or files.
  • JSON: stores semi-structured JSON documents and often supports JSON functions/indexes.

How to Choose the Right Data Type?

Use this practical checklist:

  • Choose the smallest type that safely covers expected values.
  • Use DECIMAL for money; avoid FLOAT for financial amounts.
  • Prefer VARCHAR over CHAR unless data length is truly fixed.
  • Store dates and times with temporal types, not strings.
  • Check DB-specific behavior for defaults, timezone handling, and indexing.

Choosing data types carefully at design time reduces future data migration work, application bugs, and performance regressions.

Key takeaways from this lesson:

  • Data types define what values a column can store and strongly affect data quality.
  • Numeric types serve different goals: whole numbers, exact decimals, and approximate floating-point values.
  • CHAR, VARCHAR, and TEXT should be chosen based on expected text length and storage behavior.
  • Date/time fields should use DATE, TIME, or TIMESTAMP instead of plain text.
  • Picking the correct type early helps avoid bugs, data cleanup, and performance issues later.

Frequently Asked Questions

What is the difference between DECIMAL and FLOAT?

DECIMAL stores exact values and is best for money. FLOAT stores approximate values and may introduce rounding differences, so it is better for scientific-style calculations.

Should I use CHAR or VARCHAR for names and emails?

In most cases, use VARCHAR because names and emails have variable length. CHAR is better for fixed-length values like country codes.

Is NULL a data type?

No. NULL means a missing or unknown value. It is not a separate data type but a special marker that can appear in columns, depending on constraints.

Interview Questions

How do you choose between INTEGER, BIGINT, and SMALLINT?

Estimate the expected value range and pick the smallest type that safely fits it. This improves storage efficiency while preventing overflow.

Why is DECIMAL preferred for currency values?

Because DECIMAL stores exact precision and avoids floating-point rounding errors that can cause financial inaccuracies.

What problems happen when data types are chosen poorly?

Typical issues include incorrect sorting/filtering, conversion errors, wasted storage, slower queries, and inconsistent application logic.

→ Lesson 1.5: Understanding NULL Values in SQL