Page 5 - DDL and DML Commands

Example1:
(modifying one column)
update emp
set sal = sal + 1000
where deptno = 20;
Example2:
(modifying two column)
update emp
set sal = sal + 1000, comm = comm+500
where job = 'SALESMAN';
Example3:
(updating to NULL and a scalar quantity)
Update employee
Set grade=NULL, col_new='ABC'

Altering/Modifying Table Structure

Adding Column
Syntax is:
ALTER TABLE <tablename>
ADD/MODIFY/DROP (<column name> [<data type> <size>],
<column name> [<data type> <size>],………);
Example
Alter table emp
ADD (city varchar(15));

Alter table emp
MODIFY (city varchar(25));

Dropping a column
Syntax is:
ALTER TABLE <table name>
DROP COLUMN <column name>
Example:
Alter table emp
DROP COLUMN city;
Restrictions on Alter table:
• Table name can't change
• Column size can't decrease if record exists

Few more examples:
Adding primary key
Alter table Customer Add Primary Key(SID);
Adding Foreign Key
Alter table Orders
Add Foreign Key(customer_sid) References Customer(SID);
Remaining a column/field of table
Alter table Customers
change First_Name FirstName varchar(20);