Data Manipulation & DDL/DML/DCL

1. What is the difference between UNION and UNION ALL?

Both combine the results of two queries into one result set. UNION removes duplicate rows. UNION ALL keeps every row, including duplicates.

How they work: UNION must check for duplicates, so it sorts or hashes the combined results and drops repeats. That extra step makes it slower.

UNION ALL simply stacks the results of the two queries together. No duplicate check, so it’s faster.

Example with the two tables:

Employees_2024 table:

name
Ali
Bob

Employees_2025 table:

name
Bob
Cam

UNION:

SELECT name FROM employees_2024
UNION
SELECT name FROM employees_2025;

Result:

name
Ali
Bob
Cam

Bob appears only once because UNION removed the duplicate.

UNION ALL:

SELECT name FROM employees_2024
UNION ALL
SELECT name FROM employees_2025;

Result:

name
Ali
Bob
Bob
Cam

Bob appears twice because UNION ALL keeps every row.

Key differences table:

	UNION	UNION ALL
Removes duplicates	Yes	No
Faster	No	Yes
Good when results never overlap	No	Yes

Key takeaway: Use UNION only when you genuinely need distinct rows. Use UNION ALL when duplicates don’t matter, because it’s faster. Both queries must have the same number and type of columns.

2. What is the difference between DELETE, TRUNCATE, and DROP?

All three remove data, but at different levels. DELETE removes specific rows, TRUNCATE removes all rows but keeps the table, and DROP removes the entire table.

1. DELETE Removes rows one by one.

It supports a WHERE clause, so you can remove only certain rows.

It is fully logged, so it can be rolled back inside a transaction.

Because it’s row-by-row and logged, it’s the slowest of the three.

DELETE FROM employees WHERE department_id = 10;

2. TRUNCATE Removes all rows instantly.

It keeps the table structure, so you can still insert into it afterward.

It uses minimal logging and cannot easily be rolled back.

It doesn’t support a WHERE clause.

TRUNCATE TABLE employees;

3. DROP Removes the entire table — structure, data, indexes, everything.

The table no longer exists.

You must recreate it before using it again.

DROP TABLE employees;

Key differences table:

	DELETE	TRUNCATE	DROP
Removes rows	Yes (with WHERE)	All rows	All rows
Keeps table structure	Yes	Yes	No
Supports WHERE	Yes	No	No
Can roll back	Yes	Hardly	No
Speed	Slowest	Fast	Fast
Command type	DML	DDL	DDL

Key takeaway: Use DELETE for selected rows with recovery options. Use TRUNCATE to clear a table completely but keep it. Use DROP when the table is no longer needed.

3. What is the logical order of execution for a SELECT query?

SQL doesn’t run a query top-to-bottom. It follows a fixed order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

The order:

1. FROM — pick the tables.
2. WHERE — filter rows.
3. GROUP BY — group rows.
4. HAVING — filter groups.
5. SELECT — pick the columns.
6. ORDER BY — sort.
7. LIMIT — cut the row count.

Why this matters: You can’t use an alias from SELECT in WHERE, because WHERE runs first.

SELECT name, salary * 1.1 AS bonus
FROM employees
WHERE bonus > 50000;   -- ERROR: bonus doesn't exist yet

Aliases created in SELECT can be used in ORDER BY but not WHERE.

Key takeaway: Know the order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. It explains which parts of a query can reference what.

4. What is the difference between DDL, DML, and DCL?

DDL defines the structure of the database. DML manages the data inside it. DCL manages access and permissions.

DDL — Data Definition Language: Deals with the structure: tables, schemas, indexes.

Examples: CREATE, ALTER, DROP.

CREATE TABLE employees (id INT PRIMARY KEY);

DML — Data Manipulation Language: Deals with the data inside tables.

Examples: INSERT, UPDATE, DELETE, SELECT.

INSERT INTO employees (id) VALUES (1);

DCL — Data Control Language: Deals with permissions.

Examples: GRANT, REVOKE.

GRANT SELECT ON employees TO analyst;

Key differences table:

	DDL	DML	DCL
Works on	Structure	Data	Access
Examples	CREATE, ALTER, DROP	INSERT, UPDATE, DELETE	GRANT, REVOKE
Common	Schema design	Everyday work	Admin work

Key takeaway: DDL shapes the tables, DML works the data, DCL controls who can do what.

5. What is an ‘Identity’ or ‘Auto-Increment’ column?

An identity column automatically generates a unique number for each new row.

The idea: You don’t provide the value.

The database assigns the next number automatically.

Example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

Inserts:

INSERT INTO users (name) VALUES ('Ali');
INSERT INTO users (name) VALUES ('Bob');

Ali gets id 1, Bob gets id 2 — automatically.

Why use it:

• No manual numbering.
• Always unique.
• Great as a surrogate key.

Database names:

• MySQL: AUTO_INCREMENT
• PostgreSQL: SERIAL or IDENTITY
• SQL Server: IDENTITY

Key takeaway: An identity column numbers rows automatically — perfect for primary keys where you never want to pick the number yourself.

6. What is the difference between a ‘Hard Delete’ and a ‘Soft Delete’?

A hard delete removes the row permanently. A soft delete marks the row as inactive while keeping the data.

Hard delete: Uses the DELETE command.

The row is gone forever.

DELETE FROM users WHERE id = 5;

Soft delete: Uses an UPDATE to set a flag.

The row stays in the table but is treated as deleted.

UPDATE users SET is_deleted = 1 WHERE id = 5;

Queries must filter out deleted rows:

SELECT * FROM users WHERE is_deleted = 0;

Why soft delete?

• Audit trail — you can see what was deleted and when.
• Recovery — restoring is just setting the flag back.
• Data that other tables reference stays intact.

Key differences table:

	Hard delete	Soft delete
Command	DELETE	UPDATE
Data kept	No	Yes
Recoverable	No	Yes
Extra query filtering	No	Yes

Key takeaway: Hard delete removes data permanently; soft delete hides it with a flag. Use soft delete when you need an audit trail or recoverability.

7. How do you UPDATE a table using data from another table, and what is UPSERT?

UPDATE with a JOIN lets you modify rows based on values in another table. Two common syntaxes:

-- PostgreSQL / SQL Server
UPDATE orders o
SET o.status = 'priority'
FROM customers c
WHERE o.customer_id = c.id AND c.tier = 'gold';

-- MySQL
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'priority'
WHERE c.tier = 'gold';

Both match rows across the two tables and update only the ones meeting the condition. It’s the standard way to propagate a change from a master table into a detail table.

UPSERT = UPdate or inSERT — write a row, updating it if it already exists, inserting it if not. The ON CONFLICT (PostgreSQL) and ON DUPLICATE KEY UPDATE (MySQL) clauses:

-- PostgreSQL
INSERT INTO users (id, name, email) VALUES (1, 'A', 'a@x.com')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- MySQL
INSERT INTO users (id, name, email) VALUES (1, 'A', 'a@x.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);

The interview points: UPSERT is how you make an operation idempotent — run it many times, and the final state is the same. UPDATE-with-JOIN is the way to update one table from another’s data, and UPSERT is the way to safely sync a row when you don’t know if it exists yet.