The ALTER TABLE statement in SQL is a DDL statement used to alter the structure of an existing table while the table is actively in use. It is used to add, delete, or modify columns in an existing table in real time.

Below are a few examples of the SQL ALTER TABLE Syntax:

As you will see, even though SQL is a standardized language, there are a few subtleties in the syntax of the commands between the developers of the software.

To add a column in a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype

To delete a column in a table, use the following syntax

(Some database systems don't allow deleting a column):

ALTER TABLE table_name

DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

Using MS SQL Server or MS Access:

ALTER TABLE table_name

ALTER COLUMN column_name datatype

Using My SQL or Oracle:

ALTER TABLE table_name

MODIFY COLUMN column_name datatype

Oracle 10G and later:

ALTER TABLE table_name

MODIFY column_name datatype

Highlights and Outcomes This Week

We will practice the above script in class. Additionally, during class, we will practice Union, Intersection, and Difference queries. Difference queries are sometimes referred to as minus queries. At the end of the week you should be able to write your own independent Alter Table queries, Intersection queries, Difference queries, and Union Queries.

In Class Practice Assignments

In Class Practice: Alter Table Statements

Homework

Assignment: Alter Table Statements

Readings

Continue Reviewing and Studying Chapter 5. Thoroughly read this Chapter to fully understand the material in this Module

Example Exercise

There are several in class example assignments this week. We will continue to use SQL script described in chapter 5 and in the material that I provide. This week we introduce additional advance applied SQL queries. We will expand on writing SQL script while continuing to use multiple table joins, and now introducing inner queries.

Discussions for the Week

ethical discussion on Ethics in Information Control

Database Project

Weekly Quiz

There is no Quiz scheduled for this Module.

In Class Example Exercise I

In Class Example Exercise II