SQL DDL – Data Definition Language Commands

SQL DDL – Data Definition Language Commands -SQL commands are extensively used to interact with databases, enabling users to perform a wide range of actions on database systems. Understanding these commands is crucial for effectively managing and manipulating data.

Types of SQL Commands:

There are four types of SQL commands: DDL, DML, DCL, TCL.

Data Definition Language (DDL):

DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

DDL is a set of SQL commands used to create, modify, and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application.

Here are all the main DDL (Data Definition Language) commands along with their syntax:

Command	Description	Syntax
CREATE	Create database or its objects (table, index, function, views, store procedure, and triggers)	CREATE TABLE table_name (column1 data_type, column2 data_type);
DROP	Delete objects from the database	DROP TABLE table_name;
ALTER	Alter the structure of the database	ALTER TABLE table_name ADD COLUMN column_name data_type;
TRUNCATE	Remove all records from a table, including all spaces allocated for the records are removed	TRUNCATE TABLE table_name;
COMMENT	Add comments to the data dictionary	COMMENT 'comment_text' ON TABLE table_name;
RENAME	Rename an object existing in the database	RENAME TABLE old_table_name TO new_table_name;

SQL DDL: CREATE Command

It is used to create a new table in the database. – SQL CREATE

Syntax:

CREATE TABLE table_name (column_name DATATYPE […]);  

In above statement, table_name is the name of the table, column_name is the name of the columns and DATATYPE is used to define the type of data.

Example:

Create table Employee (
Employee_Name VARCHAR (25),
Employee_Email VARCHAR(50),
DOB DATE );

SQL DDL: DROP Command

It is used to delete both the structure and record stored in the table.

Syntax: To DROP a table permanently from memory

DROP TABLE table_name [cascade constraint];  

The cascade constraint is an optional parameter which is used for tables which have foreign keys that reference the table being dropped. If cascade constraint is not specified and used attempt to drop a table that has records in a child table, then an error will occur. So by using cascade constraints, all child table foreign keys are dropped.

Example

DROP TABLE Employee;  

SQL DDL: ALTER Command

It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

Following are the list of modifications that can be done using ALTER command.

• With the use of ALTER commands we can add or drop one or more columns form existing tables.
• Increase or decrease the existing column width by changing the data type
• Make an existing mandatory column to optional.
• Enable or disable the integrity constraints in a table. We can also add, modify or delete the integrity constraints from a table.
• We can also specify a default value for existing column in a table.

Adding new columns in Table:

With the use of ALTER table command we can add new columns existing table.

Syntax: To add a new column in the table

ALTER TABLE table_name ADD column_name column-definition;  

In the above syntax, where table_name corresponds to the name of the table, column-definition corresponds to the valid specifications for a column name and data type.

EXAMPLE:

ALTER TABLE Student ADD Adhar_Num VARCHAR (15);

SQL DDL: TRUNCATE Command

It is used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;  

Example:

TRUNCATE TABLE Employee;  

SQL DDL: COMMENT Command

Used to add comments to the data dictionary.

Syntax: 

COMMENT 'comment_text' ON TABLE table_name;

SQL DDL: RENAME Command

It is used to rename the table.

Syntax:

Rename <OLD_TBLENAME> to <NEW_TABLENAME>;  

In the above syntax, Rename is a command, <OLD_TABLENAME> is the name of the table and <NEW_TABLENAME> is the name that you have changed.

Example:

Rename STU_NAME to STUDENT_NAME; 

DDL commands play a crucial role in defining the database schema.