Skip to main content

Data Definition Language(DDL)

1.Data Definition Language (DDL)

— CREATE
— ALTER
— DROP
— RENAME
— TRUNCATE

Create Table

Syntax:

CREATE TABLE [schema.]table
(columndatatype[DEFAULT expr][, ...]);

Naming convention Rules for Creating table:
– Must begin with a letter
– Must be 1–30 characters long
– Must contain only A–Z, a–z, 0–9, _, $, and #
– Must not duplicate the name of another object owned by the same user
– Must not be an Oracle server–reserved word

ie.

CREATE TABLE Mycontact(id NUMBER(10),mobile NUMBER(10), name VARCHAR(10) );

Defining default value while creating table.
Note:The default data type must match the column data type.

CREATE TABLE hire_dates(id NUMBER(8), hire_date DATE DEFAULT SYSDATE);

How to See table Description

DESCRIBE Mycontact
Creating table using sub-query:
CREATE TABLE EMP
AS 
SELECT employee_id, last_name,
salary*12 ANNUAL_SAL,
hire_date FROM employees;

ALTER TABLE Statement

Using ALTER TABLE statement You can
–-Add a new column
–-Modify an existing column definition
–-Drop a column
–-Rename a column
–-Change table to read-only status

Use the ALTER TABLE syntax to put a table into the read-only mode:
ALTER TABLE employees READ ONLY;

Adding new Column in table:
syntax:

ALTER TABLE table_name ADD column_name datatype CONSTRAINT;

i.e

ALTER TABLE Employees ADD deptno NUMBER(5) NOT NULL;
Modifying Column
ALTER TABLE EMPLOYEES MODIFY SALARY NUMBER(15,2);
Dropping a Table
DROP TABLE EMP;

3.RENAME:

Is used to change the rename of the table or database.
Syntax,

Rename old_table_name to new_table_name;

i.e

Rename temp_1 to temp;

4. TRUNCATE:

It is also delete all the rows from a table and free space containing the table
Syntax:

Truncate table table_name;

i.e

 Truncate table emp;