Here are the 10 most critical SQL questions that dominate both academic exams and job interviews.

 🅢Here are the 10 most critical SQL questions that dominate both academic exams and job interviews. I’ve structured them with the exact concepts you must mention and sample syntax to ace the practical round.

1. What are the 4 categories of SQL commands (DDL, DML, DCL, TCL)?

Concept to highlight: Distinguish by their impact on database structure vs. data vs. permissions vs. transactions.

· DDL (Data Definition): CREATE, ALTER, DROP, TRUNCATE (Auto-commits).

· DML (Data Manipulation): SELECT, INSERT, UPDATE, DELETE (Requires COMMIT).

· DCL (Data Control): GRANT, REVOKE (User permissions).

· TCL (Transaction Control): COMMIT, ROLLBACK, SAVEPOINT.

---

2. Explain all SQL JOINs with a real-world use case.

Concept to highlight: How data is matched between tables.

· INNER JOIN: Returns only matching rows in both tables.

· LEFT/RIGHT JOIN: Returns all rows from the left/right table + matches from the other (NULL if no match).

· FULL OUTER JOIN: Returns all rows from both tables.

· SELF JOIN: Joining a table to itself (e.g., employees with their managers).

· CROSS JOIN: Cartesian product (every row pairs with every other).

```sql

SELECT e.name, m.name AS Manager

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.id;

```

---

3. WHERE vs. HAVING—when do you use which?

Concept to highlight: The order of execution matters.

· WHERE filters rows before grouping (GROUP BY). Cannot use aggregate functions (e.g., SUM, AVG).

· HAVING filters groups after GROUP BY. Can use aggregate functions.

```sql

-- Correct:

SELECT dept_id, AVG(salary) 

FROM employees 

WHERE salary > 30000 -- Filters raw rows first

GROUP BY dept_id 

HAVING AVG(salary) > 50000; -- Filters groups later

```

---

4. What are Window Functions? Differentiate ROW_NUMBER, RANK, and DENSE_RANK.

Concept to highlight: They perform calculations across a set of rows without collapsing them into a single output row.

· ROW_NUMBER(): Assigns a unique sequential number to each row (ties get arbitrary numbers).

· RANK(): Same rank for ties, but skips subsequent numbers (1,2,2,4).

· DENSE_RANK(): Same rank for ties, but does not skip (1,2,2,3).

```sql

SELECT name, channel,

       RANK() OVER (ORDER BY salary DESC) as rank

FROM YouTube;

```

---

5. Primary Key vs. Unique Key vs. Foreign Key.

Concept to highlight: Constraints for data integrity.

· Primary Key: Uniquely identifies a row. Implicitly NOT NULL and only one per table.

· Unique Key: Ensures uniqueness. Allows one NULL value and multiple per table.

· Foreign Key: Enforces referential integrity by linking to a Primary Key in another table. Allows duplicates and NULLs.

---

6. Explain Normalization up to 3NF (and why denormalize?).

Concept to highlight: Reducing redundancy vs. performance trade-offs.

· 1NF: Columns must contain atomic (indivisible) values; each column has a single value.

· 2NF: Must be in 1NF and every non-key column must be fully dependent on the entire primary key (removes partial dependency).

· 3NF: Must be in 2NF and no transitive dependency (non-key column cannot depend on another non-key column).

· Denormalization: Done deliberately in Data Warehousing to reduce the number of JOINs and speed up reads.

---

7. Subquery vs. CTE (Common Table Expression). Which is better?

Concept to highlight: Readability, reusability, and recursion.

· Subquery: A query nested inside another. Can be used in SELECT, FROM, or WHERE. Gets messy with multiple nesting.

· CTE (WITH clause): Creates a temporary named result set. Highly preferred for complex queries because it's more readable, can be referenced multiple times, and supports recursive queries.

```sql

WITH HighEarners AS (

    SELECT * FROM employees WHERE salary > 100000

)

SELECT * FROM HighEarners WHERE dept_id = 10;

```

---

8. What are Indexes? Clustered vs. Non-Clustered.

Concept to highlight: Performance tuning (Speeds up SELECT, slows down INSERT/UPDATE/DELETE).

· Clustered Index: Determines the physical order of data storage. Only 1 per table (usually the Primary Key). The actual data rows are stored at the leaf level.

· Non-Clustered Index: A separate structure that points to the physical data rows. Up to 999 per table. Stores a copy of the indexed columns + a pointer.

---

9. Explain ACID Properties in SQL Databases.

Concept to highlight: Guarantees for reliable transactions.

· Atomicity: Transaction is "all or nothing" (COMMIT or ROLLBACK).

· Consistency: Database moves from one valid state to another (constraints are maintained).

· Isolation: Concurrent transactions do not interfere with each other (handled by isolation levels like READ COMMITTED, SERIALIZABLE).

· Durability: Once committed, data persists even in case of a system crash.

---

10. Write a query to find the Nth highest salary (e.g., 3rd highest).

Concept to highlight: Handling ties correctly. Interviewers want to see if you use DENSE_RANK (to handle ties properly) or OFFSET (for distinct salaries).

· Option A (Best for ties): Using DENSE_RANK().

· Option B (Simplest for distinct): Using OFFSET / LIMIT.

```sql

-- Finds the 3rd highest distinct salary:

SELECT DISTINCT salary 

FROM employees 

ORDER BY salary DESC 

OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;

-- Finds 3rd highest considering ties:

WITH RankedSalaries AS (

    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk

    FROM employees

)

SELECT DISTINCT salary FROM RankedSalaries WHERE rnk = 3;

```

50 MCQ interview 

---