SQL Data Definition Language (DDL) 2 — Questions and Answers
Question 1: Which DDL command removes an entire table including its structure and all data?
- DELETE TABLE
- DROP TABLE (Correct answer)
- TRUNCATE TABLE
- REMOVE TABLE
Correct answer: DROP TABLE
DROP TABLE deletes the table definition along with all its data and indexes.
Question 2: What is the key difference between TRUNCATE and DELETE?
- TRUNCATE removes the table structure
- TRUNCATE cannot be filtered with WHERE and resets storage (Correct answer)
- DELETE is faster than TRUNCATE
- TRUNCATE only works on views
Correct answer: TRUNCATE cannot be filtered with WHERE and resets storage
TRUNCATE removes all rows without a WHERE clause and generally resets the table more efficiently than DELETE.
Question 3: Which command adds a new column to an existing table?
- ALTER TABLE ... ADD COLUMN (Correct answer)
- MODIFY TABLE ... NEW COLUMN
- UPDATE TABLE ... ADD
- INSERT COLUMN INTO
Correct answer: ALTER TABLE ... ADD COLUMN
ALTER TABLE with ADD COLUMN modifies an existing table's structure by adding a column.
Question 4: Why is TRUNCATE classified as DDL rather than DML?
- It returns a result set
- It operates at the table-structure level and is not transaction-logged per row (Correct answer)
- It requires a WHERE clause
- It only affects indexes
Correct answer: It operates at the table-structure level and is not transaction-logged per row
TRUNCATE deallocates data pages at the structural level rather than logging individual row deletions, so it is treated as DDL.
Question 5: Which statement renames an existing database object in standard SQL?
- RENAME TABLE / ALTER TABLE ... RENAME (Correct answer)
- CHANGE TABLE NAME
- UPDATE TABLE NAME
- SET TABLE NAME
Correct answer: RENAME TABLE / ALTER TABLE ... RENAME
Renaming is done via ALTER TABLE ... RENAME (or RENAME TABLE in some dialects), both DDL operations.
Question 6: What does the CREATE INDEX statement accomplish?
- Inserts rows faster by caching them
- Creates a structure to speed up data retrieval on specified columns (Correct answer)
- Deletes duplicate rows
- Encrypts a column
Correct answer: Creates a structure to speed up data retrieval on specified columns
CREATE INDEX builds a data structure that improves the speed of queries filtering or sorting on the indexed columns.
Question 7: After issuing a DROP COLUMN with ALTER TABLE, what happens to that column's data?
- It is moved to a backup table
- It is permanently removed (Correct answer)
- It is hidden but recoverable with ROLLBACK
- It becomes NULL but stays
Correct answer: It is permanently removed
Dropping a column permanently deletes the column and all its data from the table.
SQL Data Definition Language (DDL) 2
Which DDL command removes an entire table including its structure and all data?