DDL,Dml,Tcl Commands

(1)

DDL: DDL:

create command

create

create is a DDL command used to create a table or a database. is a DDL command used to create a table or a database.

Creating a Database Creating a Database

To create a database in RDBMS,

To create a database in RDBMS, createcreate command is uses. Following is the S command is uses. Following is the Syntax,yntax, create

create database database database-namedatabase-name;;

Example for Creating Database Example for Creating Database create database Test;

create database Test;

The above command will create a database named The above command will create a database namedTestTest..

Creating a Table Creating a Table

create

create command is also used to create command is also used to create a table. e can s!eci"y names and dataty!es o" variousa table. e can s!eci"y names and dataty!es o" various columns along.Following is the Syntax,

columns along.Following is the Syntax, create

create table table table-nametable-name

{ {

column-name1

column-name1 datatype1, datatype1,

column-name2

column-name3

column-name4

column-name4 datatype4 datatype4 };

};

create table command will tell the database s

create table command will tell the database system to create a new table with given ystem to create a new table with given table nametable name and column in"ormation.

and column in"ormation.

Example for

Example for creating Tacreating Tableble

create table Student(id int, name varchar, age int); create table Student(id int, name varchar, age int);

The above command will create a new table

The above command will create a new tableStudentStudent in database system with # columns, namely in database system with # columns, namely id, name and age.

(2)

alter command

alter command is used "or alteration o" table structures. There are various uses o" alter command, such as,

• to add a column to existing table • to rename any existing column

• to change datatype of any column or to modify its size. • alter is also used to drop a column.

To Add Column to existing Table

$sing alter command we can add a column to an existing table. Following is the Syntax, alter table table-name add(column-name datatype);

%ere is an &xam!le "or this,

alter table Student add(address char);

The above command will add a new column address to the Student table

To Add Multiple Column to existing Table

$sing alter command we can even add multi!le columns to an existing table. Following is the Syntax,

alter table table-name add(column-name1 datatype1, column-name2 datatype2,

column-name3 datatype3);

alter table Student add(ather!name varchar("#), m$ther!name varchar("#), d$b date);

The above command will add three new columns to theStudent table

To Add column with Default alue

alter command can add a new column to an existing table with de"ault values. Following is the Syntax,

alter table table-name add(column-name1 datatype1 default data);

(3)

alter table Student add(d$b date deault %1!&an!''%);

The above command will add a new column with de"ault value to theStudent table

To Modify an existing Column

alter command is used to modi"y data ty!e o" an existing column . Following is the Syntax, alter table table-name m$diy(column-name datatype);

alter table Student m$diy(address varchar(3#));

The above command will modi"y address column o" theStudent table

To !ename a column

$sing alter command you can rename an existing column. Following is the Syntax, alter table table-name rename $ld!c$lumn!name t$ c$lumn!name;

alter table Student rename address t$ $cati$n;

The above command will rename address column to Location.

To Drop a Column

alter command is also used to dro! columns also. Following is the Syntax, alter table table-name dr$p(c$lumn!name);

alter table Student dr$p(address);

The above command will dro! address column "rom the Student table "#L $ueries to Truncate% Drop or !ename a Table

truncate command

truncate command removes all records "rom a table. But this command will not destroy the table's structure. hen we a!!ly truncate command on a table its (rimary )ey is initiali*ed. Following is its Syntax,

truncate table table-name %ere is an &xam!le ex!laining it.

(4)

truncate table Student;

The above +uery will delete all the records o"Student table.

truncate command is di""erent "romdelete command. delete command will delete all the rows "rom a table whereas truncate command reinitiali*es a table-li)e a newly created table.

For eg. /" you have a table with 01 rows and an auto2increment !rimary )ey, i" you usedelete command to delete all the rows, it will delete all the rows, but will not initiali*e the !rimary )ey, hence i" you will insert any row a"ter using delete command, the auto2increment !rimary )e y will start "rom 00. But in case o" truncate command, !rimary )ey is reinitiali*ed.

drop command

drop +uery com!letely removes a table "rom database. This command will also destroy the table structure. Following is its Syntax,

drop table table-name

%ere is an &xam!le ex!laining it.

dr$p table Student;

The above +uery will delete theStudent table com!letely. /t can also be used on Databases. For &xam!le, to dro! a database,

