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 etc.,

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. Truncate

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 DELETE.
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

Syntax

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;
Data Definition Language
Scroll to top