HexLibraryHexLibrary
</> HexCodeSign inSign up
PythonJavaScriptTypeScriptCC++JavaPHPGoRustRubyBashSQLLuaPerlCOBOLPascalDelphiFortranDartSwiftC#JuliaGroovyRElixirHaskellKotlinScalaClassic ASPClipperMS-DOS
LESSONS
Introduction to SQL (SELECT)Tables (CREATE & INSERT)Filtering (WHERE)Sorting & LimitingAggregate FunctionsUpdate & DeleteJoins (JOIN)
REFERENCE
📖 Overview
🔎 Querying (SELECT)
🗃️ Creating a Table
✏️ Inserting & Modifying Data
🧲 Filtering (WHERE)
🔢 Sorting & Limiting
📊 Aggregate Functions
🔗 Joins (JOIN)
SQL›Tables (CREATE & INSERT)

Tables (CREATE & INSERT)

Defining tables with CREATE TABLE and inserting data with INSERT.

Simple enough for a 5-year-old to understand

Create table and insert data

Example code
1
CREATE TABLE item (id INTEGER, name TEXT, price REAL);
Columns and their types.
DatabaseColumns and their types.
2
INSERT INTO item VALUES (1, 'Pencil', 5.5);
Insert a row.
DatabaseInsert a row.
3
INSERT INTO item VALUES (2, 'Notebook', 12.0);
Another row.
DatabaseAnother row.
4
SELECT COUNT(*) FROM item;
Row count: 2
Output / InputRow count: 2
DatabaseOutput / Input
💡 Hint: Common types: INTEGER (integer), TEXT (text), REAL (decimal).

About this topic

A table is where data lives: rows hold records, and columns hold the fields of those records. Designing a table means deciding what information to store in what type and what rules apply. Since these decisions are expensive to change later, they are worth thinking about from the start.

Every table should have a primary key: a column that uniquely identifies each row. Usually, an auto-incrementing ID number is used. Without a primary key, there is nothing to prevent you from inserting the same record twice or updating the correct row.

What you will learn

  • Creating a table with CREATE TABLE
  • Basic data types: integer, text, decimal, date
  • Defining a primary key
  • NOT NULL and UNIQUE constraints
  • Inserting rows with INSERT
  • Setting default values

When to use?

Table design is a project's most permanent decision: application code changes easily, but altering the structure of a table with millions of rows requires a careful migration plan.

It is important to enforce constraints at the database level. Checks made in application code can be skipped or disabled due to a bug; a database constraint, however, never accepts corrupted data under any condition.

Common mistakes

Wrong

CREATE TABLE users (
  name TEXT,
  email TEXT
);

Since no primary key is defined, there is no way to uniquely identify the rows. The same person can be inserted twice, and updating the correct row becomes impossible.

Correct

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

Wrong

CREATE TABLE products (
  price TEXT
);

Storing price as text breaks sorting and aggregation operations: in text sorting, the value 100 comes before 20, and aggregation cannot be performed. Numeric data must be stored in a numeric type.

Correct

CREATE TABLE products (
  price DECIMAL(10, 2) NOT NULL
);

Wrong

INSERT INTO users VALUES ('Ada', '[email protected]');

When column names are omitted, the order depends on the column order in the table; the day a new column is added to the table, this query will silently write data to the wrong column. Writing columns explicitly eliminates this risk.

Correct

INSERT INTO users (name, email)
VALUES ('Ada', '[email protected]');

Frequently asked questions

What should I use as a primary key?

Usually an auto-incrementing ID number. Making a meaningful field like email the key might seem tempting, but it can change—people change their email addresses, and all relationships tied to that key break. A meaningless and unchanging ID is always safer.

What is the difference between NULL and empty text?

NULL means "value is unknown or non-existent"; empty text means "value exists and is empty". This distinction is critical in queries: NULL is not equal to anything, not even itself. NULL checks are done with IS NULL, not with equals.

What type should be used to hold money?

DECIMAL or NUMERIC. Because floating-point types (FLOAT, DOUBLE) store decimals approximately, cent losses occur, and these accumulate into serious amounts over time. Since precision is a must in financial calculations, decimal types are used.

Summary

Table design is a project's most permanent decision: give every table a primary key, enforce constraints at the database level, and store money as a decimal type. Writing column names explicitly when doing an INSERT prevents silent data errors in the future.

HexLibraryHexLibrary

Learn programming more clearly. Every command is explained line by line with colors, reinforced with runnable exercises, test your code instantly — all in one place, free.

Learn

  • Home
  • Languages & Lessons
  • HexCode

Company

  • About
  • Contact

Legal

  • Privacy Policy
  • Terms of Use

Other Projects

  • HexToolz
  • Free Service Manuals
© 2026 HexLibrary · The clearest way to learn code