Lesson 2 • Beginner Track
Database Basics & Tables
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.
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.
📊 Real-world analogy
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 students
Define the columns of a new, empty table.
-- 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.
...After running it, the table's structure looks like this (still empty — zero rows of data):
Result — structure of students (0 rows):
| Column | Type | Constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY |
| name | TEXT | NOT NULL |
| age | INTEGER | — |
| gpa | REAL | — |
| enrolled | BOOLEAN | DEFAULT 1 |
| join_date | DATE | — |
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:
| Type | Holds | Example value |
|---|---|---|
| INTEGER | Whole numbers (no decimals) | 42 |
| TEXT / VARCHAR(n) | Text; VARCHAR caps the length at n | 'Alice' |
| REAL / DECIMAL(p,s) | Decimal numbers | 9.50 |
| BOOLEAN | True / false (stored as 1 / 0) | 1 |
| DATE | A calendar date | '2024-09-01' |
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.
DECIMAL (or REAL) for prices, never INTEGER — you'd lose the pennies. And use a real DATE type for dates, not TEXT, so the database can sort and compare them properly.Data types in action
Each column gets the type that fits its data.
-- 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 D
...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: add a column
Replace the ___ blanks with a column name and a data type.
-- 🎯 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.
PRIMARY KEY usually fills itself in — AUTO_INCREMENT in MySQL, SERIAL in PostgreSQL, or just an INTEGER PRIMARY KEY in SQLite — so you never pick ids by hand.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 three students
Add multiple rows in a single statement.
-- 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:
Result — 3 rows:
| id | name | age | gpa | enrolled | join_date |
|---|---|---|---|---|---|
| 1 | Alice Johnson | 20 | 3.8 | 1 | 2024-09-01 |
| 2 | Bob Smith | 22 | 3.2 | 1 | 2023-09-01 |
| 3 | Carol White | 19 | 3.95 | 0 | 2024-09-01 |
You can also leave out optional columns. Anything with a DEFAULT gets its default, and anything that allows it becomes NULL:
INSERT with defaults
Omit columns to use DEFAULT or 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").Result — the new row (4):
| id | name | age | gpa | enrolled | join_date |
|---|---|---|---|---|---|
| 4 | Dan Brown | NULL | NULL | 1 | NULL |
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: INSERT a book
Replace the ___ blanks with a title and a price.
-- 🎯 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".
NULL means "unknown", you can't test it with =. In later lessons you'll filter for missing data with IS NULL and IS NOT NULL instead of = NULL.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.
Build & fill an orders table
CREATE TABLE then INSERT, the real two-step flow.
-- 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 DAT
...Result — orders — 3 rows:
| order_id | customer | total | paid | order_date |
|---|---|---|---|---|
| 1 | Alice Johnson | 49.99 | 1 | 2024-05-01 |
| 2 | Bob Smith | 12.5 | 0 | 2024-05-02 |
| 3 | Carol White | 88 | 1 | 2024-05-02 |
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:
Constraints rejecting bad data
See a datatype mismatch and a NOT NULL violation in action.
-- 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,
...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 —ageisINTEGER, so write20. - No
PRIMARY KEY: the table still works, but rows have nothing to uniquely identify them, which breaks later lessons (joins, updates). Always addid INTEGER PRIMARY KEY. - "NOT NULL constraint failed: students.name": you tried to insert a row without a value for a
NOT NULLcolumn. Provide a value forname, or remove theNOT NULLrule. - "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').
📘 Quick Reference
| Syntax | Purpose |
|---|---|
| CREATE TABLE t ( ... ) | Build a new, empty table |
| col INTEGER | Whole-number column |
| col TEXT / VARCHAR(n) | Text column |
| col REAL / DECIMAL(p,s) | Decimal-number column |
| col BOOLEAN | True/false column (1 / 0) |
| col DATE | Calendar-date column |
| PRIMARY KEY | Unique id for each row |
| NOT NULL | Column can't be empty |
| DEFAULT value | Auto-fill when omitted |
| INSERT INTO t (cols) VALUES (...) | Add one or more rows |
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
CREATE a movies table, then INSERT 2-3 rows.
-- 🎯 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 'movi
...🎉 Lesson Complete
- ✅
CREATE TABLEdesigns an empty table — columns first, data later - ✅ Each column gets a data type:
INTEGER,TEXT,REAL,BOOLEAN,DATE - ✅
PRIMARY KEYuniquely identifies each row; every table needs one - ✅
NOT NULLforbids blanks;DEFAULTauto-fills a value;NULLmeans "no value yet" - ✅
INSERT INTO ... VALUES (...)adds rows — quote text, match the value count - ✅ Next: the
SELECTstatement — pull the data back out of your tables
Sign up for free to track which lessons you've completed and get learning reminders.