Review of Altering Tables and Database Stuctures

 

 

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