SQL Queries & Commands

1. What is the functional difference between the DELETE, TRUNCATE, and DROP commands in SQL?

These three commands all remove data, but they work at very different levels.

Command	Type	What it does	Can it be rolled back?
DELETE	DML	Removes rows one by one, using a WHERE clause if given	Yes
TRUNCATE	DDL	Empties the entire table quickly	Not easily
DROP	DDL	Removes the table and its structure completely	No

Walkthrough:

• DELETE FROM employees WHERE department = 'HR'; — deletes matching rows, fires triggers, and can be undone with a rollback if inside a transaction.
• TRUNCATE TABLE employees; — wipes every row in one fast operation. You cannot filter it, and in most databases it can’t be rolled back.
• DROP TABLE employees; — the whole table, its columns, indexes, and definitions are gone. You’d have to recreate it from scratch.

A handy mental model: DELETE edits the data, TRUNCATE empties the container, DROP throws away the container itself.

2. How do the WHERE and HAVING clauses differ in their application during a query?

The short version: WHERE filters rows before any grouping, HAVING filters groups after grouping.

A query runs in stages. It reads rows, filters them, groups them, and aggregates them. WHERE acts at the row-filtering stage — it decides which raw rows get fed into the grouping. HAVING acts after grouping — it decides which resulting groups are kept.

Because of this order, WHERE cannot use aggregate functions like COUNT() or SUM(), but HAVING can.

Example — departments with more than 2 employees:

SELECT department, COUNT(*)
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 2;

WHERE first drops inactive employees, then the groups are formed, then HAVING keeps only departments with more than 2 active employees.

3. What is a ‘JOIN’ in SQL, and what is the difference between an INNER JOIN and a LEFT JOIN?

A JOIN combines rows from two or more tables based on a related column between them. Let’s use two small tables:

Employees:

name	department_id
Ali	1
Bob	2
Cam	(none)

Departments:

id	dept_name
1	Sales
2	IT
3	HR

INNER JOIN returns only rows with a match on both sides. Cam is dropped because he has no department.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Result: Ali/Sales, Bob/IT.

LEFT JOIN keeps every row from the left table, and fills in matching columns from the right. Unmatched rows get NULL for the right side.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Result: Ali/Sales, Bob/IT, Cam/(none).

The one-liner to remember: INNER needs a match on both sides, LEFT keeps everything on the left no matter what.

4. What is a Database View, and what is the primary difference between a regular View and a Materialized View?

A view is a saved query that you can query like a table. It doesn’t hold data itself — it’s a window on top of the underlying tables.

Regular view: Every time you query it, the database runs the underlying query live. It always shows current data, but if the base query is heavy, every access is heavy too.

Materialized view: The results are physically stored on disk. Queries hit the stored snapshot, which is fast. The catch is freshness — the stored data must be refreshed periodically (manually or on a schedule), so it can be stale compared to the source tables.

	Regular View	Materialized View
Stores data	No, virtual	Yes, physically
Always current	Yes	Only after refresh
Query speed	Same as underlying query	Fast
Extra storage	None	Yes

A common use of a materialized view is a complex daily report — the aggregation is computed once and served instantly instead of recomputed on every request.

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

Both combine the results of two SELECT queries. The difference is duplicates.

• UNION runs a distinct operation, so duplicate rows are removed.
• UNION ALL simply appends all rows from both queries, duplicates included.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

If both tables contain “Mumbai”, UNION shows it once; UNION ALL shows it twice.

Performance note: UNION ALL is faster because it skips the deduplication work. Use UNION only when you actually need distinct rows. If you know the sets can’t overlap, prefer UNION ALL.

6. What is a Stored Procedure vs. a Trigger?

Both are pre-written SQL code stored in the database, but they run differently.

• Stored Procedure: invoked explicitly — you (or your application) call it by name, often with parameters.
• Trigger: runs automatically when a defined event happens, like an INSERT, UPDATE, or DELETE on a table.

Example: A stored procedure GetCustomerOrders(customerId) runs only when someone calls it. A trigger AuditLogTrigger fires on its own whenever a row is updated, logging the old and new values — no one calls it directly.

	Stored Procedure	Trigger
Invocation	Explicit (CALL / EXEC)	Automatic on events
Parameters	Yes	No
Return value	Yes	No

7. What is a Correlated Subquery?

A normal subquery runs once and hands its result to the outer query. A correlated subquery is different — it references columns from the outer query, so it has to re-run for every row the outer query processes.

SELECT e.name
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

For each employee, the inner query recomputes the average salary of that employee’s department. That’s the correlation — the inner query depends on e.department_id from outside.

Because it executes once per outer row, a correlated subquery is often slower than an equivalent JOIN.

8. What is a Self-Join?

A self-join joins a table with itself. It’s the same table used twice, with different aliases, so you can compare rows within that table.

Classic use — hierarchies:

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

Here employees is treated as two logical tables: e for the employee and m for the manager. The join matches each employee’s manager_id to a manager’s employee_id.

Self-joins are the standard way to model trees — employees and managers, categories and sub-categories, follows and followers.

9. What is a Cursor?

A cursor is a database object that lets you process query results one row at a time, rather than all at once.

SQL is normally set-based — a query returns a whole result set. But sometimes you need row-by-row logic (like computing a running total), and a cursor gives you that:

1. DECLARE cursor over a SELECT result
2. OPEN the cursor
3. FETCH one row, process it, repeat
4. CLOSE and deallocate

Cursors are convenient inside stored procedures, but they’re generally slower than set-based operations. As a rule: if you can solve it with a single set-based statement, prefer that. Reach for cursors only when row-by-row logic is genuinely unavoidable.

10. What is an Anti‑Join conceptually in relational algebra?

An anti-join returns all rows from the first relation that have no match in the second relation.

In SQL it’s expressed with NOT IN or NOT EXISTS:

SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

This returns every customer with no orders — the anti-join. It’s the conceptual opposite of an inner join, which returns rows that do match.

11. Which Relational Algebra operation returns tuples present in the first relation but absent in the second?

That’s the Set Difference operator, written as A − B.

A = {1, 2, 3},  B = {2, 3, 4}
A − B = {1}

It returns everything in A that isn’t in B. The two relations must be compatible (same number of attributes, same domains).

For comparison:

• Intersection (∩) returns what’s in both.
• Union (∪) returns what’s in either.
• Set difference (−) returns what’s in A only.

In SQL, set difference maps to EXCEPT (or MINUS in Oracle).