DDL: CREATE, ALTER, DROP

1. What is DDL?

  • DDL (Data Definition Language): Commands used to define or modify the structure of database objects like tables, views, and indexes.
  • Key Commands:
    • CREATE
    • ALTER
    • DROP

2. CREATE Command

  • Purpose: Creates new database objects (e.g., tables, views, indexes).
  • 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 the structure of a table.
    • Can include constraints like NOT NULL, UNIQUE, CHECK, etc.

3. ALTER Command

  • Purpose: Modifies the structure of an existing table or other database objects.
  • Syntax: ALTER TABLE table_name ADD column_name datatype constraints; ALTER TABLE table_name MODIFY column_name datatype constraints; ALTER TABLE table_name DROP column_name;
  • Examples:
    • Add a new column: ALTER TABLE Students ADD Address VARCHAR(100);
    • Modify an existing column: ALTER TABLE Students MODIFY Name VARCHAR(100);
    • Drop a column: ALTER TABLE Students DROP COLUMN Address;
  • Key Points:
    • Can add, modify, or drop columns.
    • Does not delete table data.

4. DROP Command

  • Purpose: Deletes database objects completely, including all data and structure.
  • Syntax: DROP TABLE table_name;
  • Example: DROP TABLE Students;
  • Key Points:
    • Permanently deletes the object and its data.
    • Cannot be undone, so use cautiously.

Comparison Table

CommandPurposeEffect
CREATECreates new database objectsDefines a new structure (e.g., table, index).
ALTERModifies existing database objectsUpdates the structure without deleting data.
DROPDeletes existing database objectsPermanently removes the object and its data.

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.