DDL — CREATE, ALTER, DROP — Concept Check
Questions on DDL — CREATE, ALTER, DROP. Data Definition Language defines schema; CREATE TABLE, ALTER TABLE (add/modify columns), DROP TABLE, and TRUNCATE are non-transactional DDL statements in MySQL.
Questions in this quiz
7 questions on DDL — CREATE, ALTER, DROP — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A developer needs to add a new column to an existing table without causing a full table lock. Which ALTER TABLE option should they use?
- AALGORITHM=REBUILD
- BALGORITHM=INSTANT
- CALGORITHM=COPY, LOCK=SHARED
- DALGORITHM=INPLACE, LOCK=NONE
2.What happens when you execute DROP TABLE IF EXISTS on a non-existent table?
- AAn error is raised
- BThe database is locked temporarily
- CA warning is logged
- DThe operation is ignored
3.A database administrator wants to reset the AUTO_INCREMENT counter of a table quickly. Which command should they use?
- ATRUNCATE TABLE table_name
- BALTER TABLE table_name AUTO_INCREMENT=1
- CDROP TABLE table_name
- DDELETE FROM table_name
4.Which of the following ALTER TABLE operations requires a table rebuild and may cause a brief exclusive lock?
- AModifying a column type
- BRenaming a column
- CAdding a new column
- DDropping an index
5.A developer notices that ALTER TABLE fails with an error when trying to modify a column without locking. What could be the reason?
- AThe storage engine doesn't support it
- BThe table is too large
- CThe operation requires a lock
- DThe column is a primary key
6.Why might a TRUNCATE TABLE operation be preferred over DELETE FROM table_name for large tables?
- ATRUNCATE fires triggers
- BTRUNCATE locks the table for less time
- CTRUNCATE is faster and resets AUTO_INCREMENT
- DTRUNCATE can be rolled back
7.A user tries to drop a table that is referenced by a foreign key constraint. What will happen?
- AThe table is dropped
- BThe foreign key is automatically removed
- CThe operation is queued
- DAn error occurs