SQL Interview Questions and Answers

1. What are the different types of joins in SQL?

- INNER JOIN: Returns rows when there is a match in both tables.
- LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
- FULL OUTER JOIN: Returns all rows when there is a match in either table.
- CROSS JOIN: Returns Cartesian product of both tables.
- SELF JOIN: A table joined with itself.
2. What is normalization and why is it important?
- Normalization is the process of organizing data to reduce redundancy and improve data integrity. It
ensures efficient storage and consistency.
3. What are the different normal forms in SQL?
- 1NF: Atomic values, no repeating groups.
- 2NF: 1NF + no partial dependency on primary key.
- 3NF: 2NF + no transitive dependency.
- BCNF: A stricter version of 3NF.
4. What is an index, and why is it used?
- An index is a database object that improves query performance by allowing faster data retrieval,
similar to a book’s index.
5. What is a stored procedure in SQL?
- A stored procedure is a precompiled set of SQL statements stored in the database, used to perform
operations efficiently.
6. What is a trigger in SQL, and how is it used?
- A trigger is an automatic action executed in response to specific events on a table, such as INSERT,
UPDATE, or DELETE.
7. What is a subquery in SQL?
- A subquery is a query nested inside another query to return intermediate results.
8. What is the difference between WHERE and HAVING clauses?
- WHERE filters rows before grouping, HAVING filters groups after aggregation.
9. What are ACID properties in a database transaction?
- Atomicity, Consistency, Isolation, Durability.
10. What is a foreign key in SQL?
- A foreign key is a field used to establish a link between two tables, ensuring referential integrity.
11. What is the difference between INNER JOIN and OUTER JOIN?
- INNER JOIN returns matching rows only; OUTER JOIN includes non-matching rows as well.
12. What is the difference between TRUNCATE and DELETE in SQL?
- DELETE removes rows selectively, can be rolled back.
- TRUNCATE removes all rows quickly, cannot be rolled back.
13. What is a VIEW in SQL?
- A view is a virtual table based on a SQL query, stored for easier access and abstraction.
14. What is a composite key?
- A composite key is a primary key consisting of two or more columns.
15. What is the purpose of the GROUP BY clause in SQL?
- GROUP BY groups rows that share common values for aggregate calculations.
16. What is the difference between CHAR and VARCHAR?
- CHAR is fixed-length, VARCHAR is variable-length.
17. What is denormalization, and when should it be used?
- Denormalization introduces redundancy to improve read performance.
18. What is the difference between UNION and UNION ALL?
- UNION removes duplicates, UNION ALL includes duplicates.
19. What is a CASE statement in SQL?
- A CASE statement provides conditional logic within SQL queries.
20. What is a materialized view in SQL?
- A materialized view stores query results physically, unlike a normal view.
21. What are the benefits of using indexes in SQL?
- Faster query execution, efficient data retrieval.
22. What is the purpose of the DISTINCT keyword in SQL?
- DISTINCT removes duplicate rows from query results.
23. What is the difference between AND and OR in SQL?
- AND requires all conditions to be true, OR requires at least one condition.
24. What is a NULL value in SQL?
- NULL represents missing or unknown data, different from empty string or zero.
25. What is a PRIMARY KEY in SQL?
- PRIMARY KEY uniquely identifies each row; UNIQUE ensures uniqueness but allows NULLs.
26. What are aggregate functions in SQL?
- Examples: COUNT(), SUM(), AVG(), MIN(), MAX().
27. What is a CROSS JOIN in SQL?
- CROSS JOIN returns Cartesian product of two tables.
28. What is the difference between INNER JOIN and SELF JOIN?
- SELF JOIN is joining a table with itself.
29. Explain the EXCEPT operator in SQL.
- Returns rows from the first query not found in the second query.
30. What is a transaction in SQL?
- A transaction is a sequence of operations executed as a single unit. Properties: ACID.
31. What is the difference between BETWEEN and IN?
- BETWEEN checks a range, IN checks specific values.
32. What are derived tables?
- Temporary tables created from a subquery.
33. What is a composite index?
- An index on multiple columns to optimize multi-column queries.
34. What is referential integrity?
- Ensures foreign keys correctly reference primary keys.
35. Difference between GROUP BY and ORDER BY?
- GROUP BY groups data, ORDER BY sorts data.
36. HAVING vs WHERE?
- WHERE filters rows, HAVING filters groups.
37. What is a schema?
- A logical structure of the database (tables, views, etc.).
38. What is cascade delete?
- Automatically deletes related rows in child tables when a parent row is deleted.
39. What is locking?
- Mechanism to control concurrent access to data.
40. Difference between TRUNCATE and DROP?
- TRUNCATE removes rows, DROP removes the table itself.
41. Pivot and unpivot?
- Pivot transforms rows into columns; unpivot reverses.
42. RANK() vs ROW_NUMBER()?
- RANK() assigns same rank to ties, ROW_NUMBER() gives unique numbers.
43. What is COALESCE()?
- Returns the first non-NULL value in a list.
44. Subquery vs Join?
- Subquery is nested, join merges multiple tables.
45. LEFT JOIN vs RIGHT JOIN?
- LEFT JOIN keeps all left rows, RIGHT JOIN keeps all right rows.
46. Types of indexes?
- Clustered, Non-clustered, Unique, Composite, Full-text.
47. ROW_NUMBER() vs RANK()?
- ROW_NUMBER() unique per row, RANK() allows ties.
48. Find second highest salary?
- Use: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM
employees);
49. What are CTEs?
- Temporary result sets defined with WITH keyword.
50. UNION vs UNION ALL?
- UNION removes duplicates, UNION ALL keeps all.
51. What is a Self Join?
- A join of a table with itself to compare rows.
52. GROUP BY vs DISTINCT?
- GROUP BY groups rows, DISTINCT removes duplicates.
53. How to optimize a query?
- Use indexes, avoid SELECT *, use joins efficiently.
54. COALESCE function?
- Returns first non-NULL value.
55. Retrieve top N rows?
- Use LIMIT (MySQL), TOP (SQL Server), FETCH FIRST (Oracle).
56. DELETE vs TRUNCATE?
- DELETE is row-level, TRUNCATE removes all rows.
57. WHERE vs HAVING?
- WHERE filters rows, HAVING filters groups.
58. NULL handling?
- Use IS NULL, IS NOT NULL, COALESCE(), NVL().
59. Find duplicates?
- Use GROUP BY with HAVING COUNT(*) > 1.
60. RANK() usage?
- Provides ranking of rows in result sets.
61. DISTINCT keyword?
- Eliminates duplicate rows.
62. First non-NULL value?
- Use COALESCE(col1, col2, ...).
63. INNER JOIN vs OUTER JOIN?
- INNER JOIN only matches, OUTER JOIN includes non-matches.
64. Handling errors in SQL?
- Use TRY...CATCH (SQL Server), EXCEPTION (PL/SQL).