Recent Posts

Saturday, 23 April 2016

Data definition Language

Data definition Language

    This is the first sub language in SQL which is used to define the database objects such as table, view, synonym, index and sequence. This language contains five commands those are
1. Create
2. Alter
3. Rename
4. Truncate
5. Drop

1. Create

     This command is used to creating the database objects such as Table, View e.t.c.,
Syntax:
create table <table name>(col1 datatype(Size),
col2 datatype(Size), coln datatype(size));
E.g:
create table emp(eid varhcar(3), ename varchar2(12), sal number(6,2));
Rules for naming a table
1. Table name should start with alphabets which contains minimum 1 and maximum 30 characters it should not allows any spaces or any special characters such as except _# and 0 to 9.
2. A table can have minimum 1 column maximum thousand columns.
3. A table can have 0 no of records and maximum n no of records up to hard disk capacity.
4. Oracle reserved keywords and words should not be used column names or table names.
5. The rules which we are following for table name the same rules are applicable for column names.
6. The numeric precision for column must be 1 to 38.

2. Alter

     This command is used to modify the structure of the table. Using this command we can perform four different operations. This command contains four subprograms those are
1. Alter – Modify
2. Alter– Add
3. Alter– Rename
4. Alter– Drop

1. Alter– Modify
     This command is used to increase or decrease the size of the data type and also we can use the data type from all data type to new data type.
Syntax
ALTER TABLE <Table name> MODIFY <Column name> data type(size)
E.g:
ALTER  TABLE EMP MODIFY ENAME VARCHAR2(20);
Syntax for modify more than one column
ALTER TABLE <Table name> MODIFY (col1 data type(size), ------,coln data type(size))
E.g
Alter table emp modify (eid number(5), ename char(10));

2. Alter - Add
     This command is used to add a new column to the existing table.
Syntax
Alter Table<Table name>add column name datatype(size)
E.g
ALTER TABLE EMP ADD DEPTNO NUMBER(3)
Syntax to add more than one column
ALTER TABLE<table name> add (col1 datatype(size), col2 datatype(size)----,coln datatype(size))
E.g
ALTER TABLE EMP ADD( mbnno number(10), addrs varchar2(10))
Note
     When ever we add a new column to a table the new column is always added to the end of the table only.

3. Alter – Rename
     This command is used to change the column name from old column name to new column name.
Syntax
ALTER TABLE<table name> rename column<old column name> to <new column name)
E.g
ALTER TABLE EMP RENAME COLUMN SAL TO SLARY.
Syntax change table name
ALTER TABLE<old table name> RENAME TO <New table name>
E.g
ALTER TABLE EMP RENAME TO EMP1;

4. Alter - Drop
     This command is used to remove the column from the existing table.
Syntax
ALTER TABLE< table name>DROP COLUMN COLUMN NAME.
E.g
ALTER TABLE EMP DROP COLUMN DEPTNO.
Syntax to drop more than one column
ALTER TABLE EMP DROP(eid, ename, salary, mbno, addrs)// Here we can’t drop all columns.


3. Rename

     This command is used to change the table name from old table name to new table name.
Syntax
RENAME <old table name> to <new table name>
E.g
RENAME EMP TO EMP1;

4. Trunkcate

     This command is use delete records permanently from the existing table.
Syntax
TRUNCATE TABLE<table name>
E.g
TRUNCATE TABLE EMP;

5. Drop

     This command is used to drop entire data from database.
Syntax
DROP TABLE< table name>
E.g
DROP TABLE EMP;
Difference between TRUNCATE and DELETE
1. TRUNCATE is DDL command, DELETE is DML command.
2. We can delete data permanently through TRUNCATE; Through DELTE it is temporarily.
3. We can’t delete single record Through TRUNCATE, but it is possible through DELTE.
Recycle bin
     To retrieve data from recycle bin
Syntax
Select * from recycle bin;
Flashback
     This command is used to retrieve back the table from the recycle bin.
Syntax
FLASHBACK TABLE<Table name> TO BEFORE DROP
E.g
Flashback table emp to before drop
Syntax to drop the table permanently from recycle bin
Synax
PURGE TABLE <Table name>
E.g
PURGE TABLE EMP;
Syntax drop table permanently from database:
Syntax
DROP TABLE < Table name> PURGE;
E.g
DROP TABLE EMP PURGE;

Next Tutorial  Data Manipulation Language

Previous Tutorial  SQL Basic Tutorial

No comments:

Post a Comment