Velamuvi AIAcademy

Lesson 16 of 17 · Changing Data

CREATE TABLE

Create your own database tables with SQL. Learn data types, primary keys and the NOT NULL, UNIQUE, DEFAULT and CHECK constraints, plus ALTER and DROP TABLE.

  • 8 min read
  • Beginner
  • Reviewed
In this lesson you will learn
  • How to create a table and choose a data type for each column
  • How constraints keep bad data out
  • How to link a new table to an existing one
  • How to change or remove a table

Designing a table

So far you have used tables that somebody else designed. Now you will design one.

Suppose the school wants learners to review courses. A review needs a rating, an optional comment and a link to the course it is about. CREATE TABLE defines the table: its name, its columns and the type of data each column holds.

Try it yourselfSQL
CREATE TABLE reviews (
  id        INTEGER PRIMARY KEY,
  course_id INTEGER,
  rating    INTEGER,
  comment   TEXT
);

INSERT INTO reviews (course_id, rating, comment)
VALUES (1, 5, 'Clear and friendly'),
       (1, 4, 'Good pace'),
       (2, 5, NULL);

SELECT * FROM reviews;

A new table is empty. The INSERT adds three reviews, and the id is filled in automatically because it is the primary key.

Data types

Every column has a data type that says what kind of value belongs in it.

TypeHoldsExample
INTEGERWhole numbers42
REALNumbers with decimals3.75
TEXTText of any length'Good pace'
DATEA calendar date'2026-06-01'
BOOLEANTrue or false1 or 0

These names work in the practice database. Other systems use more specific ones, such as VARCHAR(100) for text with a maximum length, DECIMAL(10, 2) for money and TIMESTAMP for a date with a time. The idea is identical: pick the type that fits the data.

Constraints: rules for good data

The first version of reviews accepts anything: a review with no rating, a rating of 900, a review for a course that does not exist. Constraints are rules written into the table that reject bad data at the door.

ConstraintRule
PRIMARY KEYIdentifies the row. Unique and never missing.
NOT NULLA value is required.
UNIQUENo two rows may share this value.
DEFAULT valueUsed when no value is given.
CHECK (condition)The value must pass a test.
REFERENCES table(column)A foreign key pointing to another table.

Here is a better design.

Try it yourselfSQL
CREATE TABLE reviews (
  id        INTEGER PRIMARY KEY,
  course_id INTEGER NOT NULL REFERENCES courses(id),
  rating    INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment   TEXT,
  status    TEXT NOT NULL DEFAULT 'published'
);

INSERT INTO reviews (course_id, rating, comment)
VALUES (1, 5, 'Clear and friendly'),
       (2, 4, NULL);

SELECT * FROM reviews;

Nobody supplied a status, so the default, published, was used for both rows.

Watching a constraint work

Try to add a rating of 9.

Try it yourselfSQL
CREATE TABLE reviews (
  id        INTEGER PRIMARY KEY,
  course_id INTEGER NOT NULL REFERENCES courses(id),
  rating    INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5)
);

INSERT INTO reviews (course_id, rating)
VALUES (1, 9);

The insert is refused with a message that the CHECK constraint failed. Change the 9 to a 4 and it works. It is far better for the database to reject a wrong value now than for someone to find it in a report months later.

Using the new table with the old ones

A table you create behaves like any other. Because course_id points to courses, the two can be joined.

Try it yourselfSQL
CREATE TABLE reviews (
  id        INTEGER PRIMARY KEY,
  course_id INTEGER NOT NULL REFERENCES courses(id),
  rating    INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5)
);

INSERT INTO reviews (course_id, rating)
VALUES (1, 5), (1, 4), (2, 5), (2, 3), (4, 3);

SELECT c.title,
       COUNT(r.id) AS reviews,
       ROUND(AVG(r.rating), 1) AS average_rating
FROM courses AS c
JOIN reviews AS r ON r.course_id = c.id
GROUP BY c.id, c.title
ORDER BY average_rating DESC;

Changing a table later

Requirements change. ALTER TABLE modifies a table that already exists. The most common use is adding a column.

Try it yourselfSQL
ALTER TABLE courses ADD COLUMN level TEXT DEFAULT 'Beginner';

SELECT title, hours, level
FROM courses;

Existing rows get the default value for the new column.

Removing a table

DROP TABLE deletes a table and all the data in it. There is no undo.

Try it yourselfSQL
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('temporary');

DROP TABLE notes;

SELECT * FROM notes;

The last line fails with no such table: notes, which proves the table is gone.

Good habits for table design

  • Give every table a primary key called id.
  • Name foreign keys after what they point to: course_id, student_id.
  • Use NOT NULL unless a missing value truly makes sense.
  • Store one fact in one place. If a value can be looked up through a key, do not copy it.
  • Use clear, lowercase names with underscores: order_items, not OrdItm.

Common mistakes

Practice exercise

Create a teachers table and add two rows to it. The SELECT in the editor shows the result when both steps work.

Your solutionSQL
-- 1. Create a table called teachers with three columns:
--      id       INTEGER PRIMARY KEY
--      name     TEXT, required
--      subject  TEXT
-- 2. Insert two teachers:  Ada / Python   and   Linus / SQL
-- Do not change the SELECT.


SELECT name, subject
FROM teachers
ORDER BY id;
Knowledge check

Which constraint makes sure a column always has a value?

Summary

  • CREATE TABLE name (column type, ...); defines a new table.
  • Each column has a data type such as INTEGER, TEXT or DATE.
  • Constraints such as NOT NULL, UNIQUE, DEFAULT and CHECK keep bad data out.
  • REFERENCES creates a foreign key.
  • ALTER TABLE changes a table, and DROP TABLE removes it with all its data.

One lesson to go: a project that uses everything.

Finished reading? Tick it off to track your progress.