Question

List Four DDL commands with syntax.

Answer 1

Of course. Here are four fundamental DDL (Data Definition Language) commands, complete with their purpose, general syntax, and a practical example for each.

---

Introduction to DDL

Data Definition Language (DDL) is a subset of SQL (Structured Query Language) used to define and manage the structure of database objects. These commands are used to create, modify, and delete objects like tables, indexes, and users. DDL commands are auto-committed, meaning the changes they make are permanent and cannot be rolled back.

---

1. CREATE

• Purpose: The CREATE command is used to build new database objects from scratch, most commonly a new table.
• General Syntax (for a table):
  `sql
  CREATE TABLE table_name (

  column1_name data_type [constraints],
  column2_name data_type [constraints],
  ...
  columnN_name data_type [constraints]
  

  );
  `

• Example:
  Let's create a table to store information about students. The table will have an ID, first name, last name, and major. StudentID will be the primary key.

  `sql
  CREATE TABLE Students (

  StudentID INT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Major VARCHAR(100)
  

  );
  `
  This command creates a new, empty table named Students with the specified columns and rules.

2. ALTER

• Purpose: The ALTER command is used to modify the structure of an existing database object. For a table, this can include adding, deleting, or modifying columns.
• General Syntax (common uses for a table):
  • To add a new column:
    `sql
    ALTER TABLE table_name
    ADD column_name data_type [constraints];
    `
  • To drop an existing column:
    `sql
    ALTER TABLE table_name
    DROP COLUMN column_name;
    `
  • To modify a column's data type:
    `sql
    ALTER TABLE table_name
    MODIFY COLUMN column_name new_data_type;
    `

• Example:
  Let's add a DateOfBirth column to the Students table we created earlier.

  `sql
  ALTER TABLE Students
  ADD DateOfBirth DATE;
  `
  After this command, the Students table now has a fifth column named DateOfBirth with a DATE data type.

3. DROP

• Purpose: The DROP command is used to permanently delete an entire database object, such as a table. This action removes both the structure of the object and all the data it contains. It is irreversible.
• General Syntax (for a table):
  `sql
  DROP TABLE table_name;
  `

• Example:
  If the Students table is no longer needed, we can delete it completely.

  `sql
  DROP TABLE Students;
  `
  This command permanently removes the Students table and all its data from the database.

4. TRUNCATE

• Purpose: The TRUNCATE command is used to quickly remove all rows (all data) from a table. Unlike DROP, TRUNCATE leaves the table's structure (columns, constraints, etc.) intact so it can be used again. It is much faster than the DML DELETE command for clearing large tables.
• General Syntax (for a table):
  `sql
  TRUNCATE TABLE table_name;
  `

• Example:
  Imagine the Students table is full of test data from last year, and we want to clear it for the new academic year without having to re-create the table.

  `sql
  TRUNCATE TABLE Students;
  `
  After this command, the Students table will be empty, but its structure (the columns StudentID, FirstName, LastName, Major, and DateOfBirth) still exists.

Summary Table

| Command | Purpose | Example Syntax |
| :--- | :--- | :--- |
| CREATE | To create new database objects. | CREATE TABLE Students (...); |
| ALTER | To modify the structure of existing objects. | ALTER TABLE Students ADD Email VARCHAR(100); |
| DROP | To permanently delete an entire object. | DROP TABLE Students; |
| TRUNCATE | To quickly delete all data from a table, leaving its structure. | TRUNCATE TABLE Students; |