IMPLEMENTATION OF DDL , DML, DCL AND TCL COMMANDS

IMPLEMENTATION OF DDL AND DML COMMANDS

DML COMMANDS

1. TO CREATE A TABLE:

SQL> create table table1(name varchar(20),roll int);

Table created.

2. TO VIEW THE DESCRIPTION OF THE TABLE:

SQL> desc table1;
Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
VARCHAR2(20)
ROLL
NUMBER(38)

3. TO ALTER THE TABLE:

A) TO ADD A FIELD INTO THE TABLE:

SQL> alter table table1 add(age int);

Table altered.

SQL> desc table1;
Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
VARCHAR2(20)
ROLL
NUMBER(38)
AGE
NUMBER(38)

B) TO MODIFY THE DATATYPE OF THE FIELD:

SQL> alter table table1 modify(name char(20));

Table altered.

SQL> desc table1;

----------------------------------------- -------- ----------------------------
NAME
CHAR(20)
ROLL
NUMBER(38)
AGE
NUMBER(38)

C) TO DROP A FIELD FROM THE TABLE:

SQL>alter table table1 drop(age);

Table altered;

SQL> desc table1;

Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
CHAR(20)
ROLL
NUMBER(38)

4. TO TRUNCATE THE TABLE:

SQL> truncate table table1;

Table truncated.

SQL> select * from table1;

no rows selected

SQL> desc table1;

Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
CHAR(20)
ROLL
NUMBER(38)
MARK
NUMBER(38)

5. TO DROP THE ENTIRE TABLE:

SQL>drop table table1;

Table dropped.

DML COMMANDS

1. TO INSERT VALUES INTO THE TABLE:

SQL> insert into table1 values('Abi',1,10);

1 row created.

2. TO VIEW THE TABLE:

SQL> select * from table1;

NAME
ROLL
AGE
-------------------- ---------- ----------
Abi
1
10

SQL> insert into table1 values('&name',&roll,&age);
Enter value for name: Anu
Enter value for roll: 2
Enter value for age: 11
old 1: insert into table1 values('&name',&roll,&age)
new 1: insert into table1 values('Anu',2,11)

1 row created.

SQL> /
Enter value for name: Devi
Enter value for roll: 3
Enter value for age: 12
old 1: insert into table1 values('&name',&roll,&age)
new 1: insert into table1 values('Devi',3,12)

1 row created.

SQL> select * from table1;

NAME
ROLL
AGE
-------------------- ---------- ----------
Abi
1
10
Anu
2
11
Devi
3
12
Priya
4
13

SQL> alter table table1 add(mark int);

Table altered.

SQL> desc table1;

Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
CHAR(20)
ROLL
NUMBER(38)
AGE
NUMBER(38)
MARK
NUMBER(38)

SQL> insert into table1 values('&name',&roll,&mark);
Enter value for name: Raji
Enter value for roll: 5
Enter value for mark: 89
old 1: insert into table1 values('&name',&roll,&mark)
new 1: insert into table1 values('Raji',5,89)

1 row created.

SQL> /
Enter value for name: Vedha
Enter value for roll: 6
Enter value for mark: 78
old 1: insert into table1 values('&name',&roll,&mark)
new 1: insert into table1 values('Vedha',6,78)

1 row created.

SQL> select * from table1;

NAME
ROLL MARK
-------------------- ---------- ----------
Abi
1
Anu
2
Devi
3
Raji
4
89
Vedha
5
78
5 rows selected.

3. TO UPDATE A FIELD:

SQL> update table1 set mark=98 where name='Abi';

1 row updated.

SQL> select * from table1;

NAME
ROLL MARK
-------------------- ---------- -----------
Abi
1
98
Anu
2
Devi
3
Raji
4
89
Vedha
5
78
5 rows selected.

SQL> update table1 set mark=79 where name='Anu';
1 row updated.

SQL> update table1 set mark=90 where name='Devi';

1 row updated.

SQL> select * from table1;

NAME
ROLL MARK
-------------------- ---------- ----------
Abi
1
98
Anu
2
79
Devi
3
90
Raji
4
89
Vedha
5
78
5 rows selected.

SQL> select name from table1 where mark>=90;

NAME
--------------------
Abi
Devi

4. TO DELETE A PARTICULAR ROW:

SQL>delete from table1 where name =’Anju’;

1 row deleted.

SQL> select roll from table1;

ROLL
----------
1
3
4
5

4 rows selected.

SQL> select * from table1;

NAME
ROLL MARK
-------------------- ---------- ----------
Abi
1
98
Devi
3
90
Raji
4
89
Vedha
5
78
4 rows selected.

IMPLEMENTATION OF DCL AND TCL COMMANDS

DCL COMMANDS

SQL> select * from tab;

TABTYPE CLUSTERID

------------------------------ ------- ----------

BIN$f0ZVcbwyRaG0Lh5iR+n+8Q==$0 TABLE

SQL> create table table1(name varchar(20),roll int);

Table created.

GRANT:

SQL> grant all on table1 to public;

Grant succeeded.

REVOKE:

SQL> revoke all on table1 from public;

Revoke succeeded.

TCL COMMANDS

SAVEPOINT:

SQL> insert into table1 values('&name',&roll);

Enter value for name: Abi

Enter value for roll: 1

old 1: insert into table1 values('&name',&roll)

new 1: insert into table1 values('Abi',1)

1 row created.

Enter value for name: Devi

Enter value for roll: 2

old 1: insert into table1 values('&name',&roll)

new 1: insert into table1 values('Devi',2)

1 row created.

SQL> savepoint s1;

Savepoint created.

SQL> insert into table1 values('&name',&roll);

Enter value for name: Priya

Enter value for roll: 3

old 1: insert into table1 values('&name',&roll)

new 1: insert into table1 values('Priya',3)

1 row created.

Enter value for name: Tanu

Enter value for roll: 4

old 1: insert into table1 values('&name',&roll)

new 1: insert into table1 values('Tanu',4)

1 row created.

SQL> savepoint s2;

Savepoint created.

SQL> select * from table1;

ROLL

------------------- ----------

1

2

3

4

ROLLBACK:

SQL> rollback to s1;

Rollback complete.

SQL> select * from table1;

ROLL

-------------------- ----------

1

2

COMMIT:

SQL> commit;

Commit complete.