Creating tables and adding rows

Introduction

Before you can ask questions, the data needs somewhere to live. This lesson builds tables, fills them and changes their design, the everyday work of setting up a database.

How it works

CREATE TABLE

CREATE TABLE table_name (
  column_name TYPE extra_rules,
  ...
);

Each column gets a type. SQLite has five storage types, and other DBMSs have more:

SQLite keeps dates as text in the form '2026-10-30', which sorts correctly.

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT DEFAULT 'CSE',
  cgpa REAL
);

-- Every column given
INSERT INTO students (id, name, dept, cgpa) VALUES (1, 'Rahim', 'EEE', 3.45);

-- No id: SQLite picks the next number
INSERT INTO students (name, dept, cgpa) VALUES ('Nadia', 'BBA', 3.80);

-- No dept: the DEFAULT is used. No cgpa: it is NULL
INSERT INTO students (name) VALUES ('Karim');

-- Several rows in one statement
INSERT INTO students (name, cgpa) VALUES ('Tania', 3.10), ('Sumon', 2.95);

SELECT * FROM students;

Step through and watch the Tables panel:

• id INTEGER PRIMARY KEY gives each row a unique number. Leave it out of an INSERT, and SQLite picks the next number for you. (MySQL calls this AUTO_INCREMENT; PostgreSQL uses SERIAL or GENERATED ... AS IDENTITY.)
• DEFAULT 'CSE' is used when an INSERT does not give dept a value.
• NOT NULL means the column may never be empty. More rules like it come in the lesson on keys and constraints.

INSERT

INSERT INTO students (name, dept, cgpa) VALUES ('Rahim', 'EEE', 3.45);
INSERT INTO students (name) VALUES ('Karim'), ('Nadia');   -- several rows at once

Always name the columns. INSERT INTO students VALUES (...) without names also works, but it must list every column in the table's exact order, and it breaks the day someone adds a column.

NULL: no value

A column the INSERT leaves out, with no default, holds NULL. NULL means unknown or missing. It is not 0 and not an empty text '': a student whose CGPA is NULL has no CGPA yet; one with 0.0 has a CGPA of zero. The next lessons show that NULL needs care in conditions and sums.

Syntax

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT DEFAULT 'CSE'
);
INSERT INTO students (name) VALUES ('Karim');
ALTER TABLE students ADD COLUMN email TEXT;
DROP TABLE students;

Example

CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO teachers (name) VALUES ('Dr. Alam'), ('Ms. Sultana');

-- A new column: the old rows get NULL in it
ALTER TABLE teachers ADD COLUMN email TEXT;
UPDATE teachers SET email = 'alam@uni.edu' WHERE id = 1;

ALTER TABLE teachers RENAME TO faculty;
SELECT * FROM faculty;

DROP TABLE faculty;

• ALTER TABLE ... ADD COLUMN adds a column; existing rows get NULL (or the column's default) in it.
• ALTER TABLE ... RENAME TO renames the table.
• DROP TABLE deletes the table and every row in it. There is no undo.

Line by line

1. CREATE TABLE teachers makes a table with an id and a name, and two rows are inserted.
2. ALTER TABLE teachers ADD COLUMN email TEXT; adds a column. Both existing rows get NULL in it.
3. The UPDATE gives teacher 1 an email address, so only Ms. Sultana's email is still NULL.
4. ALTER TABLE teachers RENAME TO faculty; renames the table, and the rows go with it.
5. SELECT * FROM faculty; shows both rows with the new column.
6. DROP TABLE faculty; deletes the table and its rows for good.

Expected result

id  name         email
--  -----------  ------------
1   Dr. Alam     alam@uni.edu
2   Ms. Sultana  NULL
(2 rows)

In the real world

Every application's first release creates its tables, and every later release that adds a feature usually adds columns with ALTER TABLE. Tools called migration frameworks keep track of these changes for teams.

Common mistakes

• Inserting without naming the columns, which breaks when a column is added.
• Using an empty text '' or 0 where NULL is meant, or the other way round.
• Running DROP TABLE when only the rows should go: that is DELETE FROM.

Best practices

• Choose the most precise type for every column.
• Mark every column that must always have a value as NOT NULL.
• Keep the CREATE TABLE statements in a file, so the design can be rebuilt.

Summary

CREATE TABLE defines columns and their types, and INSERT adds rows, best with the columns named. A missing value is NULL, ALTER TABLE changes the design, and DROP TABLE removes a table completely.

Mark lesson complete