DDL (Data DefinAtion Language)
DDL is a sub-language of SQL used to create and manipulate objects in a database. DDL provides the following statements to manipulate the object in database:
We have shown examples of all the statements with respect to a table object.
Create Statement is used to create a new object in the database
CREATE TABLE <TABLE_NAME> (<COLUMN_NAME> <DATATYPE>(<SIZE>), <COLUMN_NAME> <DATATYPE>(<SIZE>) ... N);
CREATE TABLE EMP (EMPID NUMBER(10), ENAME VARCHAR2(20), SAL NUMBER(7,2))
Notice that the number datatype has (7,2) as its size. Here 7 is the size and 2 is the precision i.e. two digits are allowed after the decimal point and 5 digits before.(12345.67)
Note: The table structure can be viewed with the DESC command.
Alter statement is used to modify the structure of an existing object.
Below alteration can be done on a table:
- Addition of new columns
- Deletion of existing columns
- Changing the data type of columns
- Changing the size of columns
ALTER TABLE <TABLE_NAME> ADD|MODIFY (<COLUMN_NAME> <DATATYPE> (<SIZE>) [,<COLUMN_NAME> <DATATYPE> (<SIZE>)... N]);
ALTER TABLE EMP ADD(DEPTID NUMBER(5)); ALTER TABLE EMP MODIFY(EMPID NUMBER(5));
Note: To change the datatype of a column, the column must be empty.
Drop statement is used to delete an existing object from the database. It can also be used to delete any column from a table.
Syntax: To delete a single column ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>; To delete multiple columns ALTER TABLE <TABLE_NAME> DROP (COLUMN1, COLUMN2 ... N); Delete a table DROP TABLE <TABLE_NAME>;
Examples: ALTER TABLE EMP DROP COLUMN EMPID; ALTER TABLE EMP DROP(EMPID, DEPTID); DROP TABLE EMP;
Note: All columns cannot be deleted from a table using the ALTER statement.
Truncate statement is used to delete all data from the table, leaving only the table structure behind.
Truncate is a part of DDL(not DML) because the truncate command basically deletes all the records without putting them into temporary memory space. Any DML statement executed will make changes to the temporary copy of the table, and a commit or rollback command is required to make the changes persist in the database. Truncate on the other hand is directly executed on the table, no commit or rollback required.
Syntax: TRUNCATE TABLE <TABLE_NAME>
Rename statement is used to rename an existing object from a database.
RENAME <TABLE_NAME> TO <NEW_TABLE_NAME>
RENAME EMP TO EMPLOYEE;