SQL Data Types: A Practical Reference for Beginners

Every column in a SQL table has a data type that determines what kind of values it can hold. Pick the wrong type and you'll get silent bugs — prices stored as text that can't be summed, dates that won't sort properly, or integers that overflow. Understanding data types is one of the first things that separates someone who writes SQL from someone who writes correct SQL.

In this guide you'll learn the most common SQL data types through real examples from the Chinook music-store database, see how types affect query behavior, and practice with hands-on exercises.

Table of Contents

• Why Data Types Matter
• Numeric Types: INTEGER and NUMERIC
• Text Types: VARCHAR and NVARCHAR
• Date and Time Types: DATETIME
• NULL — The Absence of a Value
• Type Conversion with CAST and TYPEOF
• Cheat Sheet
• Common Mistakes to Avoid
• Practice Makes Perfect
• Summary
• Frequently Asked Questions

Why Data Types Matter

Consider two columns in the Chinook database: Track.Milliseconds (INTEGER) and Invoice.Total (NUMERIC). Both store numbers, but they serve very different purposes. The integer tracks whole milliseconds of audio. The numeric type preserves exact decimal precision for financial amounts.

If you accidentally stored a price as an integer, $9.99 would become $9 — and your revenue reports would be wrong. If you stored a date as plain text, sorting by date would give you alphabetical order ('10-Jan' before '9-Feb') instead of chronological order.

Data types enforce correctness at the database level, before your queries even run.

Numeric Types: INTEGER and NUMERIC

INTEGER

The INTEGER type stores whole numbers without decimals. In the Chinook database, every primary key and foreign key uses INTEGER — TrackId, AlbumId, CustomerId, and so on.

SELECT
  TrackId,
  Name,
  Milliseconds,
  Bytes
FROM Track
ORDER BY Milliseconds DESC
LIMIT 5;

Notice that Milliseconds and Bytes are both integers — they represent discrete, countable quantities. You can perform arithmetic directly on them:

SELECT
  Name,
  Milliseconds / 1000 AS Seconds,
  Milliseconds / 60000 AS Minutes
FROM Track
ORDER BY Milliseconds DESC
LIMIT 5;

Key detail: Integer division in SQL truncates — 5286953 / 1000 gives 5286, not 5286.953. This is important when you need precise results.

Try it yourself — Show Track Names and Composers →

NUMERIC (Decimal)

The NUMERIC(10,2) type stores exact decimal numbers. The Chinook database uses it for monetary values like Invoice.Total, InvoiceLine.UnitPrice, and Track.UnitPrice.

SELECT
  InvoiceId,
  BillingCountry,
  Total
FROM Invoice
ORDER BY Total DESC
LIMIT 5;

NUMERIC preserves the decimal precision you need for financial calculations. When you aggregate these values with SUM or AVG, the result stays precise:

SELECT
  BillingCountry,
  COUNT(*) AS InvoiceCount,
  ROUND(SUM(Total), 2) AS TotalRevenue,
  ROUND(AVG(Total), 2) AS AvgInvoice
FROM Invoice
GROUP BY BillingCountry
ORDER BY TotalRevenue DESC
LIMIT 5;

Try it yourself — Display Invoice Dates and Totals →

Text Types: VARCHAR and NVARCHAR

VARCHAR and NVARCHAR

VARCHAR(n) and NVARCHAR(n) store variable-length text up to n characters. NVARCHAR supports Unicode (international characters like accented letters, Asian scripts, etc.), while VARCHAR traditionally stores ASCII only. The Chinook database uses NVARCHAR throughout since it contains artist names from around the world.

Here's how different columns use different lengths based on expected content:

SELECT
  FirstName,
  LastName,
  Email,
  LENGTH(Email) AS EmailLength
FROM Customer
ORDER BY LENGTH(Email) DESC
LIMIT 5;

The LENGTH() function works on text columns and returns the number of characters — useful for validation and analysis.

VARCHAR vs TEXT: Many databases also offer a TEXT type with no length limit. Use VARCHAR when you want to enforce a maximum length (like a 10-character postal code). Use TEXT for free-form content with no practical limit.

Try it yourself — Find Customer Contact Emails →

Date and Time Types: DATETIME

The DATETIME type stores both a date and a time in a single column. In the Chinook database, Employee.BirthDate, Employee.HireDate, and Invoice.InvoiceDate all use DATETIME.

SELECT
  FirstName,
  LastName,
  HireDate,
  BirthDate
FROM Employee
ORDER BY HireDate
LIMIT 5;

The real power of DATETIME is that SQL provides functions to extract parts and perform date arithmetic:

SELECT
  FirstName || ' ' || LastName AS Employee,
  strftime('%Y', HireDate) AS HireYear,
  strftime('%m', HireDate) AS HireMonth
FROM Employee
ORDER BY HireDate
LIMIT 5;

Date functions like strftime() are essential for time-based analysis. You'll use them constantly when grouping data by time periods.

Try it yourself — Invoices from Early 2021 →

NULL — The Absence of a Value

NULL is not a data type itself, but every data type can hold a NULL value unless the column is defined as NOT NULL. NULL means "unknown" or "not applicable" — it is not the same as zero, an empty string, or false.

In the Chinook database, the Track.Composer column is NVARCHAR(220) and allows NULL because not every track has a known composer:

SELECT
  Name,
  Composer,
  CASE
    WHEN Composer IS NULL THEN 'Unknown'
    ELSE Composer
  END AS DisplayComposer
FROM Track
WHERE AlbumId = 1
LIMIT 5;

Critical NULL rules:

• NULL = NULL is not true — use IS NULL or IS NOT NULL
• NULL in arithmetic produces NULL: 5 + NULL = NULL
• Aggregate functions like COUNT(column) skip NULLs; COUNT(*) counts all rows
• Use COALESCE(column, default) to replace NULLs with a fallback value

