A Data Definition Language (DDL) is a sublanguage of SQL responsible to create and manipulate database objects such as tables, indices, users, views, schemas, etc structure in SQL. Data Definition Language is also known as data description language in some contexts, as it describes the fields and records in a database table.
DDL provides the following statement for manipulating the database objects.
- Create
- Alter
- Drop
- Truncate
- Rename
Better to read the previous topic to get good knowledge about SQL.
Function in SQL
JOIN in SQL
Wildcard Operators in SQL
Operator in Transact SQL
Data Query Language
Contents
Create in Data Definition Language
Create command used for builds a new databse object such table, view, index, etc and has a predefined syntax.
Please look into the below syntax for creating a new table in database.
Syntax for Create Table in SQL
CREATE TABLE <T_NAME>
(
COL_NAME1 DATATYPE(SIZE),
COL_NAME2 DATATYPE(SIZE),
COL_NAME3 DATATYPE(SIZE)
................ N
);
Q. Write query for create a new employee table in SQL having columns such as employee id, employee name and salary.
CREATE TABLE TB_EMP
(
EMPNO INT,
NAME VARCHAR(10),
SAL NUMERIC (7,2)
);
The above example create an employee table structure physical in SQL database.
Once table structure is created to display the structure of table user can use describe, EXEC sp_help command.
EXEC SP_HELP TB_EMP
Alter
This statement is used to modification the database object.
- Add after new column after table design
- Deleting of exiting the column
- Changing a data type of column
- Changing the column datatype size of the table
Syntax for Alter Table
ALTER TABLE <T_NAME>
ADD/ALTER/[DROP <COLUMN>] (C_NAME DATATYPE(SIZE), COL_NAME DATATYPE(SIZE)..................N);
To add a new column, to delete a exiting column or change size of column is possible weather table not contain the data but change the data type of column a column must be empty or new rules must be satified with existing data.
Q. Write query for change name size to 50 charaters in SQL from employee table.
ALTER TABLE TB_EMP
ALTER NAME VARCHAR(50)
Q. Write query for add a new column address in employee table.
ALTER TABLE TB_EMP
ADD ADDRESS VARCHAR(250)
Q. Write query for delete salary column from employee table.
ALTER TABLE TB_EMP
DROP COLUMN SAL
Drop command in Data Definition Language
Deleting table structure using drop in data defination language. It will delete data as well as structure of a table in SQL.
Syntax for Detele Table in SQL
DROP TABLE <T_NAME>;
Q. Write query for delete the complete sysemp table from sql.
DROP TABLE SYSEMP;
TRUNCATE
This command is very similar to the drop command in SQL but It will be deleting all records of the table and physical remaining the structure using truncate. This command also coming under data definition language.
Syntax
TRUCATE TABLE <T_NAME>;
Please the below example to truncate a table from SQL database
TRUNCATE TABLE TB_EMP;
Rename
Rename a table using rename
Syntax
RENAME <T1_NAME> TO <T2_NAME>;
Q. Write query for change table from TB_EMP to Employee.
RENAME TB_EMP TO EMPLOYEE
Reference Link
Data Definition Language in Wiki
Data Definition Language in SQL Server
