Introduction to Data Definition Language (DDL) Commands

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:

  1. CREATE Command
  2. ALTER Command
  3. DROP Command
  4. TRUNCATE Command
  5. RENAME Command
  6. 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’.

Leave a Comment

Your email address will not be published. Required fields are marked *