SELECT
  COUNT(*) AS TotalTracks,
  COUNT(Composer) AS TracksWithComposer,
  COUNT(*) - COUNT(Composer) AS TracksWithoutComposer
FROM Track;

Try it yourself — Find Unique Track Composers →

Type Conversion with CAST and TYPEOF

Sometimes you need to convert between types — concatenating a number with text, or treating a text date as an actual date. Use CAST() for explicit conversion:

SELECT
  Name,
  Milliseconds,
  CAST(Milliseconds AS REAL) / 60000.0 AS ExactMinutes,
  TYPEOF(Milliseconds) AS OriginalType,
  TYPEOF(CAST(Milliseconds AS REAL)) AS CastType
FROM Track
ORDER BY Milliseconds DESC
LIMIT 5;

By casting to REAL before dividing, we get precise decimal minutes instead of truncated integers. The TYPEOF() function (SQLite-specific) shows you what type a value actually is — handy for debugging.

Common CAST patterns:

-- Number to text for concatenation
SELECT 'Track #' || CAST(TrackId AS TEXT) FROM Track LIMIT 1;

-- Round a decimal to an integer
SELECT CAST(ROUND(Total) AS INTEGER) FROM Invoice LIMIT 1;

Try it yourself — List Music Media Types →

Cheat Sheet

Common Mistakes to Avoid

1. Comparing NULLs with = instead of IS NULL

This is the most common data type trap. NULL is not a value — it's the absence of one. Equality comparisons with NULL always return NULL (which is falsy).

-- Wrong: this returns NO rows, even when Composer is NULL
SELECT Name FROM Track WHERE Composer = NULL;

-- Right: use IS NULL
SELECT Name FROM Track WHERE Composer IS NULL;

2. Integer division when you need decimals

When both operands are integers, SQL performs integer division and truncates the result. This silently drops precision.

-- Wrong: gives 5 instead of 5.28
SELECT 5286953 / 1000000;

-- Right: make one operand a decimal
SELECT 5286953 / 1000000.0;
-- Or cast explicitly
SELECT CAST(5286953 AS REAL) / 1000000;

3. Storing dates as plain text

If you store dates as VARCHAR (e.g., 'March 23, 2026'), you lose the ability to sort chronologically, perform date arithmetic, or extract parts with strftime(). Always use a proper date type.

-- Text dates sort alphabetically (wrong)
-- 'April' comes before 'March' regardless of year

-- DATETIME sorts chronologically (correct)
SELECT InvoiceDate FROM Invoice ORDER BY InvoiceDate LIMIT 3;

4. Using REAL for money

Floating-point types (REAL, FLOAT, DOUBLE) are approximate. They can't represent 0.10 exactly due to binary representation. Use NUMERIC or DECIMAL for any financial data.

-- Dangerous: REAL might give 19.989999999... instead of 19.99
-- Safe: NUMERIC(10,2) stores 19.99 exactly
SELECT TYPEOF(Total), Total FROM Invoice LIMIT 1;

5. Forgetting that VARCHAR length limits vary by database

In SQLite, VARCHAR(10) is actually treated as TEXT with no enforced limit. In PostgreSQL or SQL Server, inserting 50 characters into a VARCHAR(10) column will raise an error. Always test on your target database.

Practice Makes Perfect

Strengthen your data type skills with these Chinook exercises:

Join 500+ SQL learners already practicing on SQiLs — Browse all exercises →

Summary

• INTEGER is for whole numbers — IDs, counts, durations. Watch out for integer division truncating decimals.
• NUMERIC / DECIMAL preserves exact decimal precision — always use it for money and financial data.
• VARCHAR / NVARCHAR stores text with a maximum length. Use NVARCHAR for international characters.
• DATETIME stores dates and times. Use date functions like strftime() to extract parts and group by time periods.
• NULL is the absence of a value. Use IS NULL, not = NULL, and be aware of how aggregates handle NULLs.
• CAST() converts between types when needed — especially useful for avoiding integer division.
• Once you're comfortable with types, explore JOINs to combine data from multiple tables, or subqueries vs JOINs to decide which approach fits your query.

Ready to put this into practice? Start solving exercises on SQiLs →

Frequently Asked Questions

What is the difference between VARCHAR and TEXT in SQL?

VARCHAR(n) enforces a maximum length of n characters — the database will reject any value longer than that limit (in most databases other than SQLite). TEXT has no declared length limit and can store arbitrarily long strings. Use VARCHAR when you want to enforce a constraint (like postal codes or country codes), and TEXT for free-form content like descriptions or blog posts.

Should I use FLOAT or NUMERIC for prices?

Always use NUMERIC (also called DECIMAL) for prices and financial data. FLOAT and REAL are approximate types that use binary floating-point representation, which cannot represent values like 0.10 exactly. Over many calculations, these tiny errors compound and your totals will drift. NUMERIC stores exact decimal values, which is essential for money.

How do I check the data type of a column in SQL?

In SQLite, use TYPEOF(column_name) in a SELECT to inspect the storage type of a value at runtime. In PostgreSQL, query information_schema.columns. In MySQL, use DESCRIBE table_name. In SQL Server, use sp_columns table_name. Each database has its own way, but the concept is the same — check the schema metadata.

What happens when I insert the wrong data type?

It depends on the database. SQLite is very lenient — it uses "type affinity" and will often accept mismatched types (storing text in an integer column, for example). PostgreSQL and SQL Server are strict and will raise an error if you try to insert a string into an integer column. This is why understanding your database's type system matters — SQLite's flexibility can hide bugs that would surface immediately in stricter databases.