Database Basics & Tables

Reviewed & published by Brayan K

By the end of this lesson you'll be able to design a table from scratch — choosing a column for each piece of data, giving each column the right data type, protecting it with constraints, and filling it with real rows using INSERT INTO. This is the foundation everything else in SQL is built on: before you can query data, the data has to live in a well-built table.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Create a table with CREATE TABLE
• Pick the right data type for each column
• Give every table a PRIMARY KEY
• Protect data with NOT NULL and DEFAULT
• Add rows of data with INSERT INTO
• Read a table's structure and its rows

What Is a Table?

A table stores one kind of thing — students, products, orders. Its columns are the fields every record has (a student's name, age, GPA), and each row is one record (one actual student). A data type is the rule for what a column can hold: numbers, text, dates, and so on.

Think of a table as a grid. The columns run across the top as headings; the rows stack underneath, one per record. Building a database is mostly about choosing good columns and giving each one the right type — get that right and the rest of SQL becomes easy.

Building a table is exactly like setting up a spreadsheet before you type any data. First you label the columns across the top — Name, Age, GPA — and decide what each one holds (Age is a number, Name is text). CREATE TABLE is that planning step. Only afterwards do you type rows of data in underneath, which is what INSERT INTO does. The big difference from a spreadsheet: SQL enforces the column rules, so it won't let you type "twenty" into a number column by accident.

1. Designing a Table with CREATE TABLE

The CREATE TABLE statement builds the empty shell. You give the table a name, then list its columns inside parentheses, separated by commas. Each column needs a name and a data type, and can have optional constraints (rules) like PRIMARY KEY or NOT NULL.

The example below creates a students table. Read the comments — every column explains what it holds. Notice that running it creates the structure but adds no rows; you've drawn the empty grid, not filled it in.

-- CREATE TABLE defines a brand-new, empty table.
-- You name the table, then list its columns inside ( ).
-- Each line reads:  column_name  DATA_TYPE  [optional constraints]

CREATE TABLE students (
    id         INTEGER  PRIMARY KEY,   -- unique row id, no duplicates allowed
    name       TEXT     NOT NULL,      -- text that must always be filled in
    age        INTEGER,                -- a whole number (may be left empty)
    gpa        REAL,                   -- a decimal number, e.g. 3.75
    enrolled   BOOLEAN  DEFAULT 1,     -- 1 = true; defaults to 1 if omitted
    join_date  DATE                    -- a calendar date like 2024-09-01
);

-- This runs without error but adds ZERO rows.
-- You've designed the columns; the table is still empty.

After running it, the table's structure looks like this (still empty — zero rows of data):

2. Common Data Types

The data type is a promise about what lives in a column. Pick the right one and the database stores it efficiently, sorts it correctly, and rejects bad values for you. Here are the types you'll use 95% of the time:

Choosing a type is mostly common sense: a count or an id is an INTEGER, anything you'd write in a sentence is TEXT, a measurement or price is REAL/DECIMAL, a yes/no flag is BOOLEAN, and a day on the calendar is a DATE.

-- The data type tells SQL what KIND of value a column holds.
-- Put the wrong kind in and the database rejects it — that's a feature,
-- not a bug. It stops a typo from quietly corrupting your data.

CREATE TABLE products (
    id        INTEGER  PRIMARY KEY,  -- whole numbers: 1, 2, 3, ...
    name      TEXT     NOT NULL,     -- any text: 'Coffee Mug'
    price     REAL,                  -- decimals: 9.50, 24.99
    in_stock  BOOLEAN,               -- true/false (stored as 1/0)
    added_on  DATE                   -- 2024-03-15
);

-- TEXT and VARCHAR(n) both store text. VARCHAR(100) just caps the
-- length at 100 characters. REAL and DECIMAL both store decimals.

Your Turn: finish the table

Complete the books table by adding one more column. Fill in a column name and its data type. The expected result is in the comments so you can check yourself.

-- 🎯 YOUR TURN — fill in the two blanks, then press "Try it Yourself"
-- Goal: finish the 'books' table by adding a column for the price.

CREATE TABLE books (
    id      INTEGER  PRIMARY KEY,
    title   TEXT     NOT NULL,
    ___     ___                    -- 👉 add a column named "price" of type REAL
);

-- ✅ Expected: the table is created with 3 columns (id, title, price)
--    and contains 0 rows. A 'price' that stores 12.99 needs type REAL.

3. Constraints — Rules That Protect Your Data

Constraints are guardrails the database enforces for you. They turn "please be careful" into a rule that simply cannot be broken. These three are the essentials:

PRIMARY KEY

Marks the column that uniquely identifies each row — usually an id. No two rows can share the same value, and it can never be empty. Every table should have exactly one.

NOT NULL

This column must always have a value. NULL means "no value at all" — different from 0 or an empty string. NOT NULL forbids leaving the field blank, so it's perfect for fields a record can't exist without, like a name.

DEFAULT

Supplies a value automatically when you don't provide one. enrolled BOOLEAN DEFAULT 1 means new students are marked enrolled unless you say otherwise — less typing, fewer mistakes.

4. Adding Rows with INSERT INTO

Now that the table exists, INSERT INTO puts data in it. You name the columns you're filling, then give a VALUES (...) list for each row. The values must line up with the columns in the same order, and one INSERT can add many rows at once — just separate each (...) group with a comma.

Two rules trip up almost every beginner: text goes in 'single quotes' (numbers don't), and the number of values must match the number of columns you listed.

-- INSERT INTO adds rows of actual data to a table.
-- List the columns, then give a matching VALUES (...) list for each row.

INSERT INTO students (id, name, age, gpa, enrolled, join_date)
VALUES
    (1, 'Alice Johnson', 20, 3.80, 1, '2024-09-01'),
    (2, 'Bob Smith',     22, 3.20, 1, '2023-09-01'),
    (3, 'Carol White',   19, 3.95, 0, '2024-09-01');

-- Text goes in 'single quotes'. Numbers do not.
-- The order of the VALUES must match the order of the columns above.

After the insert, the students table finally holds data:

You can also leave out optional columns. Anything with a DEFAULT gets its default, and anything that allows it becomes NULL:

-- You can leave out columns that are optional or have a DEFAULT.
-- 'enrolled' has DEFAULT 1, and 'age'/'gpa'/'join_date' allow NULL,
-- so naming just id and name is perfectly valid:

INSERT INTO students (id, name)
VALUES (4, 'Dan Brown');

-- The result: id = 4, name = 'Dan Brown', enrolled = 1 (the default),
-- and age, gpa, join_date are NULL (empty — "no value yet").

Your Turn: insert a row

Add one book to the books table. Fill in a title (remember the quotes) and a price. The expected result is in the comments.

-- 🎯 YOUR TURN — fill in the blanks to add ONE book to the table.
-- The 'books' table has columns: id, title, price.

INSERT INTO books (id, title, price)
VALUES (1, ___, ___);    -- 👉 a title in 'quotes', then a price like 12.99

-- ✅ Expected result: 1 row added —
--    id = 1, title = (your text), price = (your number)
-- Remember: text needs 'single quotes', numbers do not.

5. NULL — the "No Value Yet" Value

When a column has no data, SQL stores NULL. This is the single most misunderstood idea for beginners, so it's worth pinning down now: NULL is not zero, and it is not an empty string ''. It means "unknown" or "not entered yet".

A student with gpa = 0.0 has a real GPA of zero. A student with gpa = NULL simply hasn't been graded. Those are different facts, and SQL keeps them apart on purpose. The NOT NULL constraint from Section 3 is how you say "this field is never allowed to be unknown".

6. Putting It Together: Build a Table, Then Fill It

Real work is just these two steps back to back: CREATE TABLE to design the structure, then INSERT INTO to load the rows. Here's a complete orders example you can read top to bottom — notice paid uses DEFAULT 0 and customer is NOT NULL.

-- A complete, realistic build: design a table AND fill it,
-- the exact two-step flow you'll use on every real project.

-- Step 1 — design the structure (columns + types + constraints)
CREATE TABLE orders (
    order_id   INTEGER  PRIMARY KEY,   -- unique id for each order
    customer   TEXT     NOT NULL,      -- who placed it (required)
    total      DECIMAL,                -- order total, e.g. 49.99
    paid       BOOLEAN  DEFAULT 0,     -- 0 = not paid yet (the default)
    order_date DATE                    -- when it was placed
);

-- Step 2 — fill it with rows
INSERT INTO orders (order_id, customer, total, paid, order_date)
VALUES
    (1, 'Alice Johnson', 49.99, 1, '2024-05-01'),
    (2, 'Bob Smith',     12.50, 0, '2024-05-02'),
    (3, 'Carol White',   88.00, 1, '2024-05-02');

-- ✅ Result: an 'orders' table with 5 columns and 3 rows of real data.

Finally, watch the guardrails work. The statement below fails on purpose — the database rejects a wrong type and a missing NOT NULL value rather than store bad data. Run it, read the errors, then fix them:

-- Watch a constraint do its job. Try to run this and the
-- database REFUSES it — it protects the table from bad data.

-- 1) Wrong data type: 'twenty' is text, but age is INTEGER
INSERT INTO students (id, name, age)
VALUES (5, 'Eve Adams', 'twenty');     -- ❌ datatype mismatch

-- 2) Missing a NOT NULL value: every student needs a name
INSERT INTO students (id, age)
VALUES (6, 30);                         -- ❌ NOT NULL constraint failed: students.name

