Crack SQL Interviews with These 50+

Essential Questions
1. SQL Basics
Q1. What is SQL?
SQL (Structured Query Language) is a standard language used to store, manipulate, and retrieve data
in relational databases.

Q2. Difference between SQL and MySQL?

• SQL → language to query databases.

• MySQL → a relational database management system that uses SQL.

Q3. What are DDL, DML, DCL, and TCL?

• DDL (Data Definition Language): CREATE, DROP, ALTER

• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE

• DCL (Data Control Language): GRANT, REVOKE

• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

Q4. What is the difference between DELETE, TRUNCATE, and DROP?

• DELETE: Removes rows, can use WHERE, logs each row.

• TRUNCATE: Removes all rows, no WHERE, faster, cannot rollback in some DBs.

• DROP: Removes entire table structure and data.

Q5. What is a Primary Key?

A unique identifier for each record in a table, cannot be NULL.

Q6. What is a Foreign Key?

A key that references a primary key in another table, maintaining referential integrity.

Q7. What is a Unique Key?

Ensures all column values are unique, but allows one NULL.

Q8. What is the difference between WHERE and HAVING?

• WHERE: Filters before grouping.

• HAVING: Filters after grouping.

Q9. Difference between CHAR and VARCHAR?

• CHAR: Fixed length, pads with spaces.

• VARCHAR: Variable length, saves storage.

Q10. What is a constraint?
A rule enforced on data columns (PRIMARY KEY, UNIQUE, CHECK, NOT NULL, FOREIGN KEY).

2. Joins and Queries

Q11. What are different types of JOINs?

• INNER JOIN: Only matching rows.

• LEFT JOIN: All rows from left + matching right.

• RIGHT JOIN: All rows from right + matching left.

• FULL JOIN: All rows from both sides.

• SELF JOIN: A table joins with itself.

• CROSS JOIN: Cartesian product.

Q12. Write a query to find the second highest salary.

Q13. Difference between UNION and UNION ALL?

• UNION: Combines results, removes duplicates.

• UNION ALL: Combines results, keeps duplicates.

Q14. What is a subquery?

A query inside another query.

• Correlated Subquery: Depends on outer query.

• Non-correlated Subquery: Independent.

Q15. Query to get employees with salary greater than average salary.

Q16. What is an EXISTS operator?

Checks if subquery returns rows.
Q17. Difference between IN and EXISTS?

• IN: Compares with list of values.

• EXISTS: Checks for existence of rows.

Q18. Query to find duplicate rows in a table.

Q19. Query to fetch top 5 highest salaries.

Q20. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

• ROW_NUMBER(): Unique sequence (1,2,3).

• RANK(): Skips numbers for ties (1,2,2,4).

• DENSE_RANK(): No gaps in ranking (1,2,2,3).

3. Indexing and Performance

Q21. What is an Index?
A database object that improves query speed by providing quick lookup.

Q22. Types of Indexes?

• Clustered Index: Reorders table physically, only one per table.

• Non-clustered Index: Separate structure, can have multiple.

• Unique Index, Composite Index, Full-Text Index.

Q23. What is the difference between clustered and non-clustered index?

• Clustered: Data is stored in index order, one per table.

• Non-clustered: Separate structure, multiple allowed.

Q24. What is the difference between OLTP and OLAP?

• OLTP: Transactional, real-time (INSERT/UPDATE).

• OLAP: Analytical, read-heavy (reports, analysis).

Q25. How to improve query performance?

• Use indexes.

• Avoid SELECT *.

• Optimize joins.

• Use EXISTS instead of IN.

• Partition large tables.

4. Normalization & Data Modeling

Q26. What is Normalization?
Process of structuring data to reduce redundancy.

Q27. Different Normal Forms?

• 1NF: No repeating groups, atomic values.

• 2NF: 1NF + no partial dependency.

• 3NF: 2NF + no transitive dependency.

• BCNF: Every determinant is a candidate key.

Q28. What is Denormalization?

Adding redundancy to improve read performance.

Q29. What is a View?

A virtual table based on query result.

Q30. Difference between View and Table?

• View: Virtual, doesn’t store data.

• Table: Physical, stores data.

5. Transactions & Locks

Q31. What are ACID properties?

• Atomicity: All or nothing.

• Consistency: Data integrity maintained.

• Isolation: Transactions don’t interfere.

• Durability: Changes persist after commit.

Q32. What are Transactions?

A sequence of operations performed as a single logical unit.

Q33. What are Savepoints?

Intermediate points to rollback partially.
Q34. What are Locks in SQL?

• Shared Lock: Multiple read access.

• Exclusive Lock: Only one write access.

• Deadlock: Two transactions wait for each other’s locks.

Q35. Difference between optimistic and pessimistic locking?

• Optimistic: Assumes no conflict, checks at commit.

• Pessimistic: Locks data early to avoid conflict.

6. Stored Procedures, Triggers, Functions

Q36. What is a Stored Procedure?
A precompiled set of SQL statements stored in database.

Q37. Difference between Stored Procedure and Function?

• Procedure: Can return multiple values, can use DML.

• Function: Returns one value, can’t use DML (mostly).

Q38. What is a Trigger?

A block of SQL code executed automatically when an event occurs (INSERT, UPDATE, DELETE).

Q39. Difference between BEFORE and AFTER Trigger?

• BEFORE: Executes before data modification.

• AFTER: Executes after modification.

Q40. What is a Cursor?

A pointer that allows row-by-row processing of query results.

7. Advanced SQL
Q41. What is a Common Table Expression (CTE)?
A temporary result set that can be referenced within a query.

Q42. Difference between CTE and Subquery?

• CTE → More readable, reusable.

• Subquery → Nested inside query.

Q43. What is a Window Function?
Performs calculation across a set of rows related to the current row (e.g., RANK, LAG, LEAD).

Q44. What is a Materialized View?

A stored view with precomputed data, faster but requires refresh.

Q45. What is Pivot in SQL?

Converts rows into columns (cross-tab reports).

Q46. Difference between OLTP and OLAP databases?

• OLTP → Transaction processing (banking, retail).

• OLAP → Analytical (data warehouses, BI).

Q47. What is a Self Join?

Joining a table with itself.

Q48. What is Referential Integrity?

Ensuring foreign key values always match primary key values in the parent table.

Q49. What are Stored Generated Columns?

Columns computed from other columns (e.g., total = price * qty).

Q50. Difference between SQL and NoSQL databases?

• SQL: Relational, structured schema.

• NoSQL: Non-relational, schema-less, supports unstructured data.

Q51. What is Sharding?

Splitting a database into smaller parts (shards) to improve scalability.

Q52. What is Partitioning in SQL?

Splitting a table into smaller pieces for better performance.