Skip to main content
SQL Fundamentals
CHAPTER 05 Beginner

SQL Data Types Explained | INT, VARCHAR, DATE, BOOLEAN

Updated: May 16, 2026
15 min read

# CHAPTER 5

SQL Data Types Explained

1. Introduction

If you tell a human, "My age is 25," the human understands it. If you tell a database, "My age is twenty-five," the database will crash if the age column is mathematically configured to only accept whole numbers. In SQL, you must explicitly declare what kind of data lives in every single column. This strictness is exactly why databases are lightning-fast and structurally indestructible. In this chapter, we will master Data Types, ensuring our databases are optimized for both speed and storage.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Define strict data types for table columns.
  • Choose between numerical types (INT, FLOAT, DECIMAL).
  • Choose between text types (VARCHAR, TEXT).
  • Handle chronological data (DATE, DATETIME).
  • Implement logical true/false constraints (BOOLEAN).

3. Numeric Data Types

Numbers are divided into two categories: Whole numbers and Decimal numbers.
  • INT (Integer): Used for whole numbers (e.g., 1, 500, -42). Perfect for IDs, quantities, or age.
*Example:* quantity INT
  • FLOAT / DOUBLE: Used for approximate fractional numbers (e.g., 3.14159). Great for scientific measurements, but terrible for money due to microscopic rounding errors.
  • DECIMAL(M, D): Used for *exact* fractional numbers. Mandatory for financial applications!
*Example:* price DECIMAL(10, 2) means the number can have 10 digits total, with 2 digits after the decimal point (e.g., 99999999.99).

4. Text (String) Data Types

If you are storing words, sentences, or paragraphs, you use text types.
  • VARCHAR(N) (Variable Character): The most common text type. Used for short text like names, emails, or passwords. You must specify the maximum length (N).
*Example:* email VARCHAR(255) means it can hold up to 255 characters.
  • CHAR(N) (Fixed Character): Used when the text is *always* the exact same length, like a 2-letter state abbreviation (TX, NY). It is slightly faster than VARCHAR.
*Example:* statecode CHAR(2)
  • TEXT: Used for massive blocks of text with no strict length limit. Perfect for blog post bodies or product descriptions.
*Example:* blogcontent TEXT

5. Date and Time Data Types

Never store a date as a VARCHAR string like "Jan 5th 2023"! If you do, the database cannot mathematically sort the dates. You must use native chronological types.
  • DATE: Stores just the date (YYYY-MM-DD).
*Example:* birthdate DATE (e.g., 1990-12-25)
  • DATETIME / TIMESTAMP: Stores both the date and the exact time (YYYY-MM-DD HH:MM:SS). Perfect for tracking when an order was placed.
*Example:* createdat DATETIME (e.g., 2023-10-31 14:30:00)

6. Logical Data Types

  • BOOLEAN (or TINYINT(1) in MySQL): Used for True/False or Yes/No flags. Behind the scenes, the database actually stores it as a 1 (True) or a 0 (False).
*Example:* is_active BOOLEAN

7. Mini Project: Designing an Optimized Table

Let's design a perfectly optimized users table for a web application.
sql
12345678910111213141516171819 
CREATE TABLE users (
    -- An Integer is perfect for a mathematically ascending ID
    id INT, 
    
    -- VARCHAR saves hard drive space if the name is short
    username VARCHAR(50), 
    
    -- A user's bio might be long, so we use TEXT
    biography TEXT, 
    
    -- Exact chronological math is required for birth dates
    date_of_birth DATE, 
    
    -- 1 (True) or 0 (False) to track if they verified their email
    is_verified BOOLEAN, 
    
    -- Exact decimal precision for their account balance (No floating point errors!)
    wallet_balance DECIMAL(10, 2) 
);

8. Common Mistakes

  • Using VARCHAR for Numbers: If you create a phonenumber VARCHAR(15) column, that is actually correct! You don't do math on phone numbers, and they often contain dashes (555-1234). But if you create a price VARCHAR(10) column, you can never ask the database "Find all products under $50", because strings cannot be mathematically compared!
  • Using INT for massive numbers: A standard INT caps out around 2.1 Billion. If you are tracking the national debt, you must use a BIGINT.

9. Best Practices

  • Minimize VARCHAR sizes: If you know a ZIP code is never longer than 10 characters, don't use VARCHAR(255). Use VARCHAR(10). This saves massive amounts of server RAM when querying millions of rows.

10. Exercises

  1. 1. What data type should you use to store a user's short email address?
  1. 2. Why is it architecturally incorrect to store financial currency in a FLOAT column, and what should you use instead?

11. SQL Challenges

Write the SQL DDL command to create a movies table. It should have movieid (Whole Number), title (Short Text up to 150 chars), release_date (Chronological Date), and rating (Exact Decimal with 1 decimal place, e.g., 8.5).
sql
123456 
CREATE TABLE movies (
    movie_id INT,
    title VARCHAR(150),
    release_date DATE,
    rating DECIMAL(3, 1)
);

12. MCQ Quiz with Answers

Question 1

In a Relational Database, why is it mandatory to assign a strict Data Type to every column?

Question 2

If you are building a banking application, which data type MUST be used for the accountbalance column to prevent microscopic rounding errors?

13. Interview Questions

  • Q: Compare and contrast VARCHAR and CHAR. When would it be architecturally advantageous to use CHAR(2) instead of VARCHAR(255)?
  • Q: A junior developer creates a table with createdat VARCHAR(50) to store timestamps. Explain the severe technical limitations this will cause for the backend reporting team.

14. FAQs

Q: Can a table have multiple columns of the same data type? A: Absolutely! You can have firstname VARCHAR(50), lastname VARCHAR(50), and city VARCHAR(50) all in the exact same table.

15. Summary

The structure of your database is now perfect. By explicitly defining INT for mathematical IDs, VARCHAR and TEXT for strings, DATE for chronology, and DECIMAL for precision finance, you guarantee that corrupted or invalid data can never enter your system.

16. Next Chapter Recommendation

Our digital filing cabinet is fully built and properly labeled. But right now, the cabinet is completely empty! In Chapter 6: INSERT Queries and Adding Data, we will execute our first Data Manipulation (DML) commands to populate the database with real information.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Create SQL Databases and Tables | DDL Commands
Next Chapter SQL INSERT INTO | Add Data to Relational Databases

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6