DDL Commands: CREATE, ALTER, DROP

Commands used to define or change the structure of database objects (tables, views, indexes).

Main Commands:

  • CREATE → Create objects
  • ALTER → Modify structure
  • DROP → Delete objects

1. CREATE (Make New Object)

Syntax:

CREATE TABLE table_name (
   column1 datatype constraints,
   column2 datatype constraints,
   ...
);

Example:

CREATE TABLE Students (
   ID INT NOT NULL,
   Name VARCHAR(50),
   Age INT CHECK (Age > 0),
   PRIMARY KEY (ID)
);

Key Points:

  • Defines table structure.
  • Supports constraints → NOT NULL, UNIQUE, CHECK, PRIMARY KEY.

2. ALTER (Change Structure)

Syntax:

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name MODIFY column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;

Examples:

  • Add column → ALTER TABLE Students ADD Address VARCHAR(100);
  • Modify column → ALTER TABLE Students MODIFY Name VARCHAR(100);
  • Drop column → ALTER TABLE Students DROP COLUMN Address;

Key Points:

  • Can add, modify, or drop columns.
  • Data in table remains safe.

3. DROP (Delete Object)

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Students;

Key Points:

  • Removes object + its data permanently.
  • Cannot be undone → use carefully.

Quick Comparison

CommandPurposeMake a new object
CREATEMake new objectDefines structure (e.g., table, index)
ALTERChange structureUpdates structure, keeps data
DROPDelete objectRemoves object + data permanently

MCQ

Q1: What does DDL stand for?
A. Data Definition Language
B. Data Description Language
C. Data Deployment Language
D. Data Development Language

Answer: A

Q2: Which of the following is NOT a DDL command?
A. CREATE
B. ALTER
C. INSERT
D. DROP

Answer: C

Q3: What is the primary purpose of the CREATE command?
A. To insert new records into a table.
B. To modify the structure of an existing table.
C. To define and create a new database object.
D. To delete an existing table and its data.

Answer: C

Q4: Which of the following is a correct CREATE TABLE syntax?
A. CREATE TABLE table_name;
B. CREATE TABLE table_name (column1 datatype constraints, column2 datatype);
C. CREATE TABLE table_name (column1 datatype, column2 constraints);
D. CREATE TABLE table_name column1 datatype, column2 datatype;

Answer: B

Q5: What happens if you try to create a table that already exists using CREATE TABLE without the IF NOT EXISTS clause?
A. The table is overwritten.
B. An error is raised.
C. The existing table is dropped and recreated.
D. A warning is generated, and the table remains unchanged.

Answer: B

Q6: What is the main purpose of the ALTER command?
A. To delete a table.
B. To modify the structure of an existing table.
C. To insert a column in an existing table.
D. To change data values in a table.

Answer: B

Q7: Which of the following commands will add a new column Address to the Employees table?
A. ALTER TABLE Employees ADD COLUMN Address VARCHAR(100);
B. ALTER Employees TABLE ADD Address VARCHAR(100);
C. ALTER TABLE Employees ADD Address;
D. ALTER Employees ADD COLUMN Address;

Answer: A

Q8: How can you drop a column Age from the Students table?
A. ALTER TABLE Students REMOVE COLUMN Age;
B. DROP COLUMN Age FROM Students;
C. ALTER TABLE Students DROP COLUMN Age;
D. DELETE COLUMN Age FROM Students;

Answer: C

Q9: Which command changes the datatype of a column?
A. ALTER COLUMN table_name datatype;
B. ALTER TABLE table_name MODIFY column_name datatype;
C. ALTER table_name COLUMN datatype;
D. CHANGE COLUMN datatype;

Answer: B

Q10: What does the DROP command do?
A. Deletes data from a table but keeps its structure intact.
B. Permanently deletes a database object, including its structure and data.
C. Removes specific rows from a table.
D. Modifies the structure of a table.

Answer: B

Q11: Which command is used to delete a table permanently?
A. DELETE TABLE table_name;
B. TRUNCATE TABLE table_name;
C. DROP TABLE table_name;
D. REMOVE TABLE table_name;

Answer: C

Q12: What happens if you use the DROP command on a table with dependent foreign key constraints?
A. The table is dropped, and constraints are removed.
B. The operation fails unless constraints are dropped first.
C. The table is dropped, but constraints remain in the database.
D. An error occurs, and the operation is rolled back.

Answer: B

Q13: Which command would you use to rename a column in an existing table?
A. ALTER TABLE table_name RENAME column_name TO new_name;
B. ALTER COLUMN table_name RENAME TO new_name;
C. MODIFY TABLE table_name column_name RENAME TO new_name;
D. CHANGE COLUMN table_name column_name TO new_name;

Answer: A

Q14: How can you safely create a table only if it doesn’t already exist?
A. CREATE TABLE table_name;
B. CREATE TABLE table_name IF EXISTS;
C. CREATE TABLE IF NOT EXISTS table_name;
D. CREATE IF NOT EXISTS TABLE table_name;

Answer: C

Q15: Which DDL command allows you to add constraints to an existing table?
A. CREATE
B. ALTER
C. DROP
D. TRUNCATE

Answer: B

Exam Tips

  1. CREATE: Used for defining new objects like tables and views.
  2. ALTER: Used for modifying existing objects (add, modify, or delete columns).
  3. DROP: Irreversible command that removes objects completely.
  4. Memorize the syntax and understand practical scenarios for each command.