Tables, rows, columns and data types

Databases, tables, rows and columns

A relational database stores data in tables. Each table describes one kind of thing, such as students, courses or payments.

• A column is one attribute that every record has, such as name or city. Each column has a data type.
• A row is one record, such as one student.

Here is a students table we'll use throughout this path:

Kiran has no email recorded. A missing value in SQL is NULL, which means "unknown". It is different from an empty string or zero.

Common data types

Choosing the right type matters. The database rejects values of the wrong type, sorts and compares correctly (dates sort as dates, not as text), and stores data efficiently.

Creating the table

SQL (Structured Query Language) is the language for working with relational databases. CREATE TABLE defines a table's columns and types:

CREATE TABLE students (
    id     INTEGER PRIMARY KEY,
    name   VARCHAR(100) NOT NULL,
    city   VARCHAR(60)  NOT NULL,
    year   INTEGER      NOT NULL CHECK (year BETWEEN 1 AND 4),
    marks  INTEGER      CHECK (marks BETWEEN 0 AND 100),
    email  VARCHAR(255) UNIQUE
);

The extra words are constraints, rules the database enforces on every change:

• NOT NULL means the column must have a value.
• CHECK (...) rejects values that fail the condition, such as year = 9.
• UNIQUE means no two rows may share the same value.
• PRIMARY KEY is covered in the next lesson.

Adding rows

INSERT INTO students (id, name, city, year, marks, email)
VALUES (1, 'Asha Menon', 'Kochi', 2, 88, 'asha@example.com');

INSERT INTO students (id, name, city, year, marks)
VALUES (6, 'Kiran Rao', 'Bengaluru', 1, 77);   -- email is left NULL

Text values use single quotes in SQL. Keywords such as SELECT are case-insensitive, but writing them in capitals makes queries easier to read. -- starts a comment.

Try it

You can practise without installing anything using an online SQL playground such as DB Fiddle (choose PostgreSQL), or install PostgreSQL locally and use the psql command-line tool.