ZeePedia

Alter Table Statement

<< Categories of SQL Commands,
Select Statement, Attribute Allias >>
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules