SQL DDL Interview Questions

DELETE removes rows one at a time, logs each deletion in the transaction log, can use a WHERE clause to target specific rows, and fires any DELETE triggers on the table. TRUNCATE removes all rows at once with minimal logging (it deallocates data pages), cannot use a WHERE clause, does not fire triggers, and resets identity counters. DELETE is slower but more flexible; TRUNCATE is faster but removes everything. Both can be rolled back inside an explicit transaction in SQL Server and PostgreSQL.

ALTER TABLE is the DDL command used to modify an existing table's structure in SQL Server. You can add columns, drop columns, change data types, add or drop constraints, and rename columns. The syntax varies slightly depending on what you're doing, for example adding a column uses ADD, while modifying an existing column's type uses ALTER COLUMN.

-- Add a new column
ALTER TABLE employees ADD department_id INT NULL;

-- Change a column's data type
ALTER TABLE employees ALTER COLUMN salary DECIMAL(12, 2);

-- Drop a column
ALTER TABLE employees DROP COLUMN old_column;

-- Add a constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

Fetching alternate rows relies on using the row's position, typically via ROW_NUMBER() or a rowid. You filter on even or odd row numbers depending on which set you want. The exact syntax differs slightly between databases, but the pattern is the same.

-- Fetch odd-numbered rows (1st, 3rd, 5th...)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
    FROM employees
) ranked
WHERE rn % 2 = 1;

-- Fetch even-numbered rows (2nd, 4th, 6th...)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
    FROM employees
) ranked
WHERE rn % 2 = 0;

TRUNCATE removes all rows from a table but keeps the table structure intact, so you can still insert data into it afterward. DROP removes the entire table, including its structure, indexes, constraints, and all data. TRUNCATE is also much faster than a DELETE because it does minimal logging and does not fire row-level triggers. DROP is irreversible in the sense that you would need to recreate the table from scratch, while TRUNCATE just gives you an empty table.

TRUNCATE TABLE orders;  -- table still exists, just empty

DROP TABLE orders;      -- table is completely gone

You create a database with the CREATE DATABASE statement followed by the database name. In most systems that is all you need, though you can also specify options like file locations, size, collation, and character set depending on the database engine. After creation you typically use a USE statement to switch into that database before creating tables.

-- Basic creation
CREATE DATABASE company_db;

-- Switch to it
USE company_db;

-- PostgreSQL / MySQL with character set
CREATE DATABASE company_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Use TRUNCATE when you want to remove all rows from a table quickly, do not need to log individual row deletions, and do not have WHERE clause conditions. It resets identity columns and is much faster on large tables because it deallocates data pages rather than deleting rows one by one. Use DELETE when you need to remove specific rows based on a WHERE condition, when you need the operation to fire DELETE triggers, or when you need to be able to roll it back as part of a transaction with finer control. DELETE is also required when the table is referenced by foreign keys with cascading rules.