Alter Tables

Sql tutorial · PySpark.in

2.6 ALTER TABLE

What is ALTER TABLE?

ALTER TABLE is used to modify the structure of an existing table.

Using ALTER TABLE, we can:

• Add new columns
• Rename columns
• Remove columns
• Modify table structure without deleting the table

Real-World Example

Suppose we already have a player table.

Now we want to:

• Add jersey number
• Rename a column
• Remove a column

We use ALTER TABLE.

Adding a New Column

What is ADD COLUMN?

ADD COLUMN is used to add a new column to an existing table.

Syntax

```

hide

ALTER TABLE table_name
ADD COLUMN column_name datatype;

```

Example

Add a new column jersey_num of type INTEGER to the player table.

```

hide

ALTER TABLE player
ADD COLUMN jersey_num INTEGER;

```

Before ADD COLUMN

Important Note

Existing rows will contain NULL values for the newly added column unless a default value is specified.

Rename a Column

What is RENAME COLUMN?

RENAME COLUMN is used to change the name of an existing column.

Syntax

```

hide

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

```

Example

Rename jersey_num to jersey_number.

```

hide

ALTER TABLE player
RENAME COLUMN jersey_num TO jersey_number;

```

Before RENAME COLUMN

Drop a Column

What is DROP COLUMN?

DROP COLUMN is used to remove a column from a table.

Syntax

```

hide

ALTER TABLE table_name
DROP COLUMN column_name;

```

Example

Remove the column jersey_number from the player table.

```

hide

ALTER TABLE player
DROP COLUMN jersey_number;

```

Before DROP COLUMN

Important SQLite Note

Older versions of SQLite do not support DROP COLUMN.

However:

• Modern SQLite versions support it.
• Older versions require creating a new table and copying data manually.

ALTER TABLE Flow Diagram

Complete Example

````

CREATE TABLE player (
name VARCHAR(200),
age INTEGER,
score INTEGER
);

INSERT INTO player (name, age, score)
VALUES
('Virat', 32, 45),
('Rakesh', 39, 35),
('Sai', 47, 30);

ALTER TABLE player
ADD COLUMN jersey_num INTEGER;

ALTER TABLE player
RENAME COLUMN jersey_num TO jersey_number;

ALTER TABLE player
DROP COLUMN jersey_number;

```

Summary

More Sql tutorials

• Introduction to Databases
• Introduction To DBMS
• Introduction To SQL
• Natural Join
• INNER JOIN
• LEFT JOIN

All tutorials · Try the free PySpark compiler · Practice challenges