DML & DCL commands in RDBMS

DML &DCL COMMANDS:

1. CREATE COMMAND:

                    Syntax:

                                 Create table tablename ( fieldname column datatype(size));

                    It is used to create a new table.

2.INSERT COMMAND:

                   Syntax:

                                 Insert into tablename values(‘& fieldname’…….);

                   It is used to insert the values into table.

3.UPDATE COMMAND:

                     Syntax:

                                    Update tablename set fieldname=values;

                     It is used to update the table values when new fields are added using alter command.

4.DELETE COMMAND:

                    Syntax:

                                  Delete from tablename where fieldname=value;

                     Deleting the row which contain the values mentioned.

5.ROLL BACK COMMAND:

                  Syntax:

                                Rollback;               

                  It is used to undo the last performed operation.

6.GRANT COMMAND:

                    Syntax:

                                  Grant select,insert,update,delete on tablename to public.

                    It is used to grant permission to perform mentioned operation.

7.TO CHECK:

                     Selete operation is used after each query execution to check whether the corresponding operations are performed.

8.REVOKE COMMAND:

                      Syntax:

                                   Revoke selete ,insert, update ,delete on tablename from public.

                      It is used to cancel the permission provided by grant operations.

OUTPUT:

SQL> create table ajith(name varchar(10),rollno number(10),dept varchar(3));

Table created.

SQL> insert into ajith values('&name','&rollno','&dept');

Enter value for name: shalini

Enter value for rollno: 11

Enter value for dept: cse

old   1: insert into ajith values('&name','&rollno','&dept')

new   1: insert into ajith values('shalini','11','cse')

1 row created.

SQL> insert into ajith values('&name','&rollno','&dept');

Enter value for name: anoshka

Enter value for rollno: 12

Enter value for dept: ece

old   1: insert into ajith values('&name','&rollno','&dept')

new   1: insert into ajith values('anoshka','12','ece')

1 row created.

SQL> insert into ajith values('&name','&rollno','&dept');

Enter value for name: nive

Enter value for rollno: 13

Enter value for dept: eee

old   1: insert into ajith values('&name','&rollno','&dept')

new   1: insert into ajith values('nive','13','eee')

1 row created.

SQL> desc ajith

 Name                            Null?    Type

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

 NAME                                     VARCHAR2(10)

 ROLLNO                                   NUMBER(10)

 DEPT                                     VARCHAR2(3)

SQL> alter table ajith add(fees number (5));

Table altered.

SQL> desc ajith

 Name                            Null?    Type

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

 NAME                                     VARCHAR2(10)

 ROLLNO                                   NUMBER(10)

 DEPT                                     VARCHAR2(3)

 FEES                                     NUMBER(5)

SQL> select * from ajith;

NAME          ROLLNO DEP      FEES

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

shalini           11 cse

anoshka           12 ece

nive              13 eee

SQL> update ajith set dept='cse' where rollno=13;

1 row updated.

SQL> select * from ajith;

NAME          ROLLNO DEP      FEES

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

shalini           11 cse

anoshka           12 ece

nive              13 cse

SQL> delete from ajith where name='nive';

1 row deleted.

SQL> select * from ajith;

NAME          ROLLNO DEP      FEES

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

shalini           11 cse

anoshka           12 ece

SQL> commit;

Commit complete.

SQL> roll back;

Rollback complete.

SQL> select * from ajith;

NAME          ROLLNO DEP      FEES

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

shalini           11 cse

anoshka           12 ece

SQL> grant select,insert,update,delete on ajith to public;

Grant succeeded.

SQL> select * from ajith;

NAME          ROLLNO DEP      FEES

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

shalini           11 cse

anoshka           12 ece

SQL> delete from ajith where rollno=11;

1 row deleted.

SQL> select * from ajith;

NAME          ROLLNO DEP      FEES

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

anoshka           12 ece

SQL> revoke select,insert,update,delete on ajith from public;

Revoke succeeded.

SQL> select * from ajith;

NAME          ROLLNO DEP      FEES

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

anoshka           12            ece

Posted in: MySQL