DDL

 Data Definition Language(DDL)

A database scheme is specified by a set of definitions which are expressed by a special language called a data definition language (DDL).The result of compilation of DDL statements is a set of tables which are stored in a special file called data dictionary or directory.

Whenever data is read or modified in the database system, the data directory is consulted.

A DDL may also be defined as a DDL commands in SQL are used to define database schema and their types. 

It modifies and creates structure of the data which will be stored in database. It also defines indexes. 

The different types of DDL commands are:

• CREATE:

The CREATE command is used to create a new database or objects of a database such as tables, views, etc.

Syntax:

CREATE TABLE table_name

  (column1 datatype,

   column2 datatype,

   ...

   columnN datatype,

);

Here, the column parameters specify the names of columns of the table and their datatypes are defined.

Example:

CREATE TABLE Student

(

  StudentID int,

  Name varchar(255),

  Roll int,

);

Example Explained: 

StudentID, Name, Roll are the column names of table 'student' and their datatypes are also defined. 'int' for integer datatype and 'varchar' to store characters and maximum length for this field is 255 characters.

We can also create a new table using another table holding same column definitions or selected columns.

For Example:

CREATE TABLE Learner AS

SELECT Name, Roll

FROM Student;   

Here, a new table 'Learner' is created from an existing table 'Student' with columns Name & Roll.

• ALTER:

The ALTER command is used to modify the structure of a database. It could be either used to alter the characteristics of an existing attribute or to add new attribute(s).

Syntax:

To add new column in a table-

ALTER TABLE table_name 

ADD column1 datatype, column2 datatype,...,columnN, datatype;

To modify an existing column in a table-

ALTER TABLE table_name

ALTER COLUMN column1 datatype, column2 datatype,...,columnN, datatype;

Example:

ALTER TABLE student

ADD  city varchar(255);

ALTER COLUMN roll varchar(255);

Here, varchar(255) is the new modified datatype of column roll.

Note: The 'ALTER COLUMNN' statement is applicable for SQL Server/MS Access. In MySQL/Oracle (prior version 10G), we make use of 'MODIFY COLUMN'. And, in Oracle 10G and later, 'MODIFY' is applicable.

• DROP

The DROP command is used to remove databases and tables from RDBMS.

Syntax:

DROP object_type object_name;

Example:

DROP DATABASE School;

DROP TABLE Student;

• TRUNCATE

The TRUNCATE command is used to delete all the records from a table and free all spaces allocated for the records, however, the tables are not deleted itself.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE student;

• RENAME

It is used to rename a table or any column in a table.

Syntax:

To rename table-

ALTER TABLE table_name

RENAME TO new_table_name;

To rename column-

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;

Note: 'RENAME COLUMN' is applicable in Oracle. Make use of 'CHANGE COLUMN' for MySQL/MariaDB.

Example:

ALTER TABLE student

CHANGE COLUMN parent's_name TO guardian's_name;

COMMENT

COMMENT is used to add comments to the data dictionary. There are total three formats to write comments:

Example:

--It's a single line comment.

/*This is an example of 

multi line comment. It is starting in one line.*/

Here's the in line comment. /*An

extended version of multi line 

comment*/ written between '/*' and '*/'. 

❮ PrevNext ❯