Creating and Modifying Tables

About the SQL covered in this course

This course teaches the universal SQL syntax that works across major RDBMSs — MySQL, Oracle, PostgreSQL, SQL Server, and more. The basics like CREATE TABLE / SELECT / WHERE / JOIN / GROUP BY write the same way no matter which database you switch to.

For technical reasons the in-browser console runs SQLite, but whenever a feature differs between databases (length enforcement on types, how to list tables, etc.) you'll see a side-by-side note like In MySQL you'd write... so you know what to use elsewhere.

What is a table?

In a relational database, you store data in tables (grids of rows and columns). Each table consists of columns and rows (records), and each column has a fixed data type that controls what values it can hold.

Designing a table means deciding up front which columns to include and which type to assign to each one. Locking down the types prevents unexpected values from sneaking in later.

CREATE TABLE — creating a table

To create a table you use the CREATE TABLE statement. Write the table name, then in parentheses list column name + type pairs separated by commas. Don't put a comma after the last column.

Locking down types up front makes it harder for unexpected data to slip in (like a string ending up in a numeric column).

-- Create a table by listing column names and types
CREATE TABLE book_record (
  id INTEGER,
  title TEXT,
  price INTEGER,
  published_on TEXT
);

Common data types

SQL data types vary in name and detail across database systems, but starting out you only need to know the following types.

What about MySQL's VARCHAR / CHAR / DATETIME?

In MySQL and PostgreSQL you'll often see fine-grained types like VARCHAR(255) / CHAR(13) / BOOLEAN / DATETIME. The console here accepts these syntactically, but length enforcement (like VARCHAR(10) rejecting an 11th character) only kicks in on strict-typed RDBMSs like MySQL or PostgreSQL.

For exercises in this course, columns mostly use INTEGER / TEXT / REAL to keep things simple. When you port a definition to MySQL, you can replace INTEGER with INT and TEXT with VARCHAR(N) or TEXT — the structure of CREATE TABLE itself stays the same.

Try creating a book_record table for storing books. (Run it correctly and the explanation will appear.)

① On the first line, write DROP TABLE IF EXISTS book_record; to remove any existing book_record.

② Next, write CREATE TABLE book_record (...) with three columns: id INTEGER, title TEXT, and price INTEGER.

③ To verify, finish with SELECT name FROM sqlite_master WHERE type='table'; and confirm that book_record shows up in the list.

PRIMARY KEY — uniquely identifying a row

A primary key (PRIMARY KEY — a column used to uniquely identify a row) is a column where values must be unique and never NULL. You make a column the primary key when its values must always exist and never collide, like an employee number or user ID.

With a primary key in place you can reliably retrieve a single row. As a rule, every table should have exactly one primary key.

-- Add PRIMARY KEY after the column definition
CREATE TABLE member (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  joined_on TEXT
);

INTEGER PRIMARY KEY auto-numbers

A column declared as INTEGER PRIMARY KEY gets auto-numbered starting from 1 whenever you INSERT without specifying its value. In MySQL the same auto-numbering is written as id INT PRIMARY KEY AUTO_INCREMENT, and in PostgreSQL as id SERIAL PRIMARY KEY. In this course's console, INTEGER PRIMARY KEY is enough.

If you INSERT with an explicit value, that value is used, and the next auto-number resumes from the integer just after it.

Try creating a member table for storing members, with id as the primary key.

① Write DROP TABLE IF EXISTS member; to remove any existing member.

② Write CREATE TABLE member (...) with three columns: id INTEGER PRIMARY KEY, name TEXT NOT NULL, and joined_on TEXT.

③ Write INSERT INTO member (name, joined_on) VALUES ('Frank', '2026-04-01'), ('Grace', '2026-04-02'); to insert two rows without specifying id.

④ Finish with SELECT * FROM member; to confirm id was auto-numbered.

Trying a constraint violation — how NOT NULL works

Let's deliberately break a rule to see how constraints (rules a column's values must obey) like NOT NULL and PRIMARY KEY work. NOT NULL is a rule that doesn't allow NULL in the column. Adding it to required fields (name, email address, etc.) prevents incomplete rows from sneaking in.

The user table created at the top of the article was defined with name TEXT NOT NULL. If you try to insert NULL into the name column, the database will reject the INSERT and return an error.

The name column of the user table is defined with the NOT NULL constraint. Deliberately insert NULL to see the constraint-violation error.

① Run the prepared INSERT INTO user (...) VALUES (10, NULL, 30, 'Tokyo'); (since you're trying to put NULL into name, you should get an error like NOT NULL constraint failed: user.name).

(This exercise is correct when an error comes back — that's exactly the role of a constraint: keeping invalid data out before it ever lands.)

-- INSERT that triggers a NOT NULL violation in Exercise 3
INSERT INTO user (id, name, age, city)
  VALUES (10, NULL, 30, 'Tokyo');

Inspecting and dropping tables

After you create a table, you'll want to check which tables exist and what columns each one has. The console here uses these two queries:

- List of tables: SELECT name FROM sqlite_master WHERE type='table';

- Column definitions: PRAGMA table_info(table_name);

In MySQL you'd write SHOW TABLES; and DESCRIBE table_name;; Oracle uses SELECT table_name FROM user_tables;. Each database has its own dedicated syntax. DROP TABLE for removing a table works the same way across all databases.

-- ① List all tables
SELECT name FROM sqlite_master WHERE type='table';

-- ② Show column definitions of the user table
PRAGMA table_info(user);

-- ③ Drop a table
DROP TABLE IF EXISTS book_record;

Take a peek inside the user table set up at the top of the article, then practice dropping a table you no longer need.

① Write PRAGMA table_info(user); to display the column definitions of the user table.

② Look at the pk column in the result to see which column is the primary key.

③ Drop the book_record table you created in Exercise 1 with DROP TABLE IF EXISTS book_record;.

④ Finally, run SELECT name FROM sqlite_master WHERE type='table'; and confirm book_record is gone from the list.

ALTER TABLE — modifying an existing definition

Once your system is in production, requests like "add one more column" or "rename the table" are inevitable. That's where ALTER TABLE comes in. The four operations covered here all work in MySQL and PostgreSQL with nearly identical syntax.

MySQL's MODIFY / CHANGE COLUMN / AFTER

In MySQL you can change a column's type with ALTER TABLE table MODIFY col type;, change a column's name and type at the same time with CHANGE COLUMN old new type;, and drop the primary key with DROP PRIMARY KEY;.

-- Rename the table
ALTER TABLE user RENAME TO user_record;

-- Add a column (an email column is appended to the end)
ALTER TABLE user_record ADD COLUMN email TEXT;

-- Rename a column
ALTER TABLE user_record RENAME COLUMN city TO area;

-- Drop a column
ALTER TABLE user_record DROP COLUMN email;

Apply all three ALTER TABLE operations (add, rename, drop) to the user table in sequence. (This is the last exercise in the article.)

① Add the email column with ALTER TABLE user ADD COLUMN email TEXT;.

② Rename the city column to area with ALTER TABLE user RENAME COLUMN city TO area;.

③ Drop the email column you added with ALTER TABLE user DROP COLUMN email;.

④ Finally, run PRAGMA table_info(user); and confirm area is in place after the rename and email is no longer there.