-- The fixes: write age as the number 20, and always supply a name.
-- Nothing is inserted until BOTH problems are corrected.

Common Errors (and the fix)

• "datatype mismatch": you put text where a number belongs, like age = 'twenty'. Match the value to the column's type — age is INTEGER, so write 20.
• No PRIMARY KEY: the table still works, but rows have nothing to uniquely identify them, which breaks later lessons (joins, updates). Always add id INTEGER PRIMARY KEY.
• "NOT NULL constraint failed: students.name": you tried to insert a row without a value for a NOT NULL column. Provide a value for name, or remove the NOT NULL rule.
• "table students has N columns but M values were supplied": your VALUES (...) count doesn't match the columns you listed. Count both — they must be equal.
• Quotes mix-up: text values need 'single quotes' ('Alice'); numbers must not be quoted (20, not '20').

Frequently Asked Questions

Q: What's the difference between NULL and 0 or an empty string?

NULL means "no value at all — unknown or not entered". 0 is a real number and '' is a real (empty) piece of text. A missing phone number is NULL; a balance of zero is 0.

Q: Do I have to list the columns in INSERT INTO?

You can skip the column list and write INSERT INTO students VALUES (...), but then you must supply every column in exact table order. Listing the columns is clearer and safer — always do it while learning.

Q: TEXT or VARCHAR? REAL or DECIMAL?

