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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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):

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 .

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.

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.

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 :

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

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".

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:

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary