Question

Discuss some reasons for changing the structure of a table. Provide at least 3 unique examples and lots of details

Answer 1

To add a column to a table using SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD command to tell the RDBMS that we want to add a column.

Syntax

The SQL syntax for ALTER TABLE Add Column is,

ALTER TABLE "table_name"
ADD "column_name" "Data Type";

Examples

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime

Example 1: Add one column to a table

Our goal is to add a column called "Gender". To do this, we key in:

MySQL:

ALTER TABLE Customer ADD Gender char(1);

Oracle:

ALTER TABLE Customer ADD Gender char(1);

SQL Server:

ALTER TABLE Customer ADD Gender char(1);

The resulting table structure is:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime
Gender	char(1)

Sometimes we need to change the data type of a column. To do this, we use the ALTER TABLE Modify Column command. For Oracle and MySQL, the SQL syntax for ALTER TABLE Modify Column is,

ALTER TABLE "table_name"
MODIFY "column_name" "New Data Type";

For SQL Server, the syntax is,

ALTER TABLE "table_name"
ALTER COLUMN "column_name" "New Data Type";

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime

Our goal is to alter the data type of the "Address" column to char(100). To do this, we key in:

MySQL:

ALTER TABLE Customer MODIFY Address char(100);

Oracle:

ALTER TABLE Customer MODIFY Address char(100);

SQL Server:

ALTER TABLE Customer ALTER COLUMN Address char(100);

Resulting table structure:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(100)
City	char(50)
Country	char(25)
Birth_Date	datetime

Sometimes we will wish to delete a column from an existing table in SQL. To do this, we specify that we want to change the table structure via the ALTER TABLE command, followed by a specification indicating that we want to remove a column. The detailed syntax for each database is as follow:

In MySQL, the syntax for ALTER TABLE Drop Column is,

ALTER TABLE "table_name"
DROP "column_name";

In Oracle and SQL Server, the syntax for ALTER TABLE Drop Column is,

ALTER TABLE "table_name"
DROP COLUMN "column_name";

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime

Our goal is to drop the "Birth_Date" column. To do this, we key in:

MySQL:

ALTER TABLE Customer DROP Birth_Date;

SQL Server:

ALTER TABLE Customer DROP COLUMN Birth_Date;

Oracle:

ALTER TABLE Customer DROP COLUMN Birth_Date;

The resulting table structure is:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)