SQL DDL Commands: CREATE TABLE, ALTER TABLE, DROP and TRUNCATE - Quiz
Total: 6 questions
1. What is DDL in SQL, which commands does it include, and how does it differ from DML?
What is DDL in SQL, which commands does it include, and how does it differ from DML?
DDL (Data Definition Language) is the group of SQL commands that create, change and delete database objects: databases, tables, columns, indexes, constraints. There are five main commands:
CREATE– creates a database, table, index or view;ALTER– changes the structure: adds, modifies and drops columns, constraints, indexes;RENAME– renames a table;DROP– deletes an object together with its data;TRUNCATE– deletes all rows while keeping the table structure.
DDL works with structure, while DML (INSERT, UPDATE, DELETE) works with rows. A key difference in MySQL: every DDL command performs an implicit COMMIT, so it cannot be undone with ROLLBACK; DML commands inside a transaction can be rolled back. In PostgreSQL DDL runs inside a transaction and can be rolled back.
2. How do you create a database and a table in MySQL, and what does IF NOT EXISTS do?
How do you create a database and a table in MySQL, and what does IF NOT EXISTS do?
A database is created with CREATE DATABASE and selected with USE. A table is created in the current database: list the columns in parentheses separated by commas (name, type, constraints), and add table-level constraints at the end.
CREATE DATABASE IF NOT EXISTS forum
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE forum;
CREATE TABLE topics (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
author_id INT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT fk_topics_author FOREIGN KEY (author_id) REFERENCES users (id)
);
If the object already exists, the command without IF NOT EXISTS fails (ERROR 1007 for a database, ERROR 1050 for a table), while with IF NOT EXISTS it does nothing and only raises a warning. This form is used in scripts that run many times. Note that IF NOT EXISTS checks only the name: if the table exists but with different columns, its structure does not change – that is what ALTER TABLE is for. The parent table (users) must be created before the child table (topics).
3. What do the NOT NULL, DEFAULT and CHECK constraints do, and how does a column-level constraint differ from a table-level one?
What do the NOT NULL, DEFAULT and CHECK constraints do, and how does a column-level constraint differ from a table-level one?
A constraint is a rule the database checks on every insert and update of a row; a violation is rejected with an error.
NOT NULL– the column contains noNULL(ERROR 1048); declared on a column only.DEFAULT value– the value used whenINSERTomits the column; strictly speaking, an attribute rather than a check.CHECK (condition)– values satisfy the condition (ERROR 3819). Enforced since MySQL 8.0.16; earlier it was parsed and ignored. A row withNULLpasses: the condition evaluates to “unknown”, not false.
A constraint can be written in the column definition (name VARCHAR(50) NOT NULL UNIQUE) or as a separate line at the end of CREATE TABLE. The second form lets you name it with CONSTRAINT name – the name appears in error messages, and you use it to drop the constraint later:
CREATE TABLE tags (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT uq_tags_name UNIQUE (name),
CONSTRAINT chk_tags_name CHECK (CHAR_LENGTH(name) >= 2)
);
A column-level foreign key (author_id INT REFERENCES users (id)) is silently ignored by MySQL 8.4 and earlier – declare it only as a separate FOREIGN KEY (...) REFERENCES ... line.
4. How do you view a table structure in MySQL, and how does DESCRIBE differ from SHOW CREATE TABLE?
How do you view a table structure in MySQL, and how does DESCRIBE differ from SHOW CREATE TABLE?
DESCRIBE table (short form DESC; SHOW COLUMNS FROM table prints the same) lists the columns as a grid: Field, Type, Null, Key, Default, Extra. In the Key column, PRI marks the primary key and MUL a non-unique index (for example, one created for a foreign key).
SHOW CREATE TABLE table prints the full CREATE TABLE statement: all constraints with their names, the engine and the character set. DESCRIBE does not show constraint names, yet you need them for DROP FOREIGN KEY and DROP CHECK. SHOW CREATE TABLE is also how you verify that a CHECK was really stored and compare a column definition before ALTER TABLE ... MODIFY. SHOW TABLES lists the tables of the current database.
5. How do you change an existing table with ALTER TABLE, and how do you rename a table?
How do you change an existing table with ALTER TABLE, and how do you rename a table?
ALTER TABLE name action [, action ...]; changes the structure without losing data; one command can list several actions.
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email; -- add a column
ALTER TABLE topics MODIFY title VARCHAR(255) NOT NULL; -- change the type and attributes
ALTER TABLE topics CHANGE title subject VARCHAR(200) NOT NULL; -- rename and change
ALTER TABLE topics RENAME COLUMN title TO subject; -- only rename (MySQL 8.0+)
ALTER TABLE users DROP COLUMN phone; -- drop a column
ALTER TABLE tags DROP CONSTRAINT chk_tags_name; -- drop a constraint (8.0.19+)
The main trap: MODIFY and CHANGE replace the whole column definition – whatever you omit (NOT NULL, DEFAULT, AUTO_INCREMENT) silently disappears. A column added without AFTER/FIRST goes last, and existing rows get DEFAULT or NULL. A foreign key is not changed in place: drop it by name (DROP FOREIGN KEY) and create it again.
A table is renamed with RENAME TABLE topics TO threads; or the equivalent ALTER TABLE topics RENAME TO threads;. Data, indexes and constraints are kept, and InnoDB updates the foreign keys of other tables automatically. Several tables in one RENAME TABLE are renamed atomically. MySQL has no RENAME DATABASE command.
6. What is the difference between TRUNCATE, DELETE and DROP?
What is the difference between TRUNCATE, DELETE and DROP?
DELETE FROM t– a DML command: deletes rows (all or byWHERE) one by one, does not reset theAUTO_INCREMENTcounter, can be rolled back inside a transaction, firesON DELETEtriggers. Slow on millions of rows.TRUNCATE TABLE t– a DDL command in MySQL: deletes all rows (noWHERE); InnoDB recreates an empty table, so it is fast. The structure, indexes and constraints stay, and theAUTO_INCREMENTcounter is reset to 1. It cannot be rolled back (implicitCOMMIT) and does not fire triggers. On a table referenced by a foreign key it fails withERROR 1701, even if the child table is empty.DROP TABLE t– a DDL command: deletes the whole table, rows and structure. It cannot be rolled back. On a table referenced by a foreign key it fails withERROR 3730.IF EXISTSturns the “unknown table” error into a warning.
In short: to delete some rows or keep the option to roll back – DELETE; to empty the whole table and restart numbering – TRUNCATE; to remove the table from the database – DROP.