SQL Table Creation: A First Look

Think of a table as a structured list — the kind you might keep on paper for something you track regularly. A teacher might have a class register: columns for Roll Number, Student Name, and Attendance, with each row being one student's record. That register is a table. SQL (Structured Query Language) is just the language we use to create and manage such tables inside a computer database.

The core idea is simple: a table organises data into rows (records) and columns (fields). Each column holds one type of information — names, dates, amounts — and every row is one complete entry. When you create a table, you are essentially drawing the empty grid and labelling each column with its name and the kind of data it will hold.

The CREATE TABLE Statement

To build a table, you use the CREATE TABLE command. It tells the database: "Make a new empty table with this name and these columns." The basic structure looks like this:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

You replace table_name with whatever you want to call your table (like Students or Orders). Inside the parentheses, you list each column, followed by its datatype — the kind of data that column will store.

Choosing Datatypes

Every column must have a datatype. This is not a formula or calculation; it is simply a rule that tells the database what sort of information goes in that column. Common datatypes include:

• INT — for whole numbers (like Roll Number or Age)
• VARCHAR(n) — for text of variable length up to n characters (like Name or Address)
• DATE — for dates (like Date of Birth)
• DECIMAL(p,s) — for numbers with decimal places (like Price or Salary)

A Simple Example

Suppose you want to create a table to store information about books in a library. You might write:

CREATE TABLE Books (
    BookID INT,
    Title VARCHAR(100),
    Author VARCHAR(50),
    PublishedYear INT,
    Price DECIMAL(6,2)
);

This creates a table called Books with five columns. BookID will hold whole numbers, Title and Author will hold text (up to 100 and 50 characters respectively), PublishedYear will hold whole numbers, and Price will hold numbers with up to two decimal places. The table is empty — it has no rows yet — but it is ready to accept data.

Why Table Creation Matters …

MySQL Data Types: Storing Information the Right Way

Think of a library. Every book has a specific place, and the librarian knows exactly what kind of item each shelf holds — novels go here, reference books there, magazines in a separate rack. If you shoved a dictionary into the magazine rack, it wouldn't fit, and finding it later would be a mess.

MySQL works the same way. When you create a table to store data, you must tell MySQL what kind of data each column will hold. This is called a data type. It's the rule that says: "This column will only ever contain whole numbers" or "This column will only ever hold short text." Getting this right from the start is what keeps your database organised, fast, and reliable.

Why Data Types Matter

Without data types, MySQL would have no idea how much space to reserve for each piece of information, or how to sort and compare values. If you stored a phone number as a number, MySQL might try to add it to another number — which makes no sense. If you stored a date as plain text, MySQL couldn't tell you "which records are from last month."

Every column in every table must have a data type. It's not optional. And choosing the right one is a skill you build with practice.

The Main Families of Data Types

MySQL offers many data types, but they fall into a few broad families. For a commerce or humanities student, these are the ones you'll use most often.

1. String (Text) Types

These store words, sentences, names, addresses, product descriptions — anything made of letters, digits, or symbols.

• CHAR — Fixed-length text. If you say CHAR(10), every entry takes exactly 10 characters, even if you only store "Hi". MySQL pads the rest with spaces. Use this when you know the length will always be the same, like a state code ("MH", "DL", "KA").
• VARCHAR — Variable-length text. If you say VARCHAR(100), an entry like "Mumbai" uses only 6 characters of space, not 100. This is the workhorse for names, addresses, email IDs, and most short-to-medium text.
• TEXT — For long paragraphs, like product reviews or article bodies. It can hold up to about 65,000 characters. There are larger variants (MEDIUMTEXT, LONGTEXT) for very long content.

2. Numeric Types

These store numbers — but not all numbers are the same.

• INT — Whole numbers (integers) from roughly -2 billion to +2 billion. Use this for quantities, IDs, years, counts. Most of the time, INT is what you need.
• DECIMAL — Numbers with decimal places, where exact precision matters. Think prices, tax rates, percentages. DECIMAL(10,2) means 10 digits total, with 2 after the decimal point — perfect for ₹1,234.56.
• FLOAT and DOUBLE — Approximate decimal numbers. Faster for scientific calculations, but can have tiny rounding errors. Avoid these for money.

3. Date and Time Types

These store dates, times, or both — and MySQL understands them natively, so you can ask questions like "find all orders from last week."

• DATE — Stores a date only: '2025-04-10'. No time.
• DATETIME — Stores date and time together: '2025-04-10 14:30:00'. This is the most common choice for timestamps of transactions, registrations, or log entries.
• TIMESTAMP — Similar to DATETIME, but with a smaller range and automatic timezone conversion. Often used for "last updated" fields.
• YEAR — Just a four-digit year. Useful for birth years or academic sessions.

4. The Boolean Type

MySQL doesn't have a true/false data type in the way you might expect. Instead, it uses BOOLEAN or BOOL, which is really just a tiny integer — 0 means false, 1 means true. You'll use this for yes/no fields like "is_active", "has_discount", or "verified".

How to Choose: A Simple Rule

Ask yourself three questions about every piece of data you want to store:

1. Is it text, a number, or a date? That tells you the family.
2. How long or how big can it be? That tells you the specific type within the family. …