Alter Table Statement

<< Categories of SQL Commands,

Select Statement, Attribute Allias >>

Database Management System (CS403)

Lecture No. 27

Reading Material

"Database Management Systems", 2nd edition, Raghu Ramakrishnan, Johannes Gehrke,

McGraw-Hill

"Teach Yourself SQL in 21 Days", Second Edition Que Series.

Overview of Lecture

Data Manipulation Language

In the previous lecture we were studying DDL in which we studied the CREATE

command along with different examples. We also saw different constraints of create

command. In this lecture we will study the ALTER and other SQL commands with

examples.

Alter Table Statement

The purpose of ALTER statement is to make changes in the definition of a table

already created through Create statement. It can add, and drop the attributes or

constraints, activate or deactivate constraints. It modifies the design of an existing

table. The format of this command is as under:

Syntax

ALTER TABLE table {

ADD [COLUMN] column type [(size)] [DEFAULT default] |

ALTER [COLUMN] column type [(size)] [DEFAULT default] |

ALTER [COLUMN] column SET DEFAULT default |

DROP [COLUMN] column |

RENAME [COLUMN] column TO columnNew

}

The ALTER TABLE statement has these parts:

Part

Description

Table

The name of the table to be altered.

206

Database Management System (CS403)

Column

The name of the column to be altered or added to or deleted from table.

ColumnNew The new name of the altered column

Type

The data type of column.

The size of the altered column in characters or bytes for text or binary

Size

columns.

An expression defining the new default value of the altered column.

Default

Can contain literal values, and functions of these values

Using the ALTER TABLE statement, we can alter an existing table in several

ways. We can:

Use ADD COLUMN to add a new column to the table. Specify the name, data

type, an optional size, and an optional default value of the column.

Use ALTER COLUMN to alter type, size or default value of an existing

column.

Use DROP COLUMN to delete a column. Specify only the name of the

column.

Use RENAME COLUMN to rename an existing column. We cannot add,

delete or modify more than one column at a time.We will now see an example

of alter command

ALTER TABLE Student

add constraint fk_st_pr

foreign key (prName) references

Program (prName)

This is a simple example, in which we have incorporated a constraint and the names

are meaningful, so that if in the future we have to refer them, we can do so. We will

now see an example of removing or changing attribute.

ALTER TABLE student

ALTER COLUMN stFName char (20)

ALTER TABLE student

Drop column curSem

ALTER TABLE student

Drop constraint ck_st_pr

Now in these examples either an attribute is deleted or altered by using the keywords

of Drop and Alter. We will now see an example in which few or all rows will be

removed, or whole table is required to be removed. The TRUNCATE is used to delete

all the rows of any table but rows would exist. The DELETE is used to delete one or

many records. If we want to remove all records we must use TRUNCATE. Next is the

DROP table command, which is used to drop the complete table from the database.

TRUNCATE TABLE table_name

Truncate table class

207

Database Management System (CS403)

Delete can also be used

DROP TABLE table_name

Data Manipulation Language

The non-procedural nature of SQL is one of the principle characteristics of all 4GLs -

Fourth Generation Languages - and contrasts with 3GLs (eg, C, Pascal, Modula-2,

COBOL, etc) in which the user has to give particular attention to how data is to be

accessed in terms of storage method, primary/secondary indices, end-of-file

conditions, error conditions (eg, Record NOT Found), and so on. The Data

Manipulation Language (DML) is used to retrieve, insert and modify database

information. Data Manipulation is retrieval, insertion, deletion and modification of

information from the database SQL is a non-procedural language that is, it allows the

user to concentrate on specifying what data is required rather than concentrating on

the how to get it. There are two types of DML.First is procedural in which: the user

specifies what data is needed and how to get it. Second is nonprocedural in which the

user only specifies what data is needed. The DML component of SQL comprises of

following basic statements:

Insert To add new rows to tables.

Select To retrieve rows from tables

Update To modify the rows of tables

Insert

The INSERT command in SQL is used to add records to an existing table. We will

now see the format of insert command as under:

INSERT [INTO] table

{[ ( column_list ) ]

{ VALUES

( { DEFAULT | NULL | expression } [ ,...n] )

}

| DEFAULT VALUES

The basic format of the INSERT...VALUES statement adds a record to a table using the

columns you give it and the corresponding values you instruct it to add. You must

follow three rules when inserting data into a table with the INSERT...VALUES

statement:

The values used must be the same data type as the fields they are being added to.

The data's size must be within the column's size. For instance, you cannot add an 80-

character string to a 40-character column.

The data's location in the VALUES list must correspond to the location in the column

list of the column it is being added to. (That is, the first value must be entered into the

first column, the second value into the second column, and so on.)

The rules mentioned above must be followed. We will see the examples of the insert

statement in the coming lectures.

Summary

SQL provides three statements that can be used to manipulate data within a database.

The INSERT statement has two variations. The INSERT...VALUES statement inserts a set

of values into one record. The INSERT...SELECT statement is used in combination with

a SELECT statement to insert multiple records into a table based on the contents of one

or more tables. The SELECT statement can join multiple tables, and the results of this

208

Database Management System (CS403)

join can be added to another table. The UPDATE statement changes the values of one

or more columns based on some condition. This updated value can also be the result

of an expression or calculation.

The DELETE statement is the simplest of the three statements. It deletes all rows from

a table based on the result of an optional WHERE clause. If the WHERE clause is

omitted, all records from the table are deleted. Modern database systems supply

various tools for data manipulation. Some of these tools enable developers to import

or export data from foreign sources. This feature is particularly useful when a

database is upsized or downsized to a different system. Microsoft Access, Microsoft

and Sybase SQL Server, and Personal Oracle7 include many options that support the

migration of data between systems.

Exercise:

Try inserting values with incorrect data types into a table. Note the errors and then

insert values with correct data types into the same table.

209

Table of Contents: