Alter Table Cheat Sheet < Introduction to SQL

 

Alter Table

ALTER

clause is used to add, delete, or modify columns in an existing table. Let's learn more about ALTER clause using the following database.

Database The database consists of a

player

table that stores the details of players who are a part of a tournament.

player

table stores the name, age and score of players.

Add Column

Syntax

1

2

3

4

ALTER TABLE

table_name

ADD

column_name datatype;

SQL

• You can use
  PRAGMA TABLE_INFO(table_name)
  command to check the updated schema of the table.

Example

Add a new column

jersey_num

of type

integer

to the

player

table.

1

2

3

4

ALTER TABLE

player

ADD

jersey_num INT;

SQL

Note

Default values for newly added columns in the existing rows will be NULL.

Rename Column

Syntax

1

2

ALTER TABLE

table_name RENAME COLUMN c1 TO c2;

SQL

Example

Rename the column

jersey_num

in the

player

table to

jersey_number

.

1

2

ALTER TABLE

player RENAME COLUMN jersey_num TO jersey_number;

SQL

Drop Column

Syntax

1

2

ALTER TABLE

table_name DROP COLUMN column_name;

SQL

Example

Remove the column

jersey_number

from the

player

table.

1

2

ALTER TABLE

player DROP COLUMN jersey_number;

SQL

Note

DROP COLUMN is not supported in some DBMS, including SQLite.

Try it Yourself!

1. Add a new column
   id
   of type
   int
   to the player table.
2. Update the name of column
   id
   to
   player_id
   in the player table.