DDL Command in SQL

DDL commands in SQL are used to define, modify, and manage the structure of database objects, such as tables, indexes, and constraints. Some common DDL commands include:

  1. CREATE: Used to create a new table, index, or other database object.
  2. ALTER: Used to modify an existing table’s structure, such as adding, deleting, or changing columns.
  3. DROP: Used to delete a table, index, or other database object.
  4. TRUNCATE: Used to remove all rows from a table, but keep the table structure.
  5. RENAME: Used to change the name of a table, index, or other database object.

Here is an example of how to use DDL commands in SQL:

Create a new database named Precords and create new tables named Players, then use all DDL Command to view changes in tables.

Create database Precords;

How to Create New Table in MS SQL Server?

Create table Players
(
Emp_ID int,
Fname varchar (20),
Age int,
DOB date,
Salary int
);

To View Newly Created table use Below Select Command.

Select *From Players;

How to Rename a Column name in a table?


Syntax: -
			EXEC sp_rename \'TABLENAME.oldcolumn_name\', \'Newcolumn_name\',\'Column\'

For Example,
			Above table Salary Column is renamed as NetSalary

EXEC sp_rename \'Players.Salary\',\'NetSalary\',\'Column\';

How to Add New column in the table?


Syntax: -
			ALTER TABLE table_name ADD NewColumn_name DATAYPE;
For Example,
			In the above  table EMail_ID is added as new Column

			Alter table Players add Email_id varchar(25);

How to Modify Datatype in a table of give column?


Syntax: -
			ALTER TABLE table_name ALTER COLUMN Column_name DATATYPE;
For Example,
			Above table EMail_ID is added as new Column

			Alter table Players alter column Netsalary decimal;

How to Delete a column in a table?

Syntax: -
			ALTER TABLE table_name DROP COLUMN Column_name;
For Example,
			Above table EMail_ID is added as new Column

			Alter table Players drop column Age;

How to Delete OR Drop/Truncate a table?

Syntax: -
			DROP TABLE Table_name;		--Drop delete Whole table 
			TRUNCATE TABLE Table_name;	-- Truncate delete table data only
For Example,
			Below we created a new table and use DROP Command to delete table.

			Drop table Player;
			Truncate Table Player;

Create a new table in the give database and use below commands.

Create table Player
(
Emp_ID int,
Fname varchar (20),
Age int,
DOB date,
Salary int
);

View the table data and added two more row the given table .

Select *from Player;
Insert into Player Values(101,\'Sachin\',35,\'1986/04/15\',35000);
Insert into Player Values(102,\'Virat\',25,\'1996/08/05\',35000);

Truncate table Player; -- This will delete both the rows only
Drop table Player;	-- this will delete both rows as well as header too means whole table