dr$p database Test;

The above +uery will dro! a database namedTest "rom the system.

rename $uery

rename command is used to rename a table. Following is its Syntax, rename table old-table-name t$ new-table-name

%ere is an &xam!le ex!laining it.

rename table Student t$ Student!rec$rd;

The above +uery will renameStudent table to Student-record.

DML command

Data Mani!ulation Language -DML statements are used "or manag ing data in database. DML commands are not autocommitted. /t means changes made by DML command are not

(5)

&' ()"*!T command

/nsert command is used to insert data into a table. Following is its general syntax, INSERT int$ table-name values(data1,data2,)

Lets see an exam!le,

3onsider a table Student with "ollowing "ields.

S_id S_Name age

*+S-T int$ Student values(1#1,%.dam%,1/);

The above command will insert a record intoStudent table.

S_id S_Name age

&+& Adam &,

*xample to (nsert )-LL alue to a column

Both the statements below will insert 4$LL value intoage column o" the Student table.

*+S-T int$ Student(id,name) values(1#2,%.le0%);

5r,

*+S-T int$ Student values(1#2,%.le0%,null);

The above command will insert only two column value other column is set to null.

S_id S_Name age

&+& Adam &,

&+/ Alex

*xample to (nsert Default alue to a column

*+S-T int$ Student values(1#3,%hris%,deault)

S_id S_Name age

&+& Adam &,

&+/ Alex

&+0 chris &1

(6)

7lso, i" you run the below +uery, it will insert de"ault value into the age column, whatever the de"ault value may be.

*+S-T int$ Student values(1#3,%hris%)

/' -2DAT* command

$!date command is used to u!date a row o" a table. Following is its general syntax, UPDATE table-name set c$lumn!name  value where condition;

Lets see an exam!le,

update Student set age1 here s5id1#2;

S_id S_Name age

&+& Adam &,

&+/ Alex &3

&+0 chris &1

*xample to -pdate multiple columns

678.T Student set s5name%.bhi%,age19 here s5id1#3;

The above command will u!date two columns o" a record.

S_id S_Name age

&+& Adam &,

&+/ Alex &3

&+0 Abhi &4

0' Delete command

Delete command is used to delete data "rom a table. Delete command can also be used with condition to delete a !articular row. Following is its general syntax,

DELETE r$m table-name;

*xample to Delete all !ecords from a Table 8T r$m Student;

(7)

*xample to Delete a particular !ecord from a Table

3onsider the "ollowingStudent table

S_id S_Name age

&+& Adam &,

&+/ Alex &3

&+0 Abhi &4

8T r$m Student here s5id1#3;

The above command will delete the record where s2id is 01# "romStudent table.

S_id S_Name age

&+& Adam &,

&+/ Alex &3

TCL command

Transaction 3ontrol Language-T3L commands are used to manage transactions in database.These are used to manage the changes made by DML statements. /t also allows statements to be grou!ed together into logical transactions.

Commit command

3ommit command is used to !ermanently save any transaaction into database. Following is 3ommit command's syntax,

commit_;

!ollbac5 command

This command restores the database to last commited state. /t is also use with save!oint command to 8um! to a save!oint in a transaction.

Following is Rollbac) command's syntax, rollback t$ savepoint-name;

(8)

savepoint command is used to tem!orarily save a transaction so that you can rollbac) to that !oint whenever necessary.

Following is save!oint command's syntax, a!epoint savepoint-name;

*xample of "aepoint and !ollbac5

Following is the class table,

ID NAME

& abhi

/ adam

1 alex

Lets use some S9L +ueries on the above table and see the results.

*+S-T int$ class values(/,%-ahul%); c$mmit;

678.T class set name%abhi:it% here id%/%; savep$int A ;

*+S-T int$ class values(",%hris%); savep$int ";

*+S-T int$ class values(9,%rav$%); savep$int #;

ST < r$m class;

The resultant table will loo) li)e,

ID NAME & abhi / adam 1 alex , abhi6it 7 chris 4 brao

4owrollback tosavepoint B r$llbac= t$ ;

(9)

ID NAME & abhi / adam 1 alex , abhi6it 7 chris

4owrollback tosavepoint A r$llbac= t$ .;

The result table will loo) li)e

ID NAME

& abhi

/ adam

1 alex