Database Basics

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

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.

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:

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 .

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.

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.

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 :

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.

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 .

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:

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' ).

📘 Quick Reference

Syntax

Purpose

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.

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.

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.

🎉 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