SQL · Theory

Add/Delete Column

Theory

23/439

Add/Delete Column

Add: ALTER TABLE students ADD COLUMN city VARCHAR(40); Delete: ALTER TABLE students DROP COLUMN city; DROP COLUMN throws that data away.

Add AFTER marks if you care about column order (optional). Apps that SELECT * will see the new column — better to list columns in production SQL.

Trap — DROP COLUMN used by a live INSERT. Trap: ADD without a type.

On the example next to this theory: Add/Delete Column — aLTER TABLE changes structure without rebuilding the whole table.

Exam tip

ADD COLUMN city VARCHAR(40); DROP COLUMN city;

Add/Delete Column — sample query

CREATE TABLE items (id INTEGER PRIMARY KEY, title TEXT);
ALTER TABLE items ADD COLUMN price REAL DEFAULT 0;
INSERT INTO items (id, title, price) VALUES (1, 'Pen', 10);
SELECT * FROM items;

Add/Delete Column — aLTER TABLE changes structure without rebuilding the whole table.

Short notes

DefALTER … ADD COLUMN / DROP COLUMN.
RuleDROP COLUMN deletes that data.
TrapDropping a column the app still uses.

SQL · Theory

Add/Delete Column

← All stacks

Theory

23/439

Add/Delete Column

Add: ALTER TABLE students ADD COLUMN city VARCHAR(40); Delete: ALTER TABLE students DROP COLUMN city; DROP COLUMN throws that data away.

Add AFTER marks if you care about column order (optional). Apps that SELECT * will see the new column — better to list columns in production SQL.

Trap — DROP COLUMN used by a live INSERT. Trap: ADD without a type.

On the example next to this theory: Add/Delete Column — aLTER TABLE changes structure without rebuilding the whole table.

Exam tip

ADD COLUMN city VARCHAR(40); DROP COLUMN city;

Add/Delete Column — sample query

CREATE TABLE items (id INTEGER PRIMARY KEY, title TEXT);
ALTER TABLE items ADD COLUMN price REAL DEFAULT 0;
INSERT INTO items (id, title, price) VALUES (1, 'Pen', 10);
SELECT * FROM items;

Add/Delete Column — aLTER TABLE changes structure without rebuilding the whole table.

Short notes

DefALTER … ADD COLUMN / DROP COLUMN.
RuleDROP COLUMN deletes that data.
TrapDropping a column the app still uses.

Add/Delete Column

Add/Delete Column — sample query

Short notes

Questions

Add/Delete Column

Add/Delete Column — sample query

Short notes

Questions