Data Definition Language

Data Definition Language (DDL)

Data Definition Language is a one of subset of SQL.

These SQL commands or statements are used to create, modify, and drop the structure of database objects like table, view, procedure, indexes etc. 

Different DDL Commands are

•    Create

•    Alter

•    Drop

•    Truncate

•    Comment

•    Rename

Create: Create is used for creating databases & database objects.

CREATE DATABASE: The CREATE DATABASE statement is used to create a database.

Syntax:

CREATE DATABASE database_name

Example:

Create DataBase College

CREATE TABLE: Database tables can be added with the CREATE TABLE statement. The CREATE TABLE statement is used to create a table in a database.

Syntax:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

The data type specifies what type of data the column can hold.

Example: If you want to create the employee table, the statement would be like,

CREATE TABLE employee
(id number (5),
name char (20),
dept char (10),
age number (2),
salary number (10),
location char (10)
);

Alter: To modify an existing database object.

ALTER TABLE: The SQL ALTER TABLE command is used to change the structure of an existing table. It helps to add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.

Syntax to add a column:

ALTER TABLE table_name ADD column_name datatype;

Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employee ADD experience number (3);

Syntax to drop a column:

ALTER TABLE table_name DROP column_name;

Example: To drop the column "location" from the employee table, the query would be like

ALTER TABLE employee DROP location;

Syntax to modify a column:

ALTER TABLE table_name MODIFY column_name datatype;

Example: To modify the column salary in the employee table, the query would be like

ALTER TABLE employee MODIFY salary number (15, 2);

Drop: It is used for droping database & database objects.

The SQL DROP TABLE statement is the SQL command that removes an entire SQL table. When a SQL table must be removed, use the SQL DROP TABLE statement. Once a table is dropped we cannot get it back, so be careful while using DROP command. When a table is dropped all the references to the table will not be valid. Dropping tables have the benefit of:

•    reducing confusion

•    reducing disk space and resource requirements

Syntax for dropping Tables:

Drop Table table1, table2,….

Example: Drop Table Employee

Syntax for dropping databases:

Drop DataBase database1, database2,...

Example: Drop DataBase College

Truncate: It removes all records from a table, including all spaces allocated for the records are removed.

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax to TRUNCATE a table:

TRUNCATE TABLE table_name;

Example: To delete all the rows from employee table, the query would be like,

TRUNCATE TABLE employee;

Difference between DROP and TRUNCATE Statement: If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same; therefore any of the above problems will not exist.

Rename: It is used to rename an object.

The SQL RENAME command is used to change the name of the table or a database object.

If you change the object's name any reference to the old name will be affected. You have to manually change the old name to the new name in every reference.

Syntax to rename a table:

RENAME TABLE {tbl_name} TO {new_tbl_name};
Where {tbl_name} table that exists in the current database, and {new_tbl_name} is new table name.
Example: To change the name of the table employee to my_employee, the query would be like
RENAME employee TO my_emloyee;

Comment: The comment statement allows storing some comments about tables, views or columns in the data dictionary.

This is useful especially for large databases where you want others to understand some specific bits of information about a table, such as the type of information stored in the table. An example of using this command to add comments to a table appears as

Example:

SQL> comment on table employee is

2 ‘This is a table containing employees';
Comment created.

0 comments:

Post a Comment