Tables, Rows, and Columns

A table is the basic structural unit of a relational database: it defines a fixed set of named, typed columns, and stores data as rows that each provide one value (or NULL) per column. Designing a table well — choosing the right columns and the right data type for each one — is one of the most consequential decisions in building a database-backed application.

Why it matters

A table definition is a promise about the data: which columns exist and what kind of value each may hold. Choosing the right type prevents invalid data, such as text in a price column, and affects storage and speed. A poor choice made at the start, such as storing dates as text, is painful to correct once the table is full.

Defining a Table's Structure

A table is created with CREATE TABLE, listing each column's name and data type. Once created, every row inserted into that table must conform to this structure: it must provide a value for every NOT NULL column, and every value must match (or be convertible to) its column's declared type.

Common SQL Data Types

Choosing an appropriate type for each column keeps data both valid and efficient to store and query.

• INTEGER / BIGINT — whole numbers, commonly used for IDs and counts.
• VARCHAR(n) / TEXT — variable-length text, with VARCHAR(n) capping the length.
• DECIMAL(p, s) / NUMERIC(p, s) — exact fixed-precision numbers, essential for money.
• DATE / TIMESTAMP — calendar dates, and dates combined with a time component.
• BOOLEAN — true/false values (some databases emulate this with a small integer).

Rows as Records

Each row represents one complete record — one employee, one order, one product. A well-designed table keeps every row describing exactly one kind of "thing"; mixing unrelated kinds of records in a single table (for example, storing both customers and products in one generic table) breaks the relational model and makes queries and constraints far harder to write correctly.

Example

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    in_stock BOOLEAN DEFAULT TRUE,
    created_at DATE DEFAULT CURRENT_DATE
);

INSERT INTO products (id, name, price) VALUES
    (1, 'Wireless Mouse', 19.99),
    (2, 'Mechanical Keyboard', 79.50);

SELECT id, name, price FROM products;

Returns 2 rows: (1, 'Wireless Mouse', 19.99), (2, 'Mechanical Keyboard', 79.50)

Exercise

Create a products table with these columns: an integer primary key id; a name of up to 100 characters that is required; an integer price; a boolean in_stock; and a date added_on. Insert the two rows given in the comments, then select the name and price of the products that are in stock.

Expected output: name | price then Pen | 12

-- TODO: create the products table

-- TODO: insert (1, 'Pen', 12, TRUE, '2026-01-15') and (2, 'Bag', 450, FALSE, '2026-02-01')

-- TODO: name and price of the products that are in stock

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price INTEGER,
  in_stock BOOLEAN,
  added_on DATE
);

INSERT INTO products (id, name, price, in_stock, added_on) VALUES
  (1, 'Pen', 12, TRUE, '2026-01-15'),
  (2, 'Bag', 450, FALSE, '2026-02-01');

SELECT name, price FROM products WHERE in_stock = TRUE;

Quick quiz

Interview questions