They're close cousins. VARCHAR(n) is text with a maximum length; TEXT has no fixed limit. DECIMAL(p,s) stores decimals with exact precision (best for money); REAL is a simpler floating-point decimal. Either pairing works for these lessons.

Q: Why must every table have a PRIMARY KEY?

It gives each row a guaranteed-unique handle. Without it you can't reliably update one specific row or link tables together — both of which you'll do in later lessons.

Mini-Challenge: Build a Movies Table

Put it all together — a brief, a blank canvas, and the expected result in the comments. Design the table, insert a few rows, then copy it into a playground to confirm it runs cleanly.

-- 🎯 MINI-CHALLENGE: design a table and fill it with data
-- Using ONLY what this lesson covered (CREATE TABLE, data types,
-- PRIMARY KEY, NOT NULL, INSERT INTO):
--
--   1. Create a table called "movies" with these columns:
--        movie_id   INTEGER, and make it the PRIMARY KEY
--        title      TEXT, and it must be NOT NULL
--        year       INTEGER
--        rating     REAL        (e.g. 8.5)
--
--   2. INSERT 2 or 3 of your favourite movies into the table.
--
-- ✅ Expected: a 'movies' table with 4 columns and the rows you added,
--    e.g.  1 | The Matrix | 1999 | 8.7
--          2 | Inception  | 2010 | 8.8

-- your CREATE TABLE statement here


-- your INSERT INTO statement here

🎉 Lesson Complete

• ✅ CREATE TABLE designs an empty table — columns first, data later
• ✅ Each column gets a data type: INTEGER, TEXT, REAL, BOOLEAN, DATE
• ✅ PRIMARY KEY uniquely identifies each row; every table needs one
• ✅ NOT NULL forbids blanks; DEFAULT auto-fills a value; NULL means "no value yet"
• ✅ INSERT INTO ... VALUES (...) adds rows — quote text, match the value count
• ✅ Next: the SELECT statement — pull the data back out of your tables

Practice quiz

Continue this course

• Previous: Introduction to SQL
• Next: SELECT Statement — Retrieve data from tables with SELECT — the most important SQL statement
• Quick reference: SQL cheat sheet › Modifying Data