Here are the top 25 most frequently asked SQL questions and answers for interviews, covering queries, database design, and concepts.

1. What is the difference between SQL and MySQL?

• SQL: Language for managing databases.
• MySQL: Database management system that uses SQL.

2. What is the difference between primary key and unique key?

• Primary key: Uniquely identifies records, cannot be NULL.
• Unique key: Ensures uniqueness but allows one NULL.

3. Explain INNER JOIN vs OUTER JOIN.

• INNER JOIN: Returns matching rows only.
• LEFT/RIGHT OUTER JOIN: Returns matches + non-matches from one side.

4. What are indexes in SQL?

• Structures that speed up search queries.
• Types: Clustered, Non-Clustered.

5. How do you find duplicate rows in a table?

```sql
SELECT name, COUNT()
FROM Employees
GROUP BY name
HAVING COUNT() > 1;
```

6. Difference between WHERE and HAVING?

• WHERE: Filters rows before grouping.
• HAVING: Filters grouped results.

7. How do you delete duplicate records?

```sql
DELETE FROM Employees
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM Employees
GROUP BY name
);
```

8. What is normalization? Explain types.

• Process of organizing data.
• 1NF, 2NF, 3NF, BCNF ⇒ reduce redundancy.

9. Difference between CHAR and VARCHAR?

• CHAR(n): Fixed length.
• VARCHAR(n): Variable length.

10. What is a foreign key?

• A key that references the primary key of another table.

11. Difference between TRUNCATE, DELETE, and DROP?

• DELETE: Removes rows (can be filtered, logged).
• TRUNCATE: Removes all rows quickly, no filtering.
• DROP: Deletes entire table schema & data.

12. Write a query to fetch the second highest salary.

```sql
SELECT MAX(salary)
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
```

13. Difference between UNION and UNION ALL.

• UNION: Removes duplicates.
• UNION ALL: Keeps duplicates.

14. Explain transactions and ACID properties.

• ACID: Atomicity, Consistency, Isolation, Durability.

15. Write a query to count employees by department.

```sql
SELECT department, COUNT(*)
FROM Employees
GROUP BY department;
```

16. What is a stored procedure?

• Precompiled SQL code that can be executed multiple times.

17. Explain triggers.

• Special procedures executed automatically in response to events (INSERT, UPDATE, DELETE).

18. Explain view vs materialized view.

• View: Virtual table (query result).
• Materialized View: Stored copy of data for faster access.

19. Explain indexes best practices.

• Use indexes for columns frequently in WHERE/JOIN.
• Avoid over-indexing (slows inserts/updates).

20. Explain subqueries.

• Query inside another query, can be correlated or non-correlated.

21. Write query to show top 3 salaries.

```sql
SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 3;
```

22. Difference between clustered vs non-clustered index.

• Clustered: Sorts & stores physical rows. One per table.
• Non-clustered: Uses pointers. Multiple per table allowed.

23. Explain database deadlocks.

• Situation when two queries wait for each other’s lock, causing a cycle.

24. Difference between DELETE with JOIN and subquery.

• JOIN: Deletes using relations across tables.
• Subquery: Deletes via nested query conditions.

25. Write SQL query to find employee(s) with maximum salary.

```sql
SELECT *
FROM Employees
WHERE salary = (SELECT MAX(salary) FROM Employees);
```

---

Tip: Practice writing optimized queries; interviewers value performance-oriented SQL.