SQLite for Beginners: ALTER TABLE — ADD COLUMN, RENAME & More | Episode 12

SQLite for Beginners: ALTER TABLE — ADD COLUMN, RENAME & More

In this tutorial, you'll learn how to evolve your SQLite tables using the ALTER TABLE command. We'll start with a simple student grades table and demonstrate how to add a new column, rename an existing column, and rename the entire table without losing any data.

Code

-- Create initial table with student grades
CREATE TABLE student_grades (
  id INTEGER PRIMARY KEY,
  student_name TEXT,
  subject TEXT,
  grade TEXT
);

-- Add a new column 'email' to the existing table
ALTER TABLE student_grades ADD COLUMN email TEXT;

-- Rename the column 'subject' to 'course' (SQLite 3.25.0+)
ALTER TABLE student_grades RENAME COLUMN subject TO course;

-- Rename the entire table from 'student_grades' to 'enrollments'
ALTER TABLE student_grades RENAME TO enrollments;

-- Verify the table names
.tables

Key Points

• ALTER TABLE ADD COLUMN adds a new column with NULL values for existing rows.
• ALTER TABLE RENAME COLUMN changes a column's name without affecting data.
• ALTER TABLE RENAME TO renames the whole table safely and efficiently.
• Use the .tables command in SQLite CLI to list all current tables.
• These commands let you modify table structure without recreating or losing data.