25 Questions — Jump to a topic

On this page

1. 1. What is the difference between SQL and MySQL?
2. 2. What is the difference between primary key and unique key?
3. 3. Explain INNER JOIN vs OUTER JOIN.
4. 4. What are indexes in SQL?
5. 5. How do you find duplicate rows in a table?
6. 6. Difference between WHERE and HAVING?
7. 7. How do you delete duplicate records?
8. 8. What is normalization? Explain types.
9. 9. Difference between CHAR and VARCHAR?
10. 10. What is a foreign key?
11. 11. Difference between TRUNCATE, DELETE, and DROP?
12. 12. Write a query to fetch the second highest salary.
13. 13. Difference between UNION and UNION ALL.
14. 14. Explain transactions and ACID properties.
15. 15. Write a query to count employees by department.
16. 16. What is a stored procedure?
17. 17. Explain triggers.
18. 18. Explain view vs materialized view.
19. 19. Explain indexes best practices.
20. 20. Explain subqueries.
21. 21. Write query to show top 3 salaries.
22. 22. Difference between clustered vs non-clustered index.
23. 23. Explain database deadlocks.
24. 24. Difference between DELETE with JOIN and subquery.
25. 25. Write SQL query to find employee(s) with maximum salary.

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.