SQL ALTER TABLE

ALTER TABLE

The ALTER TABLE statement is used to add or drop columns in an existing table.

ALTER TABLE tableName ADD columnName dataType;

ALTER TABLE tableName DROP COLUMN columnName;

The "Books" table:

Title	Author	Publisher	Year
الدورة الدموية	إبن النفيس	دار العلم	1650
Java 2	L. Johnston	Fast Press	2002
Linux and Unix	J. Sam	Fast Press	2004
Operating Systems	M. Stone	Coriolis	2005
Web Programming	K. Yariv	East Edition	2005
XML Language	M. Salim	Knowledge Press	2000

Example

To add a column named "Price" in the "Books" table:

ALTER TABLE Books ADD Price decimal(6,2);

The result is:

Title	Author	Publisher	Year	Price
الدورة الدموية	إبن النفيس	دار العلم	1650	null
Java 2	L. Johnston	Fast Press	2002	null
Linux and Unix	J. Sam	Fast Press	2004	null
Operating Systems	M. Stone	Coriolis	2005	null
Web Programming	K. Yariv	East Edition	2005	null
XML Language	M. Salim	Knowledge Press	2000	null

Example

To drop the "Price" column added to the "Books" table:

ALTER TABLE Books DROP COLUMN Price;

The result is:

Title	Author	Publisher	Year
الدورة الدموية	إبن النفيس	دار العلم	1650
Java 2	L. Johnston	Fast Press	2002
Linux and Unix	J. Sam	Fast Press	2004
Operating Systems	M. Stone	Coriolis	2005
Web Programming	K. Yariv	East Edition	2005
XML Language	M. Salim	Knowledge Press	2000

Test your SQL Skills

To test your SQL skills click here.