Data Definition Language commands are used to create, modify the structure of the database in SQL. The following are the list of Data Definition Language Commands in SQL:
- CREATE Command
- ALTER Command
- DROP Command
- TRUNCATE Command
- RENAME Command
- COMMENT Command
CREATE Command:
CREATE command is used to create the table in the database.
Syntax: CREATE TABLE table_name(
Datatype_name,variable_name,
Datatype_name, variable_name,
————————————–/*define the columns to be in the database*/
);
Example:
CREATE TABLE STUDENT( ID INTEGER, NAME VARCHAR, AGE INTEGER, MARKS INTEGER );
The above query creates STUDENT table with the following columns:
- An ID of type Integer
- NAME of Variable Length Character type
- AGE of type Integer
- MARKS of Integer type
ALTER Command:
ALTER command is used to change the structure of the database. This is used to add or delete or modify a column in the table
Syntax:
- ALTER TABLE table_name ADD column_name datatype_name; – Add a column to database
- ALTER TABLE table_name DROP COLUMN column_name;- Remove a column from the database
- ALTER TABLE table_name MODIFY column_name datatype_name/length;- Modify column of database
Example:
ALTER TABLE STUDENT ADD GRADE INT; ALTER TABLE STUDENT DROP COLUMN GRADE; ALTER TABLE STUDENT MODIFY GRADE VARCHAR;
- The first query adds a GRADE column of Integer type to the STUDENT table
- The second query removes the GRADE column from the STUDENT table
- The third query changes the datatype of GRADE to Variable-length character in the STUDENT table
DROP Command:
DROP command is used to delete the table along with its allocated memory space
Syntax:
DROP TABLE table_name;
Example:
DROP TABLE STUDENT;
The above query removes the STUDENT table along with its allocated memory space
TRUNCATE Command:
TRUNCATE command is used to delete records from the table in the database
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE STUDENT;
The above query deletes the records present in the STUDENT table
RENAME Command:
RENAME command is useful in renaming the table in the database
Syntax:
ALTER TABLE table_name RENAME TO new_name
Example:
ALTER TABLE STUDENT RENAME TO OLD_STUDENTS;
The above query renames the table STUDENT to OLD_STUDENTS
COMMENT Command:
COMMENT command is useful for saving information about the table. This is useful for documentation purpose and useful in an understanding table when there is a large number of tables in the database.
Syntax:
COMMENT ON COLUMN table_name.cloumn_name IS ‘comment_content’;
Example:
COMMENT ON COLUMN STUDENT.AGE IS ‘Student age’;
The above query adds a comment for column AGE in the STUDENT table as ‘Student age’.