Data Definition Language is used to modify the schema of the database. It will never impact the user rights for the database. Otherwise, it can erase records in some tables. It describes three statements: CREATE, ALTER and DROP.

CREATE statement

The exhaustive syntax of the CREATE statement for the tables is as follows:

CREATE TABLE <table name>
(<column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>][, <column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>]]*
 [,[ CONSTRAINT <constraint name>]
  {
   PRIMARY KEY (<column name>[, <column name>]*)
  |
   UNIQUE ([VALUE|<column name>[, <column name>]*])
  |
   FOREIGN KEY (<column name>[, <column name>]*) REFERENCES <table name> (<column name>[, <column name>]*)[ ON DELETE CASCADE][ ON UPDATE CASCADE]
  |
   CHECK (<predicate>[{ AND| OR} <predicate>]*)
  }
 ]*
);

The CREATE statement is used to create a new table with no record. Let's create the table office. The records in the office table will contain a technical id, the name of the office, a description, the number of available places, the availability and the date for the next office security control:

• Query:

CREATE TABLE office
(
   id_office INTEGER PRIMARY KEY NOT NULL,
   name VARCHAR(20) NOT NULL,
   description VARCHAR(255),
   place_number INTEGER NOT NULL,
   available SMALLINT NOT NULL DEFAULT 1,
   next_inspection DATE NOT NULL
);

• The table after the statement:

Now the table office can be used and filled as the tables reunion, employee, project and members:

The statement starts with CREATE TABLE, to indicate that what we want to create is a table. It's followed by the name of the table (i.e. office). The name of the table is followed by parentheses which describe all the columns of the table. The descriptions of the columns are separated by a comma. Each description contains the column name (for instance, id_office), the column type (INTEGER, VARCHAR, CHAR, DATE, etc...), an optional nullability information (nothing to indicate that the column can be null or NOT NULL to indicate that the column can't be null) and the optional keyword DEFAULT followed by a default value or the optional keyword PRIMARY KEY to indicate that the column is a primary key. If no default value is defined, NULL is the default value. If NOT NULL is defined, the column can't have NULL as default value.

You can see that the column id_office has been defined as a primary key, the column description can be null and the column available has 1 as default value.

ALTER statement

The exhaustive syntax of the ALTER statement for the tables is as follows:

ALTER TABLE <table name>
{
 ADD[ COLUMN] <column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>]
|
 ALTER[ COLUMN] <column name>[ SET DEFAULT <default option>| DROP DEFAULT]
|
 DROP[ COLUMN] <column name>
|
 ADD[ CONSTRAINT <constraint name>]
 {
  PRIMARY KEY (<column name>[, <column name>]*)
 |
  UNIQUE ([VALUE|<column name>[, <column name>]*])
 |
  FOREIGN KEY (<column name>[, <column name>]*) REFERENCES <table name> (<column name>[, <column name>]*)[ ON DELETE CASCADE][ ON UPDATE CASCADE]
 |
  CHECK (<predicate>[{ AND| OR} <predicate>]*)
 }
|
 DROP CONSTRAINT <constraint name>
};

The ALTER statement is used to modify a table. It can be used on a table with records in it.

ALTER TABLE office ADD CONSTRAINT unique_name_and_description UNIQUE (name, description);

ALTER TABLE office DROP CONSTRAINT unique_name_and_description;

ALTER TABLE office ADD has_video_projector SMALLINT DEFAULT 0;

ALTER TABLE office DROP COLUMN next_inspection;

DROP statement

The exhaustive syntax of the DROP statement for the tables is as follows:

DROP TABLE <table name>;

The DROP statement is used to remove table.

Now you can use a database for any type of data.

TRUNCATE statement

TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms. The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).^[1]

Syntax:

TRUNCATE TABLE table_name;

table_name: Name of the table to be truncated.
DATABASE name - student_data

1. ↑ https://www.geeksforgeeks.org/sql-drop-